# 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.

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.