Consecutive Numbers
Task: Find all numbers that appear at least three times consecutively.
Table: Logs
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | INT |
| num | INT |
+-------------+------+Input: Logs table:
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+Output:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+View Reference Solution & Explanation ▼
This solution uses a `JOIN` to combine data from multiple tables to find the required output.
SELECT DISTINCT l1.num AS ConsecutiveNums FROM Logs l1 JOIN Logs l2 ON l1.id = l2.id - 1 AND l1.num = l2.num JOIN Logs l3 ON l1.id = l3.id - 2 AND l1.num = l3.num;