Consecutive Numbers - SQL

The "Consecutive Numbers" problem on LeetCode is a fascinating SQL challenge that tests your skills with window functions and logical problem-solving. It requires identifying numbers that appear consecutively three or more times in a table. Let's break down the problem and look at the solutions in MySQL, PostgreSQL, and Oracle. 

Problem Overview 

We are given a table named Logs with columns id (primary key and autoincrement) and num. The goal is to find all numbers that appear at least three times consecutively. 

The Logs Table Structure

id	num
1	1
2	1
3	1

SQL Query Solutions 

To solve this problem, we can utilize SQL window functions. We'll use LAG and LEAD functions to look at the previous and next values in the dataset and compare them. 

MySQL Solution 

In MySQL, we can use a common table expression (CTE) with window functions:

WITH RankedLogs AS (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev_num,
        LAG(num, 2) OVER (ORDER BY id) AS prev_prev_num
    FROM
        Logs
)
SELECT DISTINCT
    num AS ConsecutiveNums
FROM
    RankedLogs
WHERE
    num = prev_num AND num = prev_prev_num;

This query checks for each row if the current num is the same as the two preceding num values. 

PostgreSQL Solution 

The approach in PostgreSQL is similar:

-- Write your PostgreSQL query statement below
WITH RankedLogs AS (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev_num,
        LAG(num, 2) OVER (ORDER BY id) AS prev_prev_num
    FROM
        Logs
)
SELECT DISTINCT
    num AS ConsecutiveNums
FROM
    RankedLogs
WHERE
    num = prev_num AND num = prev_prev_num;

Oracle Solution 

In Oracle, the concept remains the same but uses Oracle's PL/SQL syntax:

/* Write your PL/SQL query statement below */
WITH RankedLogs AS (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev_num,
        LAG(num, 2) OVER (ORDER BY id) AS prev_prev_num
    FROM
        Logs
)
SELECT DISTINCT
    num AS ConsecutiveNums
FROM
    RankedLogs
WHERE
    num = prev_num AND num = prev_prev_num;

Expected Output 

For the given example, the output would be:

ConsecutiveNums
1

Conclusion 

The "Consecutive Numbers" problem is an excellent exercise to understand and apply window functions in SQL. It showcases how to use LAG to look at preceding rows and how to construct a logical condition to find consecutive occurrences. This kind of problem is typical in analyzing sequences and patterns in datasets, making it a valuable skill for data analysis and database management.