SQL data engineering interview problem. Difficulty: intermediate. Pattern: Pivot. About 16 minutes. Part of the Pro drill bank.
From lb_orders, return one row per customer with: products: the distinct product names the customer ever ordered, joined into one string with ', ' and sorted by name jan, feb, mar: the customer's total amount for January, February and March 2024, one column per month, NULL when the customer had no orders that month Columns: customer_id, products, jan, feb, mar. Order by customer_id. Orders outside Q1 2024 still contribute to products.
Input: lb_orders order_id | customer_id | product | product_category | amount | order_date 1 | C01 | Laptop | Electronics | 1200 | 2024-01-05 2 | C01 | Shirt | Clothing | 35 | 2024-02-08 3 | C01 | Mouse | Electronics | 40 | 2024-03-12 5 | C02 | Laptop | Electronics | 500 | 2023-01-10 6 | C03 | Widget | Electronics | 50 | 2024-01-15 7 | C03 | Widget | Electronics | 55 | 2024-01-20 Output: customer_id | products | jan | feb | mar C01 | Laptop, Mouse, Shirt | 1200 | 35 | 40 C02 | Laptop | NULL | NULL | NULL C03 | Widget | 105 | NULL | NULL Why this passes: C01 ordered a Laptop, Shirt and Mouse in Jan to Mar 2024. C02's only order is from 2023, so its months are NULL. C03 ordered the same Widget twice in January: one product name, 105 revenue.
Input: lb_orders order_id | customer_id | product | product_category | amount | order_date 1 | C1 | Pen | Office | 10 | 2024-01-05 2 | C1 | Ink | Office | 5 | 2024-03-09 Output: customer_id | products | jan | feb | mar C1 | Ink, Pen | 10 | NULL | 5 Why this passes: One customer, two products, orders in January and March only: February is NULL.
Topics: lakebench, sql, string_agg, pivot, aggregation.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Combine a comma-joined product list with Jan/Feb/Mar 2024 revenue as columns, one row per customer.
From `lb_orders`, return one row per customer with: - `products`: the distinct product names the customer ever ordered, joined into one string with `', '` and sorted by name - `jan`, `feb`, `mar`: the customer's total `amount` for January, February and March **2024**, one column per month, NULL when the customer had no orders that month Columns: `customer_id`, `products`, `jan`, `feb`, `mar`. Order by `customer_id`. Orders outside Q1 2024 still contribute to `products`.