Monthly Paid Revenue and Its Change From Last Month

Medium
408.

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.

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

ColumnTypeValues
invoice_idint
account_idint
invoice_datedate
amountint
statusenumpaid, void

Primary key: invoice_id.

One row per invoice raised, amount in rupees.

Example

The rows must come back in the order shown.

invoices

invoice_idaccount_idinvoice_dateamountstatus
112024-01-054999paid
222024-01-2024999paid
312024-02-054999paid
422024-02-2024999void
532024-02-2539999paid
642024-03-104999void
712024-04-054999paid
832024-04-2539999paid
922024-05-2024999paid
1012024-05-054999paid

Output

revenue_monthrevenuechange_from_prev
2024-0129998NULL
2024-024499815000
2024-0444998NULL
2024-0529998-15000
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.

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

ColumnType
invoice_idint
account_idint
invoice_datedate
amountint
statusenum(paid, void)

Primary key: invoice_id.

One row per invoice raised, amount in rupees.

Examples

Example 1

invoices

invoice_idaccount_idinvoice_dateamountstatus
112024-01-054999paid
222024-01-2024999paid
312024-02-054999paid
422024-02-2024999void
532024-02-2539999paid
642024-03-104999void
712024-04-054999paid
832024-04-2539999paid
922024-05-2024999paid
1012024-05-054999paid

Output

revenue_monthrevenuechange_from_prev
2024-0129998NULL
2024-024499815000
2024-0444998NULL
2024-0529998-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_month

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

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