Paytm: Users Who Paid Electricity in Every Month of Q1 2025

Medium
857.

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

ColumnTypeValues
payment_idint
user_idint
categoryenumelectricity, water, gas, broadband
paid_atdatetime
amountint
statusenumsuccess, failed

Primary key: payment_id.

One row per bill payment attempt.

Example

The rows must come back in the order shown.

BillPayment

payment_iduser_idcategorypaid_atamountstatus
11electricity2025-01-05 10:00:001450success
21electricity2025-02-06 09:20:001320success
31water2025-02-06 09:25:00300success
41electricity2025-03-31 23:59:001180success
52electricity2025-01-10 12:00:00980success
62electricity2025-02-11 12:00:001010success
72electricity2025-03-12 12:00:00990failed
82electricity2025-04-01 08:00:00990success
93electricity2025-01-02 08:00:00600success
103electricity2025-01-29 08:00:00650success
8 more rows

Output

user_idtotal_paid
13950
32670
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.

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

ColumnType
payment_idint
user_idint
categoryenum(electricity, water, gas, broadband)
paid_atdatetime
amountint
statusenum(success, failed)

Primary key: payment_id.

One row per bill payment attempt.

Examples

Example 1

BillPayment

payment_iduser_idcategorypaid_atamountstatus
11electricity2025-01-05 10:00:001450success
21electricity2025-02-06 09:20:001320success
31water2025-02-06 09:25:00300success
41electricity2025-03-31 23:59:001180success
52electricity2025-01-10 12:00:00980success
62electricity2025-02-11 12:00:001010success
72electricity2025-03-12 12:00:00990failed
82electricity2025-04-01 08:00:00990success
93electricity2025-01-02 08:00:00600success
103electricity2025-01-29 08:00:00650success
113electricity2025-02-27 08:00:00700success
123electricity2025-03-28 08:00:00720success
134electricity2024-12-31 21:00:00800success
144electricity2025-02-15 21:00:00820success
154electricity2025-03-15 21:00:00810success
165gas2025-01-20 07:00:00500success
175electricity2025-02-20 07:00:00900success
185electricity2025-03-20 07:00:00905success

Output

user_idtotal_paid
13950
32670

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_id

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

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