Sargable rewrite for order_date filter

SQL data engineering interview problem. Difficulty: advanced. Pattern: Explain Plans. About 18 minutes. Part of the Pro drill bank.

A query is slow because of how its filter is written. It looks for orders from 2024 with WHERE YEAR(o.order_date) = 2024. Wrapping the column in the function YEAR() forces the database to calculate the year for every row, and it cannot use an index on order_date. Rewrite the query so that it returns exactly the same rows, but with a filter that can use an index: order_date >= DATE '2024-01-01' AND order_date < DATE '2025-01-01'. Return the columns order_id, customer_id, product, amount, order_date and name (the customer's name from customers), sorted by order_id.

Requirements

  • Join lb_orders to customers on customer_id.
  • Keep only orders from the year 2024, using a date range on order_date.
  • Do not put order_date inside a function in the WHERE clause.
  • Return the 6 columns listed, sorted by order_id.

Constraints

  • The tables are lb_orders and customers.
  • The filter must be o.order_date >= DATE '2024-01-01' AND o.order_date < DATE '2025-01-01'.
  • Return only these 6 columns, in this order: order_id, customer_id, product, amount, order_date, name.

Examples

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

advanced

Sargable rewrite for order_date filter

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`.