# Find Three Consecutive Numbers via Leetcode

- Apr 7 • 5 min read
- Key Terms: window function, LAG() function, LEAD() function, WHERE clause, subquery

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 |