Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Yearly resetting running total

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

For each lb_orders row, compute yearly_running_total: a running sum of amount partitioned by customer_id and YEAR(order_date), ordered by order_date, order_id. Return customer_id, order_date, amount, yearly_running_total. Order by customer_id, order_date, order_id.

Constraints

  • Reset is by calendar year, 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

advanced

Yearly resetting running total

Interview-style drill: Running sum that resets each calendar year per customer.

For each `lb_orders` row, compute `yearly_running_total`: a running sum of `amount` partitioned by `customer_id` and `YEAR(order_date)`, ordered by `order_date`, `order_id`. Return `customer_id`, `order_date`, `amount`, `yearly_running_total`. Order by `customer_id`, `order_date`, `order_id`.