Find the Missing IDs - Practice Coding Problems

Find the Missing IDs - Problem

Database Medium

🔍 Find the Missing Customer IDs

You work as a data analyst for an e-commerce company, and you've discovered that some customer IDs are missing from your database! This could indicate data corruption or system errors that need immediate attention.

Given a Customers table with customer IDs and names, your task is to identify all the missing customer IDs in the range from 1 to the maximum customer_id present in the table.

📊 Table Structure:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| customer_name | varchar |
+---------------+---------+

Key Points:

🔑 customer_id is unique for each customer
📈 Maximum customer_id will not exceed 100
🎯 Find IDs missing in range [1, max_customer_id]
📋 Return results ordered by missing IDs in ascending order

Think of it like finding missing pages in a numbered book - if you have pages 1, 3, 5, 7, you need to identify that pages 2, 4, and 6 are missing!

Input & Output

example_1.sql — Basic Missing IDs

$ Input: Customers table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | Alice | | 3 | Bob | | 7 | Charlie | | 9 | David | | 10 | Eve | +-------------+---------------+

› Output: +-----+ | ids | +-----+ | 2 | | 4 | | 5 | | 6 | | 8 | +-----+

💡 Note: The maximum customer_id is 10, so we need to check range [1,10]. Customer IDs 1,3,7,9,10 exist, so missing IDs are 2,4,5,6,8.

example_2.sql — Consecutive IDs

$ Input: Customers table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | John | | 2 | Jane | | 3 | Jack | +-------------+---------------+

› Output: Empty result set (no missing IDs)

💡 Note: All IDs from 1 to 3 are present consecutively, so there are no missing customer IDs in the range.

example_3.sql — Single Customer

$ Input: Customers table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 5 | Solo | +-------------+---------------+

› Output: +-----+ | ids | +-----+ | 1 | | 2 | | 3 | | 4 | +-----+

💡 Note: Only customer_id 5 exists, so IDs 1,2,3,4 are missing in the range [1,5].

Time & Space Complexity

Time Complexity

⏱️

O(n)

Database optimizes set operations with hash-based algorithms

✓ Linear Growth

Space Complexity

O(n)

Temporary space for generated range and hash tables

⚡ Linearithmic Space

Constraints

1 ≤ customer_id ≤ 100
customer_id values are unique
customer_name is a non-empty varchar
The table contains at least 1 customer
Results must be ordered by ids in ascending order

Asked in

The optimal approach uses SQL set operations like EXCEPT or NOT EXISTS to efficiently find missing customer IDs. Generate a complete range from 1 to MAX(customer_id) and subtract the existing IDs using database-optimized set operations for O(n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ SQL Set Operations (Optimal)	O(n)	O(n)	Use SQL's EXCEPT/MINUS operator to subtract existing IDs from generated range
Hash Set + Range Generation	O(n)	O(n)	Create hash set of existing IDs, then iterate through range to find missing ones
Recursive CTE Approach	O(n)	O(n)	Generate all numbers from 1 to max ID using recursive CTE, then find missing ones

SQL Set Operations (Optimal) — Algorithm Steps

Generate a complete series from 1 to MAX(customer_id)
Use EXCEPT operator to subtract existing customer IDs
Database engine optimizes the set difference operation
Results are automatically ordered
Return the missing IDs

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate Full Range

Create complete set {1,2,3,4,5,6,7,8,9,10}

Get Existing Set

Extract customer IDs {1,3,7,9,10}

Set Difference

Subtract existing from full range

Missing IDs

Result: {2,4,5,6,8}

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

// Optimal SQL solution
const char* optimalSQL = 
"WITH number_range AS (\n"
"    SELECT level as ids\n"
"    FROM (\n"
"        SELECT 1 as level UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5\n"
"        UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10\n"
"        UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15\n"
"        UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20\n"
"        -- Continue up to 100 as per constraint\n"
"    ) t\n"
"    WHERE level <= (SELECT MAX(customer_id) FROM Customers)\n"
")\n"
"SELECT ids FROM number_range nr\n"
"WHERE NOT EXISTS (\n"
"    SELECT 1 FROM Customers c WHERE c.customer_id = nr.ids\n"
")\n"
"ORDER BY ids;";

// Comparison function for qsort
int compare(const void *a, const void *b) {
    return (*(int*)a - *(int*)b);
}

// C simulation of the optimal approach
int* findMissingOptimal(int* customers, int customersSize, int* returnSize) {
    if (customersSize == 0) {
        *returnSize = 0;
        return NULL;
    }
    
    // Find max ID
    int maxId = customers[0];
    for (int i = 1; i < customersSize; i++) {
        if (customers[i] > maxId) {
            maxId = customers[i];
        }
    }
    
    // Create existence array
    int* exists = (int*)calloc(maxId + 1, sizeof(int));
    for (int i = 0; i < customersSize; i++) {
        exists[customers[i]] = 1;
    }
    
    // Find missing IDs (set difference operation)
    int* missing = (int*)malloc(maxId * sizeof(int));
    int count = 0;
    
    for (int i = 1; i <= maxId; i++) {
        if (!exists[i]) {
            missing[count++] = i;
        }
    }
    
    *returnSize = count;
    free(exists);
    return missing;
}

int main() {
    int customers[] = {1, 3, 7, 9, 10};
    int returnSize;
    int* result = findMissingOptimal(customers, 5, &returnSize);
    
    printf("Missing customer IDs: ");
    for (int i = 0; i < returnSize; i++) {
        printf("%d ", result[i]);
    }
    printf("\n"); // Output: 2 4 5 6 8
    
    free(result);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Database optimizes set operations with hash-based algorithms

✓ Linear Growth

Space Complexity

O(n)

Temporary space for generated range and hash tables

⚡ Linearithmic Space

Constraints

1 ≤ customer_id ≤ 100
customer_id values are unique
customer_name is a non-empty varchar
The table contains at least 1 customer
Results must be ordered by ids in ascending order

25.0K Views

Medium Frequency

~15 min Avg. Time

850 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🔍 Find the Missing Customer IDs

📊 Table Structure:

Input & Output

Time & Space Complexity

Related Problems

Constraints

Common Approaches

SQL Set Operations (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler