SQL data engineering interview problem. Difficulty: advanced. Pattern: Explain Plans. About 18 minutes. Part of the Pro drill bank.
A slow query looks like: Wrapping order_date in YEAR() prevents a plain range scan on an order_date index. Rewrite it to an equivalent sargable filter: order_date >= DATE '2024-01-01' AND order_date < DATE '2025-01-01' Return columns: order_id, customer_id, product, amount, order_date, name. Order by order_id.
Input: lb_orders order_id | customer_id | product | amount | order_date 1 | C01 | Laptop | 1200 | 2024-01-05 5 | C02 | Laptop | 500 | 2023-01-10 6 | C03 | Widget | 50 | 2024-01-15 customers customer_id | name C01 | Ana C02 | Ben C03 | Cam Output: order_id | customer_id | product | amount | order_date | name 1 | C01 | Laptop | 1200 | 2024-01-05 | Ana 6 | C03 | Widget | 50 | 2024-01-15 | Cam Why this passes: The sargable half-open range keeps 2024 rows and drops the 2023 order while joining customer names.
Topics: lakebench, sql, optimization, sargable.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Rewrite a non-sargable year filter into a range predicate and join customers.
A slow query looks like: ```sql SELECT o.order_id, o.customer_id, o.product, o.amount, o.order_date, c.name FROM lb_orders o JOIN customers c ON o.customer_id = c.customer_id WHERE YEAR(o.order_date) = 2024; ``` Wrapping `order_date` in YEAR() prevents a plain range scan on an order_date index. Rewrite it to an equivalent sargable filter: `order_date >= DATE '2024-01-01' AND order_date < DATE '2025-01-01'` Return columns: `order_id`, `customer_id`, `product`, `amount`, `order_date`, `name`. Order by `order_id`.