Consecutive Numbers(LeetCode)

https://leetcode.com/problems/consecutive-numbers/

Consecutive Numbers - LeetCode

 

 

문제

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.

 

 

정답쿼리1

SELECT l_1.num AS ConsecutiveNums
FROM logs l_1
INNER JOIN logs l_2 ON l_1.id + 1 = l_2.id
INNER JOIN logs l_3 ON l_1.id + 2 = l_3.id
WHERE l_1.num = l_2.num AND l_1.num = l_3.num

 

 

정답쿼리2

SELECT DISTINCT l.num AS ConsecutiveNums
FROM (SELECT num,
             LEAD(num, 1) OVER (ORDER BY id) AS next,
             LEAD(num, 2) OVER (ORDER BY id) AS afternext
      FROM Logs) AS l
WHERE l.num = l.next AND l.num = l.afternext