Which query correctly finds all missing order_id values between the minimum and maximum present?
hard📝 Application Q15 of 15
SQL - Advanced Query Patterns
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?
ASELECT order_id FROM orders WHERE LEAD(order_id) - order_id > 1;
BSELECT DISTINCT order_id + 1 FROM orders WHERE order_id + 1 NOT IN (SELECT order_id FROM orders);
CSELECT order_id FROM orders WHERE order_id NOT IN (SELECT order_id FROM orders);
DWITH 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;
Step-by-Step Solution
Solution:
Step 1: Generate full sequence range
Use generate_series from minimum to maximum order_id to create all possible IDs.
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).
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
Quick Check:
Generate full range, then find missing with LEFT JOIN [OK]
Quick Trick: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
Master "Advanced Query Patterns" in SQL
9 interactive learning modes - each teaches the same concept differently
