Monthly Subscription Revenue With a Running Total

Medium
490.

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.

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

ColumnTypeValues
payment_idint
account_idint
paid_ondate
amount_inrint
statusenumsuccess, failed, refunded

Primary key: payment_id.

One row per renewal charge attempt, via UPI autopay or card.

Example

The rows must come back in the order shown.

Payment

payment_idaccount_idpaid_onamount_inrstatus
190012024-01-05199success
290022024-01-18649success
390032024-01-30499failed
490012024-02-05199success
590022024-02-18649refunded
690032024-04-01499success
790042024-04-29149success

Output

monthrevenuerunning_revenue
2024-01848848
2024-021991047
2024-046481695
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 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

ColumnType
payment_idint
account_idint
paid_ondate
amount_inrint
statusenum(success, failed, refunded)

Primary key: payment_id.

One row per renewal charge attempt, via UPI autopay or card.

Examples

Example 1

Payment

payment_idaccount_idpaid_onamount_inrstatus
190012024-01-05199success
290022024-01-18649success
390032024-01-30499failed
490012024-02-05199success
590022024-02-18649refunded
690032024-04-01499success
790042024-04-29149success

Output

monthrevenuerunning_revenue
2024-01848848
2024-021991047
2024-046481695

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 month

Another 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.month

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