【SQL】LeetCode-Consecutive Numbers

LeetCode 180：Consecutive Numbers

【Description】

Write an SQL query to find all numbers that appear at least three times consecutively.
Return the result table in any order.
The query result format is in the following example:

【Solution】

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
;

Consecutive appearing means the Id of the Num are next to each others. Since this problem asks for numbers appearing at least three times consecutively, we can use 3 aliases for this table Logs, and then check whether 3 consecutive numbers are all the same.

IMPORTANT:we need to add a keyword DISTINCT because it will display a duplicated number if one number appears more than 3 times consecutively.