Gaps in order_id sequence

SQL data engineering interview problem. Difficulty: intermediate. Pattern: CTEs. About 12 minutes. Part of the Pro drill bank.

Order ids should go up by 1 each time: 1, 2, 3, 4... If an id is missing, there is a gap, which can mean a lost or deleted order. You want to find where those gaps are. Using lb_orders.order_id, find the places where the next id is more than 1 bigger than the current one. For each gap, return prev_order_id (the id before the gap) and next_order_id (the id after the gap). Sort by prev_order_id.

Requirements

• Look at the order ids in order from the smallest.
• For each id, find the next id.
• Keep only the pairs where next_order_id - prev_order_id > 1.
• Return prev_order_id and next_order_id, sorted by prev_order_id.

Constraints

• The table is lb_orders. order_id values are integers.
• Use LEAD(order_id) OVER (ORDER BY order_id) to see the next id on the same row.
• Return only these 2 columns, in this order: prev_order_id, next_order_id.

Examples

Input: lb_orders (order_id) order_id | 1 | 2 | 3 | 5 | 6 | 7 | Output: prev_order_id | next_order_id 3 | 5 Why this passes: Whenever LEAD jumps by more than 1, emit the pair that brackets the missing ids.

Topics: lakebench, sql, gaps, lead.

More SQL interview questions · All interview problems · Learn data engineering