Skip to main content

Consecutive Numbers(LeetCode)

Problem Statement​

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+

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

Find all numbers that appear at least three times consecutively.

Return the result table in any order.

Examples​

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.

Solution​

Approach​

  1. We will first make creates three different aliases (log_1, log_2, and log_3) for the same table logs. It allows us to compare different rows within the same table.

  2. Then simply use WHERE clause to get the result where id's and num in a row are consecutive and occurence of number is 3 times. The conditions that must be met for the rows to be included in the result:

    • log_1.id = log_2.id + 1: The id of log_1 should be one more than the id of log_2. This means log_1 is immediately      after log_2.
    • log_2.id = log_3.id + 1: Similarly, the id of log_2 should be one more than the id of log_3. This means log_2 is      immediately after log_3.
    • log_1.num = log_2.num: The num value of log_1 should be equal to the num value of log_2.
    • log_2.num = log_3.num: The num value of log_2 should be equal to the num value of log_3.

        Together, these conditions ensure that the three rows have consecutive id values and the same num value.

  1. Then from the result we will select the distinct numbers and then put inside a column named 'ConsecutiveNums'. numbers

Implementation​

SELECT distinct 
    log_1.num as ConsecutiveNums 
FROM 
    logs log_1,
    logs log_2,
    logs log_3
WHERE 
    log_1.id=log_2.id+1 AND 
    log_2.id=log_3.id+1 AND 
    log_1.num=log_2.num AND 
    log_2.num=log_3.num

Conclusion​

This approach efficiently solves the problem of finding out the consecutive numbers.