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