Products per customer as one string, plus monthly revenue

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

A sales report needs, for each customer, a single readable line of the products they bought and their revenue for each of the first 3 months of 2024. From lb_orders, return 1 row for each customer with customer_id, products (the distinct product names the customer ever ordered, joined into one text with ', ', sorted by name) and jan, feb, mar (the customer's total amount in January, February and March of 2024, 1 column for each month, NULL when the customer had no orders that month). Sort by customer_id. Orders outside January to March 2024 still count for products, but not for the month columns.

Requirements

• Return 1 row for each customer.
• products lists each product once, sorted by name and separated by ', '.
• jan, feb and mar are the totals for those months of 2024, and NULL when there is none.
• Use all orders for products, but only Q1 2024 orders for the month columns.
• Sort by customer_id.

Constraints

• The table is lb_orders. order_date is a DATE.
• Use STRING_AGG(DISTINCT product, ', ' ORDER BY product) for the list.
• Months are NULL, not 0, when the customer has no revenue in that month.

Examples

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