Cumulative orders per product

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

A product manager wants to see how many times each product has been ordered so far, as time goes by. This is a cumulative count: each row shows how many orders of that product there have been up to and including this one. For each row of lb_orders, return product, order_date and cumulative_order_count, which is the running number of orders for that product, ordered by order_date and then order_id. Sort by product, order_date and order_id.

Requirements

• Return 1 row for each order. Do not collapse rows.
• cumulative_order_count counts the orders of the same product up to the current row.
• The count restarts for each product.
• Orders on the same date are ordered by order_id.

Constraints

• The table is lb_orders.
• Use COUNT(*) OVER (PARTITION BY product ORDER BY order_date, order_id).
• Return only these 3 columns, in this order: product, order_date, cumulative_order_count.

Examples

Input: lb_orders (Widget) product | order_id | order_date Widget | 6 | 2024-01-15 Widget | 7 | 2024-01-20 Output: product | order_date | cumulative_order_count Widget | 2024-01-15 | 1 Widget | 2024-01-20 | 2 Why this passes: Running count grows with each Widget row in date order.

Topics: lakebench, sql, cumulative, count.

More SQL interview questions · All interview problems · Learn data engineering