Find gaps in order ids

Find gaps in order ids

Medium SQL DQL 70 views
Explanation Complexity

Problem Statement

Assume order_id should be continuous. Using a helper Numbers(n) table, list the missing order_id between MIN(order_id) and MAX(order_id).

Input Format

SQL query

Output Format

Result set

Example

Orders(order_id), Numbers(n) 1..N
Missing ids

Constraints

Think: generate range then remove existing ids

Input / Output Format

Input Format
SQL query
Output Format
Result set
Constraints
Think: generate range then remove existing ids

Examples

Input:
Orders(order_id), Numbers(n) 1..N
Output:
Missing ids

Example Solution (Public)

SQL 
SELECT n.n AS missing_order_id FROM Numbers n WHERE n.n BETWEEN (SELECT MIN(order_id) FROM Orders) AND (SELECT MAX(order_id) FROM Orders) AND NOT EXISTS (SELECT 1 FROM Orders o WHERE o.order_id = n.n);

Official Solution Code

SELECT n.n AS missing_order_id FROM Numbers n WHERE n.n BETWEEN (SELECT MIN(order_id) FROM Orders) AND (SELECT MAX(order_id) FROM Orders) AND NOT EXISTS (SELECT 1 FROM Orders o WHERE o.order_id = n.n);
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.