Yearly resetting running total

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

A sales team tracks how much each customer has spent in the current year, and the running total starts again at the beginning of every new year. This is a running total that resets. For each row of lb_orders, calculate yearly_running_total, a running sum of amount for the same customer and the same calendar year, ordered by order_date and then order_id. Return customer_id, order_date, amount and yearly_running_total. Sort by customer_id, order_date and order_id.

Requirements

• Keep one output row for each order.
• The running total is per customer and per calendar year.
• The total starts again at 0 when a new year begins for the same customer.
• Order the running total by order_date, then order_id.

Constraints

• The table is lb_orders.
• Use PARTITION BY customer_id, YEAR(order_date) in the window.
• The reset is by calendar year (January to December), not by fiscal year.

Examples

Input: lb_orders customer_id | order_date | amount C02 | 2023-01-10 | 500 C01 | 2024-01-05 | 1200 C01 | 2024-02-08 | 35 Output: customer_id | order_date | amount | yearly_running_total C01 | 2024-01-05 | 1200 | 1200 C01 | 2024-02-08 | 35 | 1235 C02 | 2023-01-10 | 500 | 500 Why this passes: Partitioning by year resets the running sum on January 1, so 2023 does not carry into 2024.

Topics: lakebench, sql, running total, partition.

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