Consecutive Numbers SQL Challenge - PostgreSQL Solution

1. Introduction

This blog post explores how to identify sequences of consecutive numbers in a PostgreSQL database. This is a common challenge when analyzing data sets that include numerical sequences.

Problem

We need to write a PostgreSQL query that finds all numbers that appear consecutively three or more times in a given table.

Database Table:

Logs Table:

Id	Num
1	20
2	20
3	20

3. PostgreSQL Solution

WITH RankedLogs AS (
    SELECT Num,
           LEAD(Num, 1) OVER (ORDER BY Id) AS NextNum,
           LEAD(Num, 2) OVER (ORDER BY Id) AS NextNextNum
    FROM Logs
)
SELECT DISTINCT Num AS ConsecutiveNums
FROM RankedLogs
WHERE Num = NextNum AND Num = NextNextNum;

Output:

ConsecutiveNums
20

Explanation:

1. The query uses a window function with LEAD to compare each row (Num) with the next two rows (NextNum and NextNextNum).

2. It checks if the current row's number matches the next two consecutive row's numbers.

3. The WITH clause creates a temporary result set (RankedLogs) for this comparison.

4. The query returns distinct numbers that appear consecutively at least three times.