180. Consecutive Numbers

Description of Problem

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
In SQL, id is the primary key for this table.
id is an autoincrement column.

Find all numbers that appear at least three times consecutively.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input: 
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.

Solution

Tags: SQL

Explanation

Combine left-shifted rows, right-shifted rows and original rows. Return unique rows that have the same value.

For example:

Origin:         [1 1 1 2 1 2 2]
Left-shifted:   [1 1 2 1 2 2 x]
Right-shifted:  [x 1 1 1 2 1 2]
----------------------------------
Result:         [x 1 x x x x x]

Code (MySQL)

SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1
INNER JOIN Logs l2
ON l1.Id = l2.Id - 1 AND l1.Num = l2.Num
INNER JOIN Logs l3
ON l2.Id = l3.Id - 1 AND l2.Num = l3.Num;