Consecutive Numbers SQL Query | SQL Interview Question

Problem statement

Prompt

Given the logs table below (rows ordered by id), write a query that returns the distinct numbers that appear three or more times consecutively — that is, in at least three adjacent rows.

Output a single column: consecutive_num.
Each qualifying number appears once, even if it has several runs.

Example

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

Expected result:

consecutive_num
1

Constraints

logs.id is the primary key and is contiguous (no gaps).

Tips

Compare each row with the two previous rows using LAG(num, 1) and LAG(num, 2).
num may be NULL in some variants — a run of three NULLs still counts here only if your comparison treats them as equal; in SQLite NULL = NULL is false, so filter with num IS NOT NULL or use a self join.

Schema

CREATE TABLE logs (
    id  INTEGER PRIMARY KEY,
    num INTEGER NOT NULL
);

Sample data

INSERT INTO logs (id, num) VALUES
  (1, 1),
  (2, 1),
  (3, 1),
  (4, 2),
  (5, 1),
  (6, 2),
  (7, 2);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: distinct numbers appearing in at least three consecutive rows.
SELECT DISTINCT num AS consecutive_num
FROM (
    SELECT num,
           LAG(num, 1) OVER (ORDER BY id) AS prev1,
           LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM logs
) t
WHERE num = prev1 AND num = prev2
ORDER BY num;

Key concepts

LAG
Window functions
Consecutive detection

Consecutive Numbers