180. Consecutive Numbers

 

Consecutive Numbers

Problem Description

SQL Schema

Create table If Not Exists Logs (id int, num int)
Truncate table Logs
insert into Logs (id, num) values ('1', '1')
insert into Logs (id, num) values ('2', '1')
insert into Logs (id, num) values ('3', '1')
insert into Logs (id, num) values ('4', '2')
insert into Logs (id, num) values ('5', '1')
insert into Logs (id, num) values ('6', '2')
insert into Logs (id, num) values ('7', '2')

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.

Approach)  Self Join and DISTINCT

To find all numbers that appear at least three times consecutively, we perform self-join on Logs table so we have a previous number, next number, and current number all in the same row.

When the value of the current number equals the previous number and the next number, we know this number appears at least 3 times consecutively.

We use DISTINCT keyword to not return duplicate numbers in the output.

SELECT DISTINCT A.Num AS ConsecutiveNums
FROM Logs AS A
INNER JOIN Logs AS B ON A.Id + 1 = B.Id
INNER JOIN Logs AS C ON B.Id + 1 = C.Id
WHERE A.Num = B.Num
    AND B.Num = C.Num

Approach) Self Join and Group By

This is the same as the self join solution discussed above, we use Group By to ensure there is no duplicate number in the output.

SELECT A.Num AS ConsecutiveNums
FROM Logs AS A
INNER JOIN Logs AS B ON A.Id + 1 = B.Id
INNER JOIN Logs AS C ON B.Id + 1 = C.Id
WHERE A.Num = B.Num
    AND B.Num = C.Num
GROUP BY A.Num

Approach) Lag() and Lead() Window Functions

To find all numbers that appear at least three times consecutively, we utilize Lag() and Lead() Windows Functions to put the previous number, next number, and current number all in the same row.

When the value of the current number equals to the previous number and the next number, we know this number appears at least 3 times consecutively.

We then use DISTINCT keyword to not return duplicate numbers in the output.

WITH ConsecutiveNumAnalysis
AS (
    SELECT Id
        ,Num AS CurrNum
        ,lag(Num) OVER (
            ORDER BY Id
            ) AS PrevNum
        ,lead(Num) OVER (
            ORDER BY Id
            ) AS NextNum
    FROM Logs
    )
SELECT DISTINCT CurrNum AS ConsecutiveNums
FROM ConsecutiveNumAnalysis
WHERE CurrNum = PrevNum
    AND CurrNum = NextNum

Conclusion

That’s all folks! In this post, we solved LeetCode problem #180. Consecutive Numbers

I hope you have enjoyed this post. Feel free to share your thoughts on this.

You can find the complete source code on my GitHub repository. If you like what you learn. feel free to fork 🔪 and star ⭐ it.

In this blog, I have tried to collect & present the most important points to consider when improving Data structure and logic, feel free to add, edit, comment, or ask. For more information please reach me here
Happy coding!