Monthly Revenue Running Total
You manage the financial reporting for a digital subscription service. Leadership wants to see how monthly revenue accumulates over time within each calendar year. Write a query that computes the total revenue for each month, and then calculates a running total of revenue ordered by month for each year.
Tables
subscriptions: contains customer subscription payments.subscription_id(integer, primary key): unique identifier for the payment event.payment_date(date): the date the payment was received.amount(numeric): the revenue amount collected.
Task
For each calendar year and month present in the data, compute the total revenue for that month, and then calculate the cumulative running total of revenue from the start of that year up to the current month. Format the month as a two-digit string or integer.
For example, if January has 100 and February has 150 in the year 2024, the output should show January with a monthly revenue of 100 and a running total of 100, and February with a monthly revenue of 150 and a running total of 250.
| year | month | monthly_revenue | running_total |
|---|---|---|---|
| 2024 | 1 | 200 | 200 |
| 2024 | 2 | 200 | 400 |
| 2024 | 3 | 300 | 700 |
Submitting also runs your answer against 2 hidden datasets, each built around an edge case: NULLs, ties, empty tables. A failure names the case without showing its data.
Follow-up: How would you modify your query to compute a 3-month moving average instead of a running total?
Return `year`, `month`, `monthly_revenue`, and `running_total`. Order the result by `year` ascending, then `month` ascending. Round monetary values to two decimal places if necessary.