Finding gaps in sequences in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find missing invoice numbers in a sequence

Given a table Invoices with a column InvoiceNumber containing sequential invoice numbers, which query correctly finds all missing invoice numbers between the minimum and maximum?

SQL

CREATE TABLE Invoices (InvoiceNumber INT);
INSERT INTO Invoices VALUES (1001), (1002), (1004), (1005), (1007);

A SELECT InvoiceNumber AS MissingNumber FROM Invoices WHERE InvoiceNumber NOT IN (SELECT InvoiceNumber + 1 FROM Invoices);

B SELECT InvoiceNumber - 1 AS MissingNumber FROM Invoices WHERE InvoiceNumber - 1 NOT IN (SELECT InvoiceNumber FROM Invoices);

C SELECT InvoiceNumber + 2 AS MissingNumber FROM Invoices WHERE InvoiceNumber + 2 NOT IN (SELECT InvoiceNumber FROM Invoices);

D SELECT InvoiceNumber + 1 AS MissingNumber FROM Invoices WHERE InvoiceNumber + 1 NOT IN (SELECT InvoiceNumber FROM Invoices);

💡 Hint

Think about checking the next number after each existing invoice to see if it is missing.

Option D checks for each invoice number if the next number is missing by adding 1 and verifying it is not in the table. This correctly finds missing numbers like 1003 and 1006.

📝 Syntax

intermediate

Identify the syntax error in gap detection query

Which of the following SQL queries will cause a syntax error when trying to find missing IDs in a sequence?

SQL

CREATE TABLE Items (ID INT);
INSERT INTO Items VALUES (1), (2), (4), (5);

A SELECT ID + 1 AS MissingID FROM Items WHERE ID + 1 NOT IN (SELECT ID FROM Items);

B SELECT ID + 1 AS MissingID FROM Items WHERE ID + 1 NOT EXISTS (SELECT ID FROM Items);

C SELECT ID + 1 AS MissingID FROM Items WHERE NOT EXISTS (SELECT 1 FROM Items WHERE ID = Items.ID + 1);

D ;)smetI MORF DI TCELES( NI TON 1 + DI EREHW smetI MORF DIgnissiM SA 1 + DI TCELES

💡 Hint

Check the use of NOT EXISTS and NOT IN clauses.

Option B uses NOT EXISTS incorrectly with a value expression; it requires a subquery. This causes a syntax error.

❓ optimization

advanced

Optimize gap detection for large sequences

You have a large table Orders with millions of sequential OrderID values. Which query is the most efficient to find missing OrderID values?

A SELECT o1.OrderID + 1 AS MissingID FROM Orders o1 LEFT JOIN Orders o2 ON o1.OrderID + 1 = o2.OrderID WHERE o2.OrderID IS NULL;

B

WITH Numbers AS (SELECT generate_series(MIN(OrderID), MAX(OrderID)) AS num FROM Orders)
SELECT num AS MissingID FROM Numbers WHERE num NOT IN (SELECT OrderID FROM Orders);

C SELECT OrderID + 1 AS MissingID FROM Orders WHERE OrderID + 1 NOT IN (SELECT OrderID FROM Orders);

D SELECT DISTINCT OrderID FROM Orders WHERE OrderID NOT IN (SELECT OrderID + 1 FROM Orders);

💡 Hint

Consider join operations versus subqueries for performance on large data.

Option A uses a LEFT JOIN to find missing IDs efficiently by checking for absence in the join, which is generally faster than subqueries or generate_series on large tables.

🧠 Conceptual

advanced

Understanding gap detection logic

Why does the query SELECT ID + 1 FROM Table WHERE ID + 1 NOT IN (SELECT ID FROM Table) correctly find missing numbers in a sequence?

A Because it checks for each ID if the next number is missing by verifying it is not present in the table.

B Because it selects all IDs and adds 1 to them regardless of presence in the table.

C Because it finds IDs that are duplicated in the table.

D Because it returns all IDs that are present in the table plus one.

💡 Hint

Think about what ID + 1 NOT IN (SELECT ID) means logically.

The query checks for each existing ID if the next consecutive number is missing by confirming it is not in the table, thus identifying gaps.

🔧 Debug

expert

Debugging incorrect gap detection query

The following query is intended to find missing sequence numbers in SeqTable but returns no results even though gaps exist. What is the reason?

SELECT seq + 1 AS MissingSeq FROM SeqTable WHERE seq + 1 NOT IN (SELECT seq FROM SeqTable);

Table data: seq = 1, 2, 4, 5

A The subquery uses the wrong column name causing no matches.

B The query syntax is invalid and causes an error.

C The query misses the last gap because it only checks seq + 1 for existing seq values, so it cannot find gaps after the max value.

D The query should use <code>NOT EXISTS</code> instead of <code>NOT IN</code> to work correctly.

💡 Hint

Consider how the query checks for missing numbers and what happens at the sequence boundaries.

The query only checks for missing numbers immediately after existing seq values. It cannot detect gaps before the minimum or after the maximum sequence number, so some gaps may be missed.