SQL Coding Exercise – Consecutive Numbers

Puzzle is taken from https://oj.leetcode.com/problems/consecutive-numbers/

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.

The solution is to join the table to itself three times and make sure the `Id` field is consecutive but the `Num` is the same.

select Distinct `A`.`Num` from `Logs` as `A`, `Logs` as `B`, `Logs` as `C` where `A`.`Id` = `B`.`Id` - 1 and `B`.`Id` = `C`.`Id` - 1 and `A`.`Num` = `B`.`Num` and `B`.`Num` = `C`.`Num`

You can rewrite it using inner join:

select distinct(Logs.Num) as triplets
from Logs inner join
(select logs1.Id as id1, logs2.id as id2, logs1.Num as Num from
    Logs as logs1 inner join Logs as logs2 on logs1.Id = logs2.Id - 1
    where logs1.Num = logs2.Num) as doubles
on Logs.Id = doubles.id1 + 2
where doubles.Num = Logs.Num;

–EOF (The Ultimate Computing & Technology Blog) —