Spread sales periods across years

SQL data engineering interview problem. Difficulty: advanced. Pattern: CTEs. About 20 minutes. Part of the Pro drill bank.

annual_product_sales tracks multi-year contracts with columns product_id, product_name, period_start, period_end, and average_daily_spend. Report total revenue earned per product for each calendar year. When a period spans across year boundaries, split the period so that each year only accounts for days falling within that calendar year (both start and end dates inclusive). Return product_id, product_name, report_year, and total_amount (rounded to 2 decimal places), ordered by product_id and report_year.

Requirements

• Output columns: product_id, product_name, report_year, total_amount.
• Order by product_id ascending, report_year ascending.

Constraints

• period_start <= period_end.
• average_daily_spend is a positive number.
• Dates are inclusive.

Examples

Input: annual_product_sales product_id,product_name,period_start,period_end,average_daily_spend 1,Keyboard,2023-12-01,2024-01-31,10 2,Monitor,2023-01-15,2023-05-25,15 3,Desk Lamp,2022-12-25,2024-01-05,20 Output: product_id | product_name | report_year | total_amount 1 | Keyboard | 2023 | 310 1 | Keyboard | 2024 | 310 2 | Monitor | 2023 | 1965 3 | Desk Lamp | 2022 | 140 3 | Desk Lamp | 2023 | 7300 3 | Desk Lamp | 2024 | 100 Why this passes: Each period is split at the year boundary and every year's slice is days x average_daily_spend, e.g. Keyboard has 31 days in 2023 and 31 in 2024 at 10 per day = 310 each.

Input: annual_product_sales product_id | product_name | period_start | period_end | average_daily_spend 1 | Chair | 2023-01-01 | 2023-01-10 | 10 Output: product_id | product_name | report_year | total_amount 1 | Chair | 2023 | 100 Why this passes: 10 days within 2023 at 10/day yields 100 for 2023.

Topics: lakebench, sql, recursive-cte, date-ranges.

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