Finding gaps in sequences in SQL - Step-by-Step Execution

Concept Flow - Finding gaps in sequences

Start with ordered sequence

↓

Compare each value with previous

↓

Check if difference > 1

↓

Record gap

↓

End of sequence

We look at each number in order and compare it to the one before. If the difference is more than 1, we found a gap.

Execution Sample

SQL

WITH ordered AS (
  SELECT id, value, LAG(value) OVER (ORDER BY value) AS prev_value
  FROM numbers
)
SELECT prev_value + 1 AS gap_start, value - 1 AS gap_end
FROM ordered
WHERE value - prev_value > 1;

This SQL finds gaps in a sequence of numbers by comparing each number to the previous one.

Execution Table

Step	id	value	prev_value	value - prev_value	Gap Found?	Output (gap_start, gap_end)
1	1	1	NULL	NULL	No	NULL
2	2	2	1	1	No	NULL
3	3	4	2	2	Yes	(3, 3)
4	4	5	4	1	No	NULL
5	5	7	5	2	Yes	(6, 6)
6	6	8	7	1	No	NULL
7	7	10	8	2	Yes	(9, 9)
8	End	NULL	NULL	NULL	Stop	No more rows

💡 Reached end of sequence, no more rows to compare.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	Final
value	NULL	1	2	4	5	7	8	10	NULL
prev_value	NULL	NULL	1	2	4	5	7	8	NULL
value - prev_value	NULL	NULL	1	2	1	2	1	2	NULL
Gap Found?	No	No	No	Yes	No	Yes	No	Yes	No more rows

Key Moments - 3 Insights

Why is prev_value NULL on the first row?

Because there is no previous row for the first value, LAG returns NULL as shown in execution_table step 1.

Why do we check if value - prev_value > 1 to find gaps?

If the difference is more than 1, it means numbers are missing between prev_value and value, as seen in steps 3, 5, and 7.

What does the output (gap_start, gap_end) represent?

It shows the start and end of the missing numbers between two existing values, calculated as prev_value + 1 and value - 1.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of prev_value at step 4?

A 2

B 4

C NULL

D 5

💡 Hint

Check the 'prev_value' column in execution_table row for step 4.

At which step does the first gap get detected?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look for the first 'Yes' in the 'Gap Found?' column in execution_table.

If the sequence had no missing numbers, what would the 'Gap Found?' column show?

A All No

B All Yes

C Alternating Yes and No

D NULL values

💡 Hint

Refer to the condition 'value - prev_value > 1' in execution_table; if no gaps, difference is always 1.

Concept Snapshot

Finding gaps in sequences:
- Use LAG() to get previous value in order
- Compare current and previous values
- If difference > 1, gap exists
- Output missing ranges as prev_value+1 to value-1
- Works well for ordered numeric sequences

Full Transcript

This visual execution shows how to find gaps in a sequence of numbers using SQL. We start by ordering the numbers and using the LAG function to get the previous number for each row. Then we calculate the difference between the current and previous values. If this difference is greater than one, it means there is a gap. We record the start and end of the gap by adding one to the previous value and subtracting one from the current value. The execution table traces each step, showing when gaps are found and what output is produced. Key moments clarify why the first previous value is NULL and how the gap detection works. The quiz tests understanding of these steps by referencing the execution table and variable changes.