180. Consecutive Numbers

Description

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

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.

ConsecutiveNums
1

Constraints

Approach

Links

• GeeksforGeeks

• Leetcode
• ProgramCreek

• YouTube

Examples

Example 1

Input:

{"headers": {"Logs": ["Id", "Num"]}, "rows": {"Logs": [[1, 1], [2, 1], [3, 1], [4, 2], [5, 1], [6, 2], [7, 2]]}}

Output:

{"headers": ["ConsecutiveNums"], "values": [[1]]}

Solutions

Solution 1

# MySQL query statement

SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Num = l2.Num AND 
        l2.Num = l3.Num AND 
        l1.Id = l2.Id-1 AND 
        l2.Id = l3.Id-1

Follow up