Monthly Recharge Revenue by Pack Type

Medium
510.

Monthly Recharge Revenue by Pack Type

Finance wants a month-by-month revenue sheet with one column per kind of prepaid pack. Only recharges with status = 'success' earn revenue; failed and refunded ones count for nothing.

Return one row per month that has at least one successful recharge, with the columns month (as 'YYYY-MM'), data_revenue, combo_revenue, talktime_revenue and total_revenue — sums of amount in rupees, 0 (not NULL) for a pack type with no successful recharge that month. Order by month.

Tables

Table: Recharge

ColumnTypeValues
recharge_idint
mobile_numbervarchar
pack_typeenumdata, combo, talktime
amountint
statusenumsuccess, failed, refunded
recharge_datedate

Primary key: recharge_id.

One recharge attempt; amount is the pack's price in rupees.

Example

The rows must come back in the order shown.

Recharge

recharge_idmobile_numberpack_typeamountstatusrecharge_date
19845012345combo299success2024-01-05
29900123456data98success2024-01-17
39731122334combo579failed2024-01-20
49845012345talktime50success2024-01-31
58123456789data19failed2024-02-03
69900123456combo299success2024-03-01
77019988776combo719refunded2024-03-09
88123456789data98success2024-03-15

Output

monthdata_revenuecombo_revenuetalktime_revenuetotal_revenue
2024-019829950447
2024-03982990397
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 Recharge Revenue by Pack Type — SQL Problem

Finance wants a month-by-month revenue sheet with one column per kind of prepaid pack.

  • Difficulty: Medium
  • Topics: Conditional Logic, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #510

Problem statement

Finance wants a month-by-month revenue sheet with one column per kind of prepaid pack. Only recharges with status = 'success' earn revenue; failed and refunded ones count for nothing.

Return one row per month that has at least one successful recharge, with the columns month (as 'YYYY-MM'), data_revenue, combo_revenue, talktime_revenue and total_revenue — sums of amount in rupees, 0 (not NULL) for a pack type with no successful recharge that month. Order by month.

Tables

Table: Recharge

ColumnType
recharge_idint
mobile_numbervarchar
pack_typeenum(data, combo, talktime)
amountint
statusenum(success, failed, refunded)
recharge_datedate

Primary key: recharge_id.

One recharge attempt; amount is the pack's price in rupees.

Examples

Example 1

Recharge

recharge_idmobile_numberpack_typeamountstatusrecharge_date
19845012345combo299success2024-01-05
29900123456data98success2024-01-17
39731122334combo579failed2024-01-20
49845012345talktime50success2024-01-31
58123456789data19failed2024-02-03
69900123456combo299success2024-03-01
77019988776combo719refunded2024-03-09
88123456789data98success2024-03-15

Output

monthdata_revenuecombo_revenuetalktime_revenuetotal_revenue
2024-019829950447
2024-03982990397

How to solve Monthly Recharge Revenue by Pack Type

This is a pivot written with conditional aggregation. After keeping only successful recharges, the rows are grouped by month — DATE_FORMAT(recharge_date, '%Y-%m') turns a date into the '2024-01' key, so every day of a month lands in one group.

Each revenue column is a SUM over a CASE that passes the amount through for its pack type and 0 for the others. The ELSE 0 matters: without it the CASE yields NULL for other rows, and a month with no talktime sale would sum to NULL rather than 0. The total is just SUM(amount) of the successful rows.

Because the filter runs before grouping, a month whose recharges all failed has no group and is absent. The alternative moves the status test into each IF and drops all-failed months with HAVING instead — equivalent, and the form you need if failed attempts should also be counted in another column. One pass over the table plus a sort of the months.

Reference solution (MySQL)

SELECT DATE_FORMAT(recharge_date, '%Y-%m') AS month,
       SUM(CASE WHEN pack_type = 'data' THEN amount ELSE 0 END) AS data_revenue,
       SUM(CASE WHEN pack_type = 'combo' THEN amount ELSE 0 END) AS combo_revenue,
       SUM(CASE WHEN pack_type = 'talktime' THEN amount ELSE 0 END) AS talktime_revenue,
       SUM(amount) AS total_revenue
FROM Recharge
WHERE status = 'success'
GROUP BY DATE_FORMAT(recharge_date, '%Y-%m')
ORDER BY month

Another way

SELECT LEFT(recharge_date, 7) AS month,
       SUM(IF(status = 'success' AND pack_type = 'data', amount, 0)) AS data_revenue,
       SUM(IF(status = 'success' AND pack_type = 'combo', amount, 0)) AS combo_revenue,
       SUM(IF(status = 'success' AND pack_type = 'talktime', amount, 0)) AS talktime_revenue,
       SUM(IF(status = 'success', amount, 0)) AS total_revenue
FROM Recharge
GROUP BY LEFT(recharge_date, 7)
HAVING SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) > 0
ORDER BY month

← Data Users Above Their Circle's Average · Consumers With Repeated Late Payment Fees →