Month-over-Month Ad Spend Change by Channel

Medium
289.

Month-over-Month Ad Spend Change by Channel

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

ColumnTypeValues
invoice_idint
channelenumsearch, social, display, video, email
invoice_datedate
amountint

Primary key: invoice_id.

One row per invoice from a media vendor, amount in rupees (before GST).

Example

The rows must come back in the order shown.

MediaInvoice

invoice_idchannelinvoice_dateamount
1search2024-01-1080000
2search2024-01-2520000
3search2024-02-15130000
4search2024-04-0590000
5social2024-02-0150000
6social2024-03-3145000
7social2024-03-0215000

Output

channelmonthspendprev_month_spendchange
search2024-01100000NULLNULL
search2024-0213000010000030000
search2024-0490000NULLNULL
social2024-0250000NULLNULL
social2024-03600005000010000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
invoice_idint
channelenum(search, social, display, video, email)
invoice_datedate
amountint

Primary key: invoice_id.

One row per invoice from a media vendor, amount in rupees (before GST).

Examples

Example 1

MediaInvoice

invoice_idchannelinvoice_dateamount
1search2024-01-1080000
2search2024-01-2520000
3search2024-02-15130000
4search2024-04-0590000
5social2024-02-0150000
6social2024-03-3145000
7social2024-03-0215000

Output

channelmonthspendprev_month_spendchange
search2024-01100000NULLNULL
search2024-0213000010000030000
search2024-0490000NULLNULL
social2024-0250000NULLNULL
social2024-03600005000010000

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, month

Another 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 →