Paytm: Busiest Day for Each EDC Card Machine

Medium
861.

Paytm: Busiest Day for Each EDC Card Machine

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

ColumnType
terminal_idint
merchant_namevarchar
cityvarchar

Primary key: terminal_id.

One row per card machine.

Table: CardSwipe

ColumnTypeValues
swipe_idint
terminal_idint
swiped_atdatetime
amountint
statusenumapproved, declined

Primary key: swipe_id.

One row per card tap, dip or swipe; terminal_id always names a terminal.

Example

The rows must come back in the order shown.

EdcTerminal

terminal_idmerchant_namecity
1Lakshmi TextilesSurat
2Raj ElectronicsKanpur
3Fresh BakeryKochi

CardSwipe

swipe_idterminal_idswiped_atamountstatus
112025-03-01 11:00:001200approved
212025-03-01 17:30:00800approved
312025-03-02 12:00:002500declined
412025-03-02 13:00:001500approved
512025-03-03 19:45:002000approved
622025-03-02 16:00:005000declined
732025-03-04 23:59:59700approved
832025-03-05 00:00:00300approved

Output

terminal_idmerchant_namebusiest_dateday_amount
1Lakshmi Textiles2025-03-012000
3Fresh Bakery2025-03-04700
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: 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

ColumnType
terminal_idint
merchant_namevarchar
cityvarchar

Primary key: terminal_id.

One row per card machine.

Table: CardSwipe

ColumnType
swipe_idint
terminal_idint
swiped_atdatetime
amountint
statusenum(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_idmerchant_namecity
1Lakshmi TextilesSurat
2Raj ElectronicsKanpur
3Fresh BakeryKochi

CardSwipe

swipe_idterminal_idswiped_atamountstatus
112025-03-01 11:00:001200approved
212025-03-01 17:30:00800approved
312025-03-02 12:00:002500declined
412025-03-02 13:00:001500approved
512025-03-03 19:45:002000approved
622025-03-02 16:00:005000declined
732025-03-04 23:59:59700approved
832025-03-05 00:00:00300approved

Output

terminal_idmerchant_namebusiest_dateday_amount
1Lakshmi Textiles2025-03-012000
3Fresh Bakery2025-03-04700

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_id

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