Consecutive Numbers

Consecutive Numbers

What is this problem about?

The Consecutive Numbers interview question is a medium-level SQL problem. You are given a Logs table with an Id column and a Num column. Your goal is to find all numbers that appear at least three times consecutively. This means the same number must appear in three rows with consecutive Id values.

Why is this asked in interviews?

Companies like Microsoft and Uber use the Consecutive Numbers coding problem to test a candidate's ability to perform analytical queries. It requires understanding how to look across multiple rows to identify trends, which is a core skill for data engineers and backend developers working with event logs or audit trails.

Algorithmic pattern used

This follows the Database interview pattern. There are two popular ways to solve this:

1. Triple Self-Join: Join the Logs table with itself twice (Logs l1, Logs l2, Logs l3) where l1.id = l2.id - 1 and l2.id = l3.id - 1, ensuring l1.Num = l2.Num and l2.Num = l3.Num.
2. Window Functions: Use LEAD() or LAG() twice to peek at the next/previous two rows and check if the numbers match.

Example explanation

Suppose your log table looks like this:

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

In this case, only the number 1 appears three times in a row (IDs 1, 2, and 3). Number 2 appears twice at the end, but that doesn't meet the "at least three" requirement.

Common mistakes candidates make

• Not handling DISTINCT: If a number appears consecutively 4 or 5 times, it might show up multiple times in the results unless you use SELECT DISTINCT.
• Assuming IDs are consecutive: If IDs have gaps (e.g., 1, 2, 5, 6, 7), simply checking id + 1 might fail. You might need to use ROW_NUMBER() to create a truly continuous sequence first.
• Performance: Self-joining large tables three times can be very slow compared to a single pass using window functions.

Interview preparation tip

Be ready to explain the trade-offs between a JOIN approach and a Window Function approach. Window functions are generally preferred in modern SQL for their efficiency and readability.