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
| Column | Type |
|---|---|
| recharge_id | int |
| mobile_number | varchar |
| pack_type | enum(data, combo, talktime) |
| amount | int |
| status | enum(success, failed, refunded) |
| recharge_date | date |
Primary key: recharge_id.
One recharge attempt; amount is the pack's price in rupees.
Examples
Example 1
Recharge
| recharge_id | mobile_number | pack_type | amount | status | recharge_date |
|---|---|---|---|---|---|
| 1 | 9845012345 | combo | 299 | success | 2024-01-05 |
| 2 | 9900123456 | data | 98 | success | 2024-01-17 |
| 3 | 9731122334 | combo | 579 | failed | 2024-01-20 |
| 4 | 9845012345 | talktime | 50 | success | 2024-01-31 |
| 5 | 8123456789 | data | 19 | failed | 2024-02-03 |
| 6 | 9900123456 | combo | 299 | success | 2024-03-01 |
| 7 | 7019988776 | combo | 719 | refunded | 2024-03-09 |
| 8 | 8123456789 | data | 98 | success | 2024-03-15 |
Output
| month | data_revenue | combo_revenue | talktime_revenue | total_revenue |
|---|---|---|---|---|
| 2024-01 | 98 | 299 | 50 | 447 |
| 2024-03 | 98 | 299 | 0 | 397 |
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 monthAnother 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 →