Month-over-Month Ad Spend Change by Channel — SQL Problem
Finance receives several media invoices per channel each month.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #289
Problem statement
Finance receives several media invoices per channel each month. For the monthly review, each channel's spend is compared with the immediately preceding calendar month.
For every channel and month with invoices, return channel, month ('YYYY-MM' of invoice_date), spend (the month's total), prev_month_spend (the channel's total for the calendar month just before, or NULL if that month had no invoices for the channel) and change (spend − prev_month_spend, NULL when prev_month_spend is NULL). Order by channel, then month.
Tables
Table: MediaInvoice
| Column | Type |
|---|---|
| invoice_id | int |
| channel | enum(search, social, display, video, email) |
| invoice_date | date |
| amount | int |
Primary key: invoice_id.
One row per invoice from a media vendor, amount in rupees (before GST).
Examples
Example 1
MediaInvoice
| invoice_id | channel | invoice_date | amount |
|---|---|---|---|
| 1 | search | 2024-01-10 | 80000 |
| 2 | search | 2024-01-25 | 20000 |
| 3 | search | 2024-02-15 | 130000 |
| 4 | search | 2024-04-05 | 90000 |
| 5 | social | 2024-02-01 | 50000 |
| 6 | social | 2024-03-31 | 45000 |
| 7 | social | 2024-03-02 | 15000 |
Output
| channel | month | spend | prev_month_spend | change |
|---|---|---|---|---|
| search | 2024-01 | 100000 | NULL | NULL |
| search | 2024-02 | 130000 | 100000 | 30000 |
| search | 2024-04 | 90000 | NULL | NULL |
| social | 2024-02 | 50000 | NULL | NULL |
| social | 2024-03 | 60000 | 50000 | 10000 |
How to solve Month-over-Month Ad Spend Change by Channel
Start with one row per (channel, month): DATE_FORMAT(invoice_date, '%Y-%m') buckets the invoices and doubles as the output label, and since the label sorts like the date it can order a window.
LAG(spend) OVER (PARTITION BY channel ORDER BY month) fetches the previous row's total. That is right only when the previous row is the previous calendar month; when a channel skipped a month (search in March above) the lagged row is two months back, and the statement wants NULL. So lag the month label too, compute the true previous month with DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH) — which also rolls January back to December of the year before — and keep the lagged values only when they match.
The self-join alternative avoids the check entirely: LEFT JOIN each month to the row whose month is exactly the previous one; a missing month gives NULLs. change is a reserved word in MySQL, hence the backticks. Both plans aggregate once; the join adds a lookup per month.
Reference solution (MySQL)
WITH monthly AS (
SELECT channel, DATE_FORMAT(invoice_date, '%Y-%m') AS month, SUM(amount) AS spend
FROM MediaInvoice
GROUP BY channel, DATE_FORMAT(invoice_date, '%Y-%m')
), lagged AS (
SELECT channel, month, spend,
LAG(month) OVER (PARTITION BY channel ORDER BY month) AS prev_month,
LAG(spend) OVER (PARTITION BY channel ORDER BY month) AS prev_spend
FROM monthly
)
SELECT channel, month, spend,
CASE WHEN prev_month = DATE_FORMAT(DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH), '%Y-%m') THEN prev_spend END AS prev_month_spend,
CASE WHEN prev_month = DATE_FORMAT(DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH), '%Y-%m') THEN spend - prev_spend END AS `change`
FROM lagged
ORDER BY channel, monthAnother way
WITH monthly AS (
SELECT channel, DATE_FORMAT(invoice_date, '%Y-%m') AS month, SUM(amount) AS spend
FROM MediaInvoice GROUP BY channel, DATE_FORMAT(invoice_date, '%Y-%m')
)
SELECT m.channel, m.month, m.spend, p.spend AS prev_month_spend, m.spend - p.spend AS `change`
FROM monthly m
LEFT JOIN monthly p ON p.channel = m.channel
AND p.month = DATE_FORMAT(DATE_SUB(CONCAT(m.month, '-01'), INTERVAL 1 MONTH), '%Y-%m')
ORDER BY m.channel, m.month← Weekly Leads by UTM Medium · Email Subscribers Who Open but Never Click →