180. Consecutive Numbers

링크: 180. Consecutive Numbers
난이도: Medium

---

문제 

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id is the primary key for this table.
id is an autoincrement column.

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.

Example 1:

Input: 
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.

---

풀이

SELECT DISTINCT num as ConsecutiveNums
FROM Logs
WHERE (Id + 1, num) in (SELECT * FROM Logs) and (Id + 2, num) in (SELECT * FROM Logs)

---

설명

WHERE (Id + 1, num) in (SELECT * FROM Logs) and (Id + 2, num) in (SELECT * FROM Logs);

 

As we're traversing the rows, we're trying to find the ideal condition for the 1st num we encounter, that is the two consecutive ids having the same num.

See here,
Logs Table in example was something like this:

| id | num |
| 1 | 1 | <---- We're here (id=1, num=1), checking for it's ideal condition 
| 2 | 1 | 
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |

Ideal condition for (id=1, num=1) would be if (id=2, num=1) & (id=3, num=1) exists. As you can see, we check whether these combos exists in Logs Table for the 1st row (in this example).

Note: we're checking only for the 2nd and 3rd consecutive (id, num).
This checking takes place throughout all the table for each rows, and we select just the distinct values at the end (in case there are more than 3 consecutive rows with same num).

Since ids are primary key of this table, then (id, num) combo will always be unique.

 

> 만약 id 값이 연속하지 않을 경우 안풀릴 것 같아서 다른 풀이 찾아봄, 보니까 LAG, LEAD 함수 써서 풀이하는 것도 있는데 패스함
이유: 일단 문제 풀이 의도는 저 함수를 쓰냐 아니냐를 보는 것이 아니고 충분히 저 솔루션 자체도 이해하는 것에 꽤 시간이 걸림