Finding gaps in sequences in SQL - Mini Project: Build & Apply

Finding Gaps in Sequences with SQL

📖 Scenario: You work for a library that tracks book IDs assigned sequentially. Sometimes, IDs are missing due to errors or removals. You want to find these missing book IDs to fix the records.

🎯 Goal: Build an SQL query that finds missing numbers (gaps) in a sequence of book IDs stored in a table.

📋 What You'll Learn

Create a table called books with a column book_id containing these exact values: 1, 2, 3, 5, 6, 8, 9, 10

Create a variable or CTE called max_id that stores the maximum book_id from the books table

Generate a sequence of numbers from 1 to max_id using a recursive CTE called sequence

Write a query that selects numbers from sequence that are not present in books to find missing book_ids

💡 Why This Matters

🌍 Real World

Finding missing IDs or sequence gaps is common in inventory, order tracking, or any system that uses sequential numbering.

💼 Career

Database developers and analysts often need to detect missing records or gaps to ensure data integrity and correct errors.

1

Create the books table with sample data

Create a table called books with a single column book_id and insert these exact values: 1, 2, 3, 5, 6, 8, 9, 10.

SQL

-- Create table books and insert book_id values
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO to add the exact book IDs.

2

Find the maximum book_id

Create a Common Table Expression (CTE) called max_id that selects the maximum book_id from the books table.

SQL

CREATE TABLE books (book_id INT);
INSERT INTO books (book_id) VALUES (1), (2), (3), (5), (6), (8), (9), (10);

-- Create CTE max_id to find max book_id
WITH max_id AS (
    -- Your code here
)

Hint

Use SELECT MAX(book_id) AS max_book_id FROM books inside the CTE.

3

Generate a sequence from 1 to max_id

Extend the CTEs by adding a recursive CTE called sequence that generates numbers from 1 up to max_book_id from the max_id CTE.

SQL

CREATE TABLE books (book_id INT);
INSERT INTO books (book_id) VALUES (1), (2), (3), (5), (6), (8), (9), (10);

WITH max_id AS (
    SELECT MAX(book_id) AS max_book_id FROM books
),
sequence AS (
    -- Your code here
)

Hint

Use a recursive CTE starting at 1 and increment by 1 until it reaches max_book_id.

4

Find missing book IDs by comparing sequence to books

Write a final query that selects numbers from the sequence CTE that are not present in the books table to find missing book_ids.

SQL

CREATE TABLE books (book_id INT);
INSERT INTO books (book_id) VALUES (1), (2), (3), (5), (6), (8), (9), (10);

WITH max_id AS (
    SELECT MAX(book_id) AS max_book_id FROM books
),
sequence AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM sequence, max_id WHERE num < max_id.max_book_id
)

-- Select missing book_ids from sequence not in books
-- Your code here

Hint

Use WHERE num NOT IN (SELECT book_id FROM books) to find missing IDs.

Practice

1. What is the main purpose of using the LEAD() function when finding gaps in a sequence of numbers in SQL?

easy

A. To find the maximum number in the sequence

B. To compare each number with the next number in the sequence

C. To sum all numbers in the sequence

D. To sort the numbers in descending order

Solution

1. Step 1: Understand the role of LEAD()

   The LEAD() function allows you to look at the next row's value in the current row's context.

2. Step 2: Identify how LEAD() helps find gaps

   By comparing the current number with the next number, you can detect if there is a gap (difference greater than 1).

3. Final Answer:

   To compare each number with the next number in the sequence -> Option B

4. Quick Check:

   LEAD() compares current and next values [OK]

Hint: Use LEAD() to peek at the next number in sequence [OK]

Common Mistakes:

• Thinking LEAD() sums values
• Confusing LEAD() with aggregate functions
• Using LEAD() to sort data

2. Which of the following SQL snippets correctly uses LEAD() to find gaps in a sequence column named num in table numbers?

easy

A. SELECT num, LEAD(num) AS next_num FROM numbers;

B. SELECT num, LEAD(num) FROM numbers ORDER BY num;

C. SELECT num, LEAD(num) OVER (ORDER BY num) AS next_num FROM numbers;

D. SELECT num, LEAD(num) OVER () AS next_num FROM numbers;

Solution

1. Step 1: Check LEAD() syntax

   The correct syntax requires LEAD(column) OVER (ORDER BY column) to define the order for the window function.

2. Step 2: Identify the correct option

   SELECT num, LEAD(num) OVER (ORDER BY num) AS next_num FROM numbers; uses LEAD(num) OVER (ORDER BY num), which is the correct form to get the next number in order.

3. Final Answer:

   SELECT num, LEAD(num) OVER (ORDER BY num) AS next_num FROM numbers; -> Option C

4. Quick Check:

   LEAD() needs OVER with ORDER BY [OK]

Hint: Always include OVER(ORDER BY column) with LEAD() [OK]

Common Mistakes:

• Omitting OVER clause
• Not specifying ORDER BY inside OVER
• Using LEAD() without alias

3. Given the table numbers with column num containing values (1, 2, 4, 5, 7), what will the following query return?

SELECT num, LEAD(num) OVER (ORDER BY num) AS next_num FROM numbers;

medium

A. [{num:1, next_num:2}, {num:2, next_num:4}, {num:4, next_num:5}, {num:5, next_num:7}, {num:7, next_num:null}]

B. [{num:1, next_num:1}, {num:2, next_num:2}, {num:4, next_num:4}, {num:5, next_num:5}, {num:7, next_num:7}]

C. [{num:1, next_num:3}, {num:2, next_num:4}, {num:4, next_num:6}, {num:5, next_num:7}, {num:7, next_num:null}]

D. [{num:1, next_num:null}, {num:2, next_num:null}, {num:4, next_num:null}, {num:5, next_num:null}, {num:7, next_num:null}]

Solution

1. Step 1: Understand LEAD() output

   LEAD(num) returns the next row's num value ordered by num. So each row shows its own num and the next number.

2. Step 2: Apply to given data

   For values 1,2,4,5,7:
   1's next is 2
   2's next is 4
   4's next is 5
   5's next is 7
   7 has no next, so null

3. Final Answer:

   [{num:1, next_num:2}, {num:2, next_num:4}, {num:4, next_num:5}, {num:5, next_num:7}, {num:7, next_num:null}] -> Option A

4. Quick Check:

   LEAD() shifts values down by one [OK]

Hint: LEAD() shows next row's value or null if none [OK]

Common Mistakes:

• Assuming LEAD() repeats current value
• Expecting gaps filled automatically
• Confusing LEAD() with LAG()

4. The following query is intended to find gaps in the num sequence but returns no rows:

SELECT num FROM numbers WHERE LEAD(num) OVER (ORDER BY num) - num > 1;

What is the error?

medium

A. You cannot use window functions directly in WHERE clause

B. The ORDER BY inside LEAD() is missing

C. The subtraction operator is invalid here

D. LEAD() requires a GROUP BY clause

Solution

1. Step 1: Understand window function restrictions

   Window functions like LEAD() cannot be used directly in the WHERE clause because WHERE filters rows before window functions are applied.

2. Step 2: Correct approach

   Use a subquery or CTE to compute LEAD() first, then filter in an outer query.

3. Final Answer:

   You cannot use window functions directly in WHERE clause -> Option A

4. Quick Check:

   Window functions not allowed in WHERE [OK]

Hint: Use subquery or CTE to filter on window function results [OK]

Common Mistakes:

• Trying to filter window function in WHERE
• Missing OVER clause
• Expecting LEAD() to work like aggregate

5. You have a table orders with an integer column order_id that should be sequential but may have missing numbers. Which query correctly finds all missing order_id values between the minimum and maximum present?

hard

A. SELECT order_id FROM orders WHERE LEAD(order_id) - order_id > 1;

B. SELECT DISTINCT order_id + 1 FROM orders WHERE order_id + 1 NOT IN (SELECT order_id FROM orders);

C. SELECT order_id FROM orders WHERE order_id NOT IN (SELECT order_id FROM orders);

D. WITH seq AS (SELECT generate_series(MIN(order_id), MAX(order_id)) AS id FROM orders) SELECT id FROM seq LEFT JOIN orders ON seq.id = orders.order_id WHERE orders.order_id IS NULL;

Solution

1. Step 1: Generate full sequence range

   Use generate_series from minimum to maximum order_id to create all possible IDs.

2. Step 2: Find missing IDs by left join

   Left join this sequence to existing orders and select IDs where no matching order exists (NULL in orders).

3. Final Answer:

   WITH seq AS (SELECT generate_series(MIN(order_id), MAX(order_id)) AS id FROM orders) SELECT id FROM seq LEFT JOIN orders ON seq.id = orders.order_id WHERE orders.order_id IS NULL; -> Option D

4. Quick Check:

   Generate full range, then find missing with LEFT JOIN [OK]

Hint: Generate full range then find missing with LEFT JOIN [OK]

Common Mistakes:

• Using LEAD() alone misses multiple gaps
• Incorrect NOT IN subquery logic
• Assuming order_id + 1 always missing