Paytm: Merchant Days When QR Collections Fell by More Than Half

Medium
865.

Paytm: Merchant Days When QR Collections Fell by More Than Half

A sudden fall in a shop's QR collections often means a broken Soundbox or a scanner taped over, so the merchant-success team calls the shop.

A merchant's collection on a day is the sum of its successful payments that day; only days with at least one successful payment count as collection days. Compare each collection day with the merchant's previous collection day (not necessarily the calendar day before). Return the days whose collection is strictly less than half of the previous one, with merchant_id, collection_date, collection and previous_collection. A merchant's first collection day is never returned. Return the rows ordered by merchant_id, then collection_date.

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

ColumnTypeValues
payment_idint
merchant_idint
paid_atdatetime
amountint
statusenumsuccess, failed

Primary key: payment_id.

One row per customer payment to a merchant's QR code.

Example

The rows must come back in the order shown.

QrPayment

payment_idmerchant_idpaid_atamountstatus
112025-03-01 09:00:001000success
212025-03-01 18:00:00600success
312025-03-02 11:00:00900success
412025-03-04 10:30:00400success
512025-03-05 12:00:00200success
622025-03-01 13:00:003000success
722025-03-02 13:00:005000failed
822025-03-03 13:00:001000success
932025-03-02 08:00:00500success

Output

merchant_idcollection_datecollectionprevious_collection
12025-03-04400900
22025-03-0310003000
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: Merchant Days When QR Collections Fell by More Than Half

A sudden fall in a shop's QR collections often means a broken Soundbox or a scanner taped over, so the merchant-success team calls the shop.

  • Difficulty: Medium
  • Topics: Window Functions, Aggregation, Dates
  • Dialect: MySQL
  • Problem: #865

Problem statement

A sudden fall in a shop's QR collections often means a broken Soundbox or a scanner taped over, so the merchant-success team calls the shop.

A merchant's collection on a day is the sum of its successful payments that day; only days with at least one successful payment count as collection days. Compare each collection day with the merchant's previous collection day (not necessarily the calendar day before). Return the days whose collection is strictly less than half of the previous one, with merchant_id, collection_date, collection and previous_collection. A merchant's first collection day is never returned. Return the rows ordered by merchant_id, then collection_date.

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

ColumnType
payment_idint
merchant_idint
paid_atdatetime
amountint
statusenum(success, failed)

Primary key: payment_id.

One row per customer payment to a merchant's QR code.

Examples

Example 1

QrPayment

payment_idmerchant_idpaid_atamountstatus
112025-03-01 09:00:001000success
212025-03-01 18:00:00600success
312025-03-02 11:00:00900success
412025-03-04 10:30:00400success
512025-03-05 12:00:00200success
622025-03-01 13:00:003000success
722025-03-02 13:00:005000failed
822025-03-03 13:00:001000success
932025-03-02 08:00:00500success

Output

merchant_idcollection_datecollectionprevious_collection
12025-03-04400900
22025-03-0310003000

How to solve Paytm: Merchant Days When QR Collections Fell by More Than Half

Start by turning payments into collection days: keep successful payments, group by merchant and calendar day (CAST(paid_at AS DATE)), and sum. A day whose only payment failed — merchant 2 on 2 March — produces no row, so it can never be anyone's "previous day".

Then compare each day with the one before it in the same merchant's list. LAG(collection) OVER (PARTITION BY merchant_id ORDER BY collection_date) reads the previous collection day's total, however many calendar days back it is — merchant 1's 4 March is compared with 2 March. The test collection * 2 < previous_collection is "strictly less than half" in integers: 200 against 400 is exactly half and is not reported. On a merchant's first day LAG returns NULL, the comparison is unknown, and the row is filtered out without a special case.

The alternative finds the previous day with a correlated MAX(collection_date) below the current one and joins back to its total. It is quadratic per merchant; the window version is one sort.

Reference solution (MySQL)

WITH daily AS (
  SELECT merchant_id, CAST(paid_at AS DATE) AS collection_date, SUM(amount) AS collection
  FROM QrPayment
  WHERE status = 'success'
  GROUP BY merchant_id, CAST(paid_at AS DATE)
), compared AS (
  SELECT merchant_id, collection_date, collection,
         LAG(collection) OVER (PARTITION BY merchant_id ORDER BY collection_date) AS previous_collection
  FROM daily
)
SELECT merchant_id, collection_date, collection, previous_collection
FROM compared
WHERE collection * 2 < previous_collection
ORDER BY merchant_id, collection_date

Another way

WITH daily AS (SELECT merchant_id, LEFT(paid_at, 10) AS collection_date, SUM(amount) AS collection FROM QrPayment WHERE status = 'success' GROUP BY merchant_id, LEFT(paid_at, 10))
SELECT cur.merchant_id, cur.collection_date, cur.collection, prev.collection AS previous_collection
FROM daily cur
JOIN daily prev ON prev.merchant_id = cur.merchant_id
 AND prev.collection_date = (SELECT MAX(x.collection_date) FROM daily x WHERE x.merchant_id = cur.merchant_id AND x.collection_date < cur.collection_date)
WHERE cur.collection < prev.collection / 2
ORDER BY cur.merchant_id, cur.collection_date

← Paytm: Cashback Paid Above the Per-User Campaign Cap · Paytm: Gold Buyers Whose Weighted Cost Beat the Simple Average →