Running total by customer

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

Finance wants to see how much each customer has spent so far, order by order. This is called a running total: each row shows the sum of everything up to and including that row. For each row of lb_orders, compute a running total of amount for the same customer, in order of order_date and then order_id. Return customer_id, order_date, amount and running_total. Sort by customer_id, order_date and order_id.

Requirements

• Keep one output row for each order. Do not collapse rows.
• running_total is the sum of amount from the customer's first order up to the current one.
• The total restarts for each customer.
• If 2 orders have the same date, order_id decides which comes first.

Constraints

• The table is lb_orders.
• Use SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id).
• Return only these 4 columns, in this order: customer_id, order_date, amount, running_total.

Examples

Input: lb_orders customer_id | order_date | amount | order_id C01 | 2024-01-05 | 1200 | 1 C01 | 2024-02-08 | 35 | 2 C01 | 2024-03-12 | 40 | 3 Output: customer_id | order_date | amount | running_total C01 | 2024-01-05 | 1200 | 1200 C01 | 2024-02-08 | 35 | 1235 C01 | 2024-03-12 | 40 | 1275 Why this passes: The window sum accumulates in date order within the customer partition.

Topics: lakebench, sql, running total, sum window.

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