180. Consecutive Numbers

* Problem

In SQL, id is the primary key for this table. id is an autoincrement column starting from 1.

Find all numbers that appear at least three times consecutively. Return the result table in any order. 

The result forma is in the following example.

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.

 

* Key Points✨

> Compare with 3 rows

    (1) LEAD() : Comparable with 1st, 2nd, 3rd data. - MySQL 8.0

    (2) LAG() : Comparable with past data.

    (3) SELF JOIN - MySQL 5.X (Possible performance degradation)

###### Step 01
SELECT Num,
       LEAD(Num, 1) OVER (ORDER BY Id) AS next1,
       LEAD(Num, 2) OVER (ORDER BY Id) AS next2
    FROM Logs

-- Output
| Num | next1 | next2 |
| --- | ----- | ----- |
| 1   | 1     | 1     |
| 1   | 1     | 2     |
| 1   | 2     | 1     |
| 2   | 1     | 2     |
| 1   | 2     | 2     |
| 2   | 2     | null  |
| 2   | null  | null  |

###### Step 02
SELECT DISTINCT Num AS ConsecutiveNums
    FROM (
        SELECT Num,
           LEAD(Num, 1) OVER (ORDER BY Id) AS next1,
           LEAD(Num, 2) OVER (ORDER BY Id) AS next2
        FROM Logs
    ) temp

-- Output
| ConsecutiveNums |
| --------------- |
| 1               |
| 2               |

###### Step 03
SELECT DISTINCT Num AS ConsecutiveNums
    FROM (
        SELECT Num,
           LEAD(Num, 1) OVER (ORDER BY Id) AS next1,
           LEAD(Num, 2) OVER (ORDER BY Id) AS next2
        FROM Logs
    ) temp
WHERE Num = next1 AND Num = next2;

-- Output
| ConsecutiveNums |
| --------------- |
| 1               |

 

* Solution(Success)

# Write your MySQL query statement below
####################################################################
# CASE 02 - LEAD()
SELECT DISTINCT Num AS ConsecutiveNums
FROM (
    SELECT Num,
           LEAD(Num, 1) OVER (ORDER BY Id) AS next1,
           LEAD(Num, 2) OVER (ORDER BY Id) AS next2
    FROM Logs
) temp
WHERE Num = next1 AND Num = next2;
####################################################################
# CASE 02 - LAG()
SELECT DISTINCT Num AS ConsecutiveNums
FROM (
    SELECT Num,
           LAG(Num, 1) OVER (ORDER BY Id) AS prev1,
           LAG(Num, 2) OVER (ORDER BY Id) AS prev2
    FROM Logs
) temp
WHERE Num = prev1 AND Num = prev2;

 

* Solution(Failed)

Like a fool🤣🤣, I didn't read the problem properly and just looked at a single example before jumping to an answer… and of course, I got it wrong right away! 🤦‍♂️ Once again, I learned the hard way how important it is to read the problem carefully.

# Write your MySQL query statement below
SELECT num AS ConsecutiveNums 
    FROM Logs
    GROUP BY num
    HAVING COUNT(id) > 3