SQL Interview Questions – Find the Missing IDs

Problem Description –

Write an SQL query to find the missing customer IDs. The missing IDs are ones that are not in the Customers table but are in the range between 1 and the maximum customer_id present in the table.

Notice that the maximum customer_id will not exceed 100.

Return the result table ordered by ids in ascending order.

The query result format is in the following example.

Problem Link – Find Missing IDs

Difficulty Level – Medium

Solution –

-- Step 1: Generate the customer_id sequence between 1 and max using RECURSIVE CTE:
WITH RECURSIVE seq AS (
    -- initial_query
    SELECT 1 AS ids 
    UNION ALL 
    -- recursive_query
    SELECT ids + 1 
    FROM seq 
    WHERE ids < (SELECT max(customer_id) FROM Customers)
    )

-- Step 2: Find all the ids in sequence but not in Customers table
SELECT ids
FROM seq 
WHERE ids NOT IN (SELECT customer_id FROM Customers)
;