SQL Window Functions Problem Solution

Find Three Consecutive Numbers via Leetcode

In extreme detail, in this tutorial I'll walk through a Leetcode medium level SQL problem to arrive to the solution.

Problem Statement

Write a SQL query to find all numbers that appear at least three times consecutively.

Id Num
1 1
2 1
3 1
4 2
5 1
6 2
7 2

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

ConsecutiveNums
1

Dan's Step by Step Solution

Create New Columns for Lag and Lead Numbers From Num Column

Explanation of SQL Query Below:

Given the Logs table above, I'm curious for each record, what's the following row number and previous row number. If I return an output in which the original number in the row, following row number and previous row number are all the same, then I know that number appears consecutively at least three times.

We can use window functions to compare previous and following rows in a table.

The LEAD() function creates a new column of the following row's Num from the original table - exactly how it's ordered with Id values in descending order. We alias this column as following row number.

The LAG() function creates a new column of the following row's Num from the original table - exactly how it's ordered with Id values in descending order. We alias this column as previous row number.

Learn more about LEAD() and LAG() functions in Mode's great tutorial on window functions.

SQL Query:

SELECT Num AS "number", 
       LEAD(Num) OVER (ORDER BY Id DESC) AS "following row number", 
       LAG(Num) OVER (ORDER BY Id DESC) AS "previous row number"
FROM Logs

Returned Output:

number following row number previous row number
1 1 null
1 1 1
1 2 1
2 1 1
1 2 2
2 2 1
2 null 2

Return Rows with Same Number in All Three Columns

Given the output above, we can see one row in which number is equal to following row number which is equal to previous row number. That number, 1, is the solution of a number that appears consecutively for at least three times.

Explanation of SQL Query Below:

Given the previous query's output, we can use a subquery to just return one row in which number is equal to following row number which is equal to previous row number. We filter our underlying table, sub, with the WHERE clauses in the last two lines of the query.

SQL Query:

SELECT *
FROM (
SELECT Num AS "number", 
       LEAD(Num) OVER (ORDER BY Id DESC) AS "following row number", 
       LAG(Num) OVER (ORDER BY Id DESC) AS "previous row number"
FROM Logs
) sub
WHERE sub."number" = sub."following row number"
AND sub."following row number" = sub."previous row number"

Returned Output:

number following row number previous row number
1 1 1

Return Distinct number From Previous Query

Explanation of SQL Query Below:

Given our output above, we want to filter this output to just return the distinct values that appear in the number column which should just be 1 for this example.

Use the DISTINCT statement to select only distinct values in the number column in our sub table and alias this column as ConsecutiveNums to match Leetcode's desired solution.

SQL Query:

SELECT DISTINCT sub."number" AS "ConsecutiveNums"
FROM (
SELECT Num AS "number", 
       LEAD(Num) OVER (ORDER BY Id DESC) AS "following row number", 
       LAG(Num) OVER (ORDER BY Id DESC) AS "previous row number"
FROM Logs
) sub
WHERE sub."number" = sub."following row number"
AND sub."following row number" = sub."previous row number"

Returned Output:

ConsecutiveNums
1