## 180. 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               |
+-----------------+
```

b'
\n\n

## Solution

\n
\n

#### Approach: Using `DISTINCT` and `WHERE` clause [Accepted]

\n

Algorithm

\n

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.

\n
`SELECT *\nFROM\n    Logs l1,\n    Logs l2,\n    Logs l3\nWHERE\n    l1.Id = l2.Id - 1\n    AND l2.Id = l3.Id - 1\n    AND l1.Num = l2.Num\n    AND l2.Num = l3.Num\n;\n`
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
IdNumIdNumIdNum
112131
>Note: The first two columns are from l1, then the next two are from l2, and the last two are from l3.
\n

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.

\n

MySQL

\n
`SELECT DISTINCT\n    l1.Num AS ConsecutiveNums\nFROM\n    Logs l1,\n    Logs l2,\n    Logs l3\nWHERE\n    l1.Id = l2.Id - 1\n    AND l2.Id = l3.Id - 1\n    AND l1.Num = l2.Num\n    AND l2.Num = l3.Num\n;\n`
\n
'