Consecutive Numbers | Leetcode 180 | Crack SQL Interviews in 50 Qs

Welcome to Question 33 of the Crack SQL Interview in 50 Questions series. In this chapter, we explore how to find numbers that appear consecutively at least three times in a database table. This problem introduces a powerful self-join pattern used when you need to compare multiple rows within the exact same database table.

1. Problem Statement & Database Schema

The problem asks us to find all numbers in a given table that appear at least three times consecutively.

The `Logs` Table Schema

id: An integer column acting as the Primary Key. It is an auto-incrementing sequence (1, 2, 3, 4, ...).
num: A variable character (VARCHAR) column containing the recorded numbers.

Example Dataset

id	num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

Objective

Find all numbers that appear at least three times consecutively.

Looking at our example, the number 1 appears at id = 1, id = 2, and id = 3 consecutively.
The number 2 appears multiple times, but its occurrences are split (id = 4 alone, and id = 6, 7 together), so it is not consecutive three times.
Expected Output: Return 1 (aliased as ConsecutiveNums).

2. Core Mental Model: The Multi-Instance Table Analogy

Real-Life Analogy

Imagine three security guards standing at consecutive guard posts along a fence corridor (Post 1, Post 2, Post 3). To check if the exact same type of cargo crate (num) is passing down the line sequentially, you cannot just look at a single post. You must assign three separate inspectors (l1, l2, l3) to look at three adjacent positions simultaneously and check two things:

Are their positions consecutive? (Post 2 is Post 1 + 1, and Post 3 is Post 2 + 1).
Are their cargo items identical? (Crate at Post 1 = Crate at Post 2 = Crate at Post 3).

In SQL, when you need to compare different rows within the same table side-by-side, you create multiple aliases of that table and join or cross-reference them.

3. Naive vs. Optimal Approach

Strategy	Approach	Why it is Naive / Optimal
Naive Approach (Window Functions)	Using `LEAD()` or `LAG()` window functions.	Highly efficient and modern, but interviewing platforms or older SQL dialects often challenge candidates to solve row-to-row comparisons using classic self-join logic.
Optimal / Classical Approach	Multi-Instance Self-Referencing (Cross Join / Inner Join via WHERE)	Explicitly aligns three table instances (`l1`, `l2`, `l3`) to verify both sequential IDs and matching values. Excellent for testing fundamental relational logic.

4. Step-by-Step Evolution of the Query

To evaluate three consecutive rows, we instantiate the Logs table three times under different aliases:

Logs l1 represents the first row.
Logs l2 represents the second consecutive row.
Logs l3 represents the third consecutive row.

CODE

FROM Logs l1, Logs l2, Logs l3

Step 4.1: Selecting and Aliasing the Output Column

We want to select the number column and rename it to ConsecutiveNums as required by the problem statement:

SQL

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3

Note: We use DISTINCT because a number might satisfy the consecutive condition multiple times, and we only want to list unique qualifying numbers in our final output.

5. Crafting the Where Clause Conditions

The WHERE clause must handle two distinct checks simultaneously: ID Consecutiveness and Value Equality.

SQL

WHERE l1.id = l2.id - 1
  AND l2.id = l3.id - 1
  AND l1.num = l2.num
  AND l2.num = l3.num

Breaking Down the Conditions:

ID Continuity Check:
- l1.id = l2.id - 1: Ensures l2 immediately follows l1.
- l2.id = l3.id - 1: Ensures l3 immediately follows l2.
- Combined, this guarantees we are looking at three strictly adjacent rows (e.g., IDs 1, 2, 3).
Value Matching Check:
- l1.num = l2.num and l2.num = l3.num: Ensures that the values stored across these three consecutive rows are identical.

6. Complete Production-Ready SQL Query

Here is the complete, working SQL query that successfully passes LeetCode 180:

SQL

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE l1.id = l2.id - 1
  AND l2.id = l3.id - 1
  AND l1.num = l2.num
  AND l2.num = l3.num;

Complete Execution Flow Walkthrough

Structure & Concept Flow

Logs Table Instance l1 (id=1, num=1)

+---> Checked against l2 (id=2, num=1) --> IDs match (1 = 2 - 1) & Nums match (1 = 1)

+---> Checked against l3 (id=3, num=1) --> IDs match (2 = 3 - 1) & Nums match (1 = 1)

Qualifies! Returns l1.num (1) -> Distinctified -> Output: 1

7. Real-World Practical Pitfalls & Interview Tips

Missing DISTINCT: If a number appears four or five times consecutively, multiple combinations of (l1, l2, l3) will match. Omitting DISTINCT will result in duplicate rows in your output.
Performance on Large Datasets: Using implicit cross joins (FROM Logs l1, Logs l2, Logs l3) without proper indexes on id can cause performance bottlenecks ( $O(N^3)$ complexity if unoptimized). In production environments with millions of rows, window functions like LEAD() or LAG() are preferred.
Primary Key Dependency: This specific logic relies on id being sequential and auto-incrementing without gaps. If your table has deleted rows resulting in ID gaps (e.g., IDs 1, 2, 5), this ID-subtraction method will miss consecutive entries, requiring window-function partition approaches instead.

8. Summary and Key Takeaways

Multi-Instance Aliasing: Use multiple table aliases (l1, l2, l3) when you need to compare adjacent or related rows within the same database table.
Two-Part Validation: Always separate your checks into structural continuity (id = id - 1) and data validation (num = num).
Handling Duplicates: Always apply DISTINCT when searching for recurring sequence patterns to prevent duplicate result entries.
Alternative Approaches: While self-joins are intuitive for small-scale datasets and classic interview problems, be ready to discuss window functions (LEAD/LAG) as modern production alternatives.