Monthly Paid Revenue and Its Change From Last Month
The board deck shows revenue by month with the change against the month before. Only invoices with status paid count; void invoices were cancelled.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #408
Problem statement
The board deck shows revenue by month with the change against the month before. Only invoices with status paid count; void invoices were cancelled.
Return one row per calendar month that has paid revenue, with revenue_month ('YYYY-MM'), revenue (sum of paid amounts) and change_from_prev = this month's revenue minus the previous calendar month's. When the previous calendar month has no paid revenue (or there is none before it), change_from_prev is NULL — never compare across a gap. Order by revenue_month.
Tables
Table: invoices
| Column | Type |
|---|---|
| invoice_id | int |
| account_id | int |
| invoice_date | date |
| amount | int |
| status | enum(paid, void) |
Primary key: invoice_id.
One row per invoice raised, amount in rupees.
Examples
Example 1
invoices
| invoice_id | account_id | invoice_date | amount | status |
|---|---|---|---|---|
| 1 | 1 | 2024-01-05 | 4999 | paid |
| 2 | 2 | 2024-01-20 | 24999 | paid |
| 3 | 1 | 2024-02-05 | 4999 | paid |
| 4 | 2 | 2024-02-20 | 24999 | void |
| 5 | 3 | 2024-02-25 | 39999 | paid |
| 6 | 4 | 2024-03-10 | 4999 | void |
| 7 | 1 | 2024-04-05 | 4999 | paid |
| 8 | 3 | 2024-04-25 | 39999 | paid |
| 9 | 2 | 2024-05-20 | 24999 | paid |
| 10 | 1 | 2024-05-05 | 4999 | paid |
Output
| revenue_month | revenue | change_from_prev |
|---|---|---|
| 2024-01 | 29998 | NULL |
| 2024-02 | 44998 | 15000 |
| 2024-04 | 44998 | NULL |
| 2024-05 | 29998 | -15000 |
How to solve Monthly Paid Revenue and Its Change From Last Month
Two steps. First aggregate: filter to paid, group by month and sum. Then compare each month with the one before it.
LAG(revenue) OVER (ORDER BY month) reads the previous row, which is the previous calendar month only when no month is missing. In the example March has only a void invoice, so it has no row, and a bare LAG would compare April with February. The fix is to carry a month number — YEAR * 12 + MONTH, which also makes December → January a step of one — and accept the lagged value only when the lagged key is exactly one less. Otherwise the CASE has no ELSE and yields NULL.
A self-join of the monthly table on p.month_key = m.month_key - 1 states the rule directly: LEFT JOIN leaves NULL when the previous month is absent, and revenue - NULL is NULL. A correlated subquery that recomputes the previous month's total also works but rescans invoices for every month. The window plan sorts the few monthly rows once.
Reference solution (MySQL)
WITH monthly AS (
SELECT YEAR(invoice_date) * 12 + MONTH(invoice_date) AS month_key,
DATE_FORMAT(invoice_date, '%Y-%m') AS revenue_month,
SUM(amount) AS revenue
FROM invoices
WHERE status = 'paid'
GROUP BY YEAR(invoice_date) * 12 + MONTH(invoice_date), DATE_FORMAT(invoice_date, '%Y-%m')
)
SELECT revenue_month, revenue,
CASE WHEN LAG(month_key) OVER (ORDER BY month_key) = month_key - 1
THEN revenue - LAG(revenue) OVER (ORDER BY month_key) END AS change_from_prev
FROM monthly
ORDER BY revenue_monthAnother way
WITH monthly AS (
SELECT YEAR(invoice_date) * 12 + MONTH(invoice_date) AS month_key, DATE_FORMAT(invoice_date, '%Y-%m') AS revenue_month, SUM(amount) AS revenue
FROM invoices WHERE status = 'paid'
GROUP BY YEAR(invoice_date) * 12 + MONTH(invoice_date), DATE_FORMAT(invoice_date, '%Y-%m')
)
SELECT m.revenue_month, m.revenue, m.revenue - p.revenue AS change_from_prev
FROM monthly m LEFT JOIN monthly p ON p.month_key = m.month_key - 1
ORDER BY m.revenue_monthAnother way
SELECT revenue_month, revenue,
revenue - (SELECT SUM(i.amount) FROM invoices i WHERE i.status = 'paid' AND DATE_FORMAT(i.invoice_date, '%Y-%m') = DATE_FORMAT(DATE_SUB(CONCAT(revenue_month, '-01'), INTERVAL 1 MONTH), '%Y-%m')) AS change_from_prev
FROM (SELECT DATE_FORMAT(invoice_date, '%Y-%m') AS revenue_month, SUM(amount) AS revenue FROM invoices WHERE status = 'paid' GROUP BY DATE_FORMAT(invoice_date, '%Y-%m')) m
ORDER BY revenue_month← Pricing Page A/B Test Conversion by Variant · Paid Accounts Dormant for Over 30 Days →