Monthly Subscription Revenue With a Running Total
Finance tracks the year's subscription revenue month by month. Only payments with status = success are revenue; failed and refunded payments are ignored.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #490
Problem statement
Finance tracks the year's subscription revenue month by month. Only payments with status = success are revenue; failed and refunded payments are ignored.
Return one row per calendar month that has any successful payment, with the columns month (as YYYY-MM), revenue (that month's successful payments in rupees) and running_revenue (the sum of revenue over this month and every earlier month in the table). Order the rows by month.
Tables
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| account_id | int |
| paid_on | date |
| amount_inr | int |
| status | enum(success, failed, refunded) |
Primary key: payment_id.
One row per renewal charge attempt, via UPI autopay or card.
Examples
Example 1
Payment
| payment_id | account_id | paid_on | amount_inr | status |
|---|---|---|---|---|
| 1 | 9001 | 2024-01-05 | 199 | success |
| 2 | 9002 | 2024-01-18 | 649 | success |
| 3 | 9003 | 2024-01-30 | 499 | failed |
| 4 | 9001 | 2024-02-05 | 199 | success |
| 5 | 9002 | 2024-02-18 | 649 | refunded |
| 6 | 9003 | 2024-04-01 | 499 | success |
| 7 | 9004 | 2024-04-29 | 149 | success |
Output
| month | revenue | running_revenue |
|---|---|---|
| 2024-01 | 848 | 848 |
| 2024-02 | 199 | 1047 |
| 2024-04 | 648 | 1695 |
How to solve Monthly Subscription Revenue With a Running Total
Two levels of aggregation. The inner query groups successful payments by month (DATE_FORMAT(paid_on, '%Y-%m')) to get each month's revenue; the status filter belongs in its WHERE, so refunds and failures never enter any sum.
The running total is a window over those monthly rows: SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) adds every month up to the current one. Month labels are unique after grouping, so the default RANGE frame would give the same answer, but naming the frame makes the intent explicit. A month with no successful payment simply has no row — March here — and the running total carries straight over it.
Before window functions this was written as a correlated subquery or a self join on b.month <= a.month, both quadratic in the number of months; with only twelve months a year that is harmless, but the window is one sort.
Reference solution (MySQL)
WITH monthly AS (
SELECT DATE_FORMAT(paid_on, '%Y-%m') AS month, SUM(amount_inr) AS revenue
FROM Payment
WHERE status = 'success'
GROUP BY DATE_FORMAT(paid_on, '%Y-%m')
)
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_revenue
FROM monthly
ORDER BY monthAnother way
WITH monthly AS (SELECT LEFT(paid_on, 7) AS month, SUM(amount_inr) AS revenue FROM Payment WHERE status = 'success' GROUP BY LEFT(paid_on, 7))
SELECT m.month, m.revenue, (SELECT SUM(x.revenue) FROM monthly x WHERE x.month <= m.month) AS running_revenue FROM monthly m ORDER BY m.monthAnother way
WITH monthly AS (SELECT LEFT(paid_on, 7) AS month, SUM(amount_inr) AS revenue FROM Payment WHERE status = 'success' GROUP BY LEFT(paid_on, 7))
SELECT a.month, a.revenue, SUM(b.revenue) AS running_revenue FROM monthly a JOIN monthly b ON b.month <= a.month GROUP BY a.month, a.revenue ORDER BY a.month← Sign-Up Channels by First-Week Activation · Binge-Watch Runs of Three or More Episodes →