Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Gaps in order_id sequence

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

Using lb_orders.order_id, find places where the next id is more than 1 greater than the current id. Return prev_order_id and next_order_id for each gap. Order by prev_order_id.

Constraints

  • order_id values are integers in this table.

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

intermediate

Gaps in order_id sequence

Interview-style drill: Find gaps where order_id jumps by more than one.

Using `lb_orders.order_id`, find places where the next id is more than 1 greater than the current id. Return `prev_order_id` and `next_order_id` for each gap. Order by `prev_order_id`.