Paytm: Busiest Day for Each EDC Card Machine — SQL Problem
EDC card machines at merchant counters take card swipes. The devices team sizes settlement capacity from each machine's busiest day.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #861
Problem statement
EDC card machines at merchant counters take card swipes. The devices team sizes settlement capacity from each machine's busiest day.
For every terminal with at least one approved swipe, find the calendar day with the highest total approved amount; if two days tie, take the earlier day. Declined swipes never count. Return terminal_id, merchant_name, busiest_date and day_amount, ordered by terminal_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: EdcTerminal
| Column | Type |
|---|---|
| terminal_id | int |
| merchant_name | varchar |
| city | varchar |
Primary key: terminal_id.
One row per card machine.
Table: CardSwipe
| Column | Type |
|---|---|
| swipe_id | int |
| terminal_id | int |
| swiped_at | datetime |
| amount | int |
| status | enum(approved, declined) |
Primary key: swipe_id.
One row per card tap, dip or swipe; terminal_id always names a terminal.
Examples
Example 1
EdcTerminal
| terminal_id | merchant_name | city |
|---|---|---|
| 1 | Lakshmi Textiles | Surat |
| 2 | Raj Electronics | Kanpur |
| 3 | Fresh Bakery | Kochi |
CardSwipe
| swipe_id | terminal_id | swiped_at | amount | status |
|---|---|---|---|---|
| 1 | 1 | 2025-03-01 11:00:00 | 1200 | approved |
| 2 | 1 | 2025-03-01 17:30:00 | 800 | approved |
| 3 | 1 | 2025-03-02 12:00:00 | 2500 | declined |
| 4 | 1 | 2025-03-02 13:00:00 | 1500 | approved |
| 5 | 1 | 2025-03-03 19:45:00 | 2000 | approved |
| 6 | 2 | 2025-03-02 16:00:00 | 5000 | declined |
| 7 | 3 | 2025-03-04 23:59:59 | 700 | approved |
| 8 | 3 | 2025-03-05 00:00:00 | 300 | approved |
Output
| terminal_id | merchant_name | busiest_date | day_amount |
|---|---|---|---|
| 1 | Lakshmi Textiles | 2025-03-01 | 2000 |
| 3 | Fresh Bakery | 2025-03-04 | 700 |
How to solve Paytm: Busiest Day for Each EDC Card Machine
Two levels of grouping. First collapse swipes to (terminal, day) totals: filter to approved swipes, take the calendar day with CAST(swiped_at AS DATE) — a swipe at 23:59:59 belongs to that day, one at 00:00:00 to the next — and SUM(amount).
Then pick one day per terminal. ROW_NUMBER() OVER (PARTITION BY terminal_id ORDER BY day_amount DESC, day) numbers each terminal's days from the biggest down, with the earlier date first among equal totals, so rn = 1 is exactly the requested day — Lakshmi Textiles' 1 March and 3 March both total 2,000 and the earlier wins. A terminal with only declined swipes has no daily rows and is not listed. Join the terminals at the end to fetch the merchant name.
The alternative keeps a day when no other day of the same terminal beats it — more money, or the same money on an earlier date — using NOT EXISTS over the same daily totals. It is quadratic per terminal but needs no window functions.
Reference solution (MySQL)
WITH daily AS (
SELECT terminal_id, CAST(swiped_at AS DATE) AS day, SUM(amount) AS day_amount
FROM CardSwipe
WHERE status = 'approved'
GROUP BY terminal_id, CAST(swiped_at AS DATE)
), ranked AS (
SELECT terminal_id, day, day_amount,
ROW_NUMBER() OVER (PARTITION BY terminal_id ORDER BY day_amount DESC, day) AS rn
FROM daily
)
SELECT t.terminal_id, t.merchant_name, r.day AS busiest_date, r.day_amount
FROM ranked r
JOIN EdcTerminal t ON t.terminal_id = r.terminal_id
WHERE r.rn = 1
ORDER BY t.terminal_idAnother way
WITH daily AS (SELECT terminal_id, LEFT(swiped_at, 10) AS day, SUM(amount) AS day_amount FROM CardSwipe WHERE status = 'approved' GROUP BY terminal_id, LEFT(swiped_at, 10))
SELECT t.terminal_id, t.merchant_name, d.day AS busiest_date, d.day_amount
FROM daily d JOIN EdcTerminal t ON t.terminal_id = d.terminal_id
WHERE NOT EXISTS (
SELECT 1 FROM daily e WHERE e.terminal_id = d.terminal_id
AND (e.day_amount > d.day_amount OR (e.day_amount = d.day_amount AND e.day < d.day))
)
ORDER BY t.terminal_id← Paytm: Scratch Cards That Expired Unscratched · Paytm: Flight Cancellation Refunds Under the Free Window →