Find the Missing IDs - Problem
Given a Customers table with customer IDs and names, find all missing customer IDs within the range from 1 to the maximum customer ID present in the table.
The missing IDs are those that don't exist in the Customers table but fall within the continuous range starting from 1.
Requirements:
- Find all missing customer IDs in the range [1, max_customer_id]
- Return results ordered by ID in ascending order
- Maximum customer_id will not exceed 100
Table Schema
Customers
| Column Name | Type | Description |
|---|---|---|
customer_id
PK
|
int | Unique customer identifier |
customer_name
|
varchar | Name of the customer |
Primary Key: customer_id
Note: Each row contains a unique customer with their ID and name
Input & Output
Example 1 — Basic Missing IDs
Input Table:
| customer_id | customer_name |
|---|---|
| 1 | Alice |
| 3 | Bob |
| 6 | Charlie |
| 7 | David |
Output:
| ids |
|---|
| 2 |
| 4 |
| 5 |
💡 Note:
The maximum customer_id is 7, so we check the range [1, 7]. IDs 1, 3, 6, and 7 exist in the table, so the missing IDs are 2, 4, and 5.
Example 2 — Consecutive IDs
Input Table:
| customer_id | customer_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
Output:
| ids |
|---|
💡 Note:
All IDs from 1 to 3 are present consecutively, so there are no missing IDs in the range.
Example 3 — Single Customer
Input Table:
| customer_id | customer_name |
|---|---|
| 5 | Alice |
Output:
| ids |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
💡 Note:
With only customer_id 5 present, IDs 1, 2, 3, and 4 are missing from the range [1, 5].
Constraints
-
1 ≤ customer_id ≤ 100 -
customer_nameis a non-empty varchar -
All
customer_idvalues are unique
Visualization
Tap to expand
Understanding the Visualization
1
Input
Customers table with gaps in IDs
2
Generate
Create complete sequence 1 to max_id
3
Compare
Find sequence numbers not in original table
Key Takeaway
🎯 Key Insight: Use recursive CTEs to generate sequences and set operations to find missing elements in ranges
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code