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.


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;




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.