[LeetCode] 180. Consecutive Numbers

Posted on | In

Problem

Write a SQL query to find all numbers that appear at least three times consecutively.

1
2
3
4
5
6
7
8
9
10
11
+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

1
2
3
4
5
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

Explanation

  1. To have 3 consecutive numbers, we can have 3 Logs table.

  2. We will select distince number and make sure first table’s id equal to second table’s id minus 1, and second table’s id equal to third table’s id minus 1, and all three table’s number are equal.

Solution

1
2
3
4
5
# Write your MySQL query statement below
SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1 AND
        l1.Num = l2.Num AND l2.Num = l3.Num;