Paytm: Users Who Paid Electricity in Every Month of Q1 2025
The bill-payments growth team wants to reward habitual users: people who paid their electricity bill through the app in each of January, February and March…
- Difficulty: Medium
- Topics: Aggregation, Dates, Subqueries
- Dialect: MySQL
- Problem: #857
Problem statement
The bill-payments growth team wants to reward habitual users: people who paid their electricity bill through the app in each of January, February and March 2025.
Return every user with at least one successful electricity payment in each of those three months (by paid_at), with the columns user_id and total_paid — the sum of their successful electricity payments in January–March 2025. Other categories and failed payments never count. Return the rows ordered by user_id.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: BillPayment
| Column | Type |
|---|---|
| payment_id | int |
| user_id | int |
| category | enum(electricity, water, gas, broadband) |
| paid_at | datetime |
| amount | int |
| status | enum(success, failed) |
Primary key: payment_id.
One row per bill payment attempt.
Examples
Example 1
BillPayment
| payment_id | user_id | category | paid_at | amount | status |
|---|---|---|---|---|---|
| 1 | 1 | electricity | 2025-01-05 10:00:00 | 1450 | success |
| 2 | 1 | electricity | 2025-02-06 09:20:00 | 1320 | success |
| 3 | 1 | water | 2025-02-06 09:25:00 | 300 | success |
| 4 | 1 | electricity | 2025-03-31 23:59:00 | 1180 | success |
| 5 | 2 | electricity | 2025-01-10 12:00:00 | 980 | success |
| 6 | 2 | electricity | 2025-02-11 12:00:00 | 1010 | success |
| 7 | 2 | electricity | 2025-03-12 12:00:00 | 990 | failed |
| 8 | 2 | electricity | 2025-04-01 08:00:00 | 990 | success |
| 9 | 3 | electricity | 2025-01-02 08:00:00 | 600 | success |
| 10 | 3 | electricity | 2025-01-29 08:00:00 | 650 | success |
| 11 | 3 | electricity | 2025-02-27 08:00:00 | 700 | success |
| 12 | 3 | electricity | 2025-03-28 08:00:00 | 720 | success |
| 13 | 4 | electricity | 2024-12-31 21:00:00 | 800 | success |
| 14 | 4 | electricity | 2025-02-15 21:00:00 | 820 | success |
| 15 | 4 | electricity | 2025-03-15 21:00:00 | 810 | success |
| 16 | 5 | gas | 2025-01-20 07:00:00 | 500 | success |
| 17 | 5 | electricity | 2025-02-20 07:00:00 | 900 | success |
| 18 | 5 | electricity | 2025-03-20 07:00:00 | 905 | success |
Output
| user_id | total_paid |
|---|---|
| 1 | 3950 |
| 3 | 2670 |
How to solve Paytm: Users Who Paid Electricity in Every Month of Q1 2025
This is a small relational division: keep the users whose payments cover every month of a set. Filter first — successful, electricity, and inside [2025-01-01, 2025-04-01) — so that a December or April payment, a water bill or a failed attempt cannot help anyone. Then group by user: COUNT(DISTINCT MONTH(paid_at)) = 3 is true exactly when all three months are present, because only three months survive the filter. DISTINCT matters: user 3 paid twice in January and must not be counted as covering February that way.
The same filtered rows give total_paid with SUM(amount), so one grouping answers both parts.
The alternatives spell the division out: three EXISTS subqueries, one per month, or three conditional sums in HAVING. They are longer but generalise to a set that is not "all months in a range". The grouping version is a single scan; an index on (category, status, paid_at) narrows it further.
Reference solution (MySQL)
SELECT user_id, SUM(amount) AS total_paid
FROM BillPayment
WHERE category = 'electricity'
AND status = 'success'
AND paid_at >= '2025-01-01 00:00:00'
AND paid_at < '2025-04-01 00:00:00'
GROUP BY user_id
HAVING COUNT(DISTINCT MONTH(paid_at)) = 3
ORDER BY user_idAnother way
SELECT p.user_id, SUM(p.amount) AS total_paid
FROM BillPayment p
WHERE p.category = 'electricity' AND p.status = 'success' AND LEFT(p.paid_at, 7) IN ('2025-01', '2025-02', '2025-03')
AND EXISTS (SELECT 1 FROM BillPayment q WHERE q.user_id = p.user_id AND q.category = 'electricity' AND q.status = 'success' AND LEFT(q.paid_at, 7) = '2025-01')
AND EXISTS (SELECT 1 FROM BillPayment q WHERE q.user_id = p.user_id AND q.category = 'electricity' AND q.status = 'success' AND LEFT(q.paid_at, 7) = '2025-02')
AND EXISTS (SELECT 1 FROM BillPayment q WHERE q.user_id = p.user_id AND q.category = 'electricity' AND q.status = 'success' AND LEFT(q.paid_at, 7) = '2025-03')
GROUP BY p.user_id
ORDER BY p.user_idAnother way
SELECT user_id, SUM(amount) AS total_paid FROM BillPayment
WHERE category = 'electricity' AND status = 'success' AND QUARTER(paid_at) = 1 AND YEAR(paid_at) = 2025
GROUP BY user_id
HAVING SUM(MONTH(paid_at) = 1) > 0 AND SUM(MONTH(paid_at) = 2) > 0 AND SUM(MONTH(paid_at) = 3) > 0
ORDER BY user_id← Paytm: Runner-Up Movie by Seats Sold in Each City · Paytm: Days Loan Deductions Took Over Half a Merchant's Settlement →