## Monday, February 11, 2019

### SQL question challenge (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.

```+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+```

DDL SCRIPTS
```create table numbers(id int, num int);

insert into numbers values (1,1);
insert into numbers values (2,1);
insert into numbers values (3,1);
insert into numbers values (4,2);
insert into numbers values (5,1);
insert into numbers values (6,2);
insert into numbers values (7,2);
```

SOLUTION
Approach: Using DISTINCT and WHERE

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.

Id Num Id Num Id Num
1 1 2 1 3 1

Note: The first two columns are from l1, then the next two are from l2, and the last two are from l3.

Then we can select any Num column from the above table to get the target data.
However, we need to add a keyword DISTINCT because it will display a duplicated number if one number appears more than 3 times consecutively.

```select distinct(n1.num) consecutive_threes
from numbers n1,numbers n2,numbers n3
where n1.id=n2.id - 1 and n2.id=n3.id-1
and n1.num = n2.num
and n2.num = n3.num;
```