Paytm: Days Loan Deductions Took Over Half a Merchant's Settlement

Medium
858.

Paytm: Days Loan Deductions Took Over Half a Merchant's Settlement

MediumJoinsAggregation

MySQL

Merchant loans are repaid by deducting a slice of the merchant's daily settlement. The lending risk team flags days when the deductions left the shop with too little cash.

A merchant may hold more than one loan, so several deductions can fall on one settlement day. Return every (merchant, day) where the total deducted is strictly more than half the day's gross_amount, with the columns merchant_id, settle_date, gross_amount and deducted. Exactly half is not flagged. Return the rows ordered by merchant_id, then settle_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: Settlement

ColumnType
merchant_idint
settle_datedate
gross_amountint

Primary key (together): merchant_id, settle_date.

One row per merchant per day the merchant was paid out: the day's collections before any deduction.

Table: LoanDeduction

ColumnType
deduction_idint
merchant_idint
loan_idint
settle_datedate
amountint

Primary key: deduction_id.

One row per loan instalment taken out of a settlement; every deduction falls on a day that has a Settlement row for the merchant.

Example

The rows must come back in the order shown.

Settlement

merchant_idsettle_dategross_amount
12025-02-0110000
12025-02-024000
22025-02-016000
22025-02-023000
32025-02-018000

LoanDeduction

deduction_idmerchant_idloan_idsettle_dateamount
115012025-02-013000
215012025-02-021500
315022025-02-021000
425032025-02-013000
525032025-02-021600
635042025-02-014100

Output

merchant_idsettle_dategross_amountdeducted
12025-02-0240002500
22025-02-0230001600
32025-02-0180004100
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: Days Loan Deductions Took Over Half a Merchant's Settlement

Merchant loans are repaid by deducting a slice of the merchant's daily settlement.

  • Difficulty: Medium
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #858

Problem statement

Merchant loans are repaid by deducting a slice of the merchant's daily settlement. The lending risk team flags days when the deductions left the shop with too little cash.

A merchant may hold more than one loan, so several deductions can fall on one settlement day. Return every (merchant, day) where the total deducted is strictly more than half the day's gross_amount, with the columns merchant_id, settle_date, gross_amount and deducted. Exactly half is not flagged. Return the rows ordered by merchant_id, then settle_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: Settlement

ColumnType
merchant_idint
settle_datedate
gross_amountint

Primary key: merchant_id, settle_date.

One row per merchant per day the merchant was paid out: the day's collections before any deduction.

Table: LoanDeduction

ColumnType
deduction_idint
merchant_idint
loan_idint
settle_datedate
amountint

Primary key: deduction_id.

One row per loan instalment taken out of a settlement; every deduction falls on a day that has a Settlement row for the merchant.

Examples

Example 1

Settlement

merchant_idsettle_dategross_amount
12025-02-0110000
12025-02-024000
22025-02-016000
22025-02-023000
32025-02-018000

LoanDeduction

deduction_idmerchant_idloan_idsettle_dateamount
115012025-02-013000
215012025-02-021500
315022025-02-021000
425032025-02-013000
525032025-02-021600
635042025-02-014100

Output

merchant_idsettle_dategross_amountdeducted
12025-02-0240002500
22025-02-0230001600
32025-02-0180004100

How to solve Paytm: Days Loan Deductions Took Over Half a Merchant's Settlement

The comparison is between a day's settlement and the total of that day's deductions, so the deductions have to be summed per (merchant, day) first. Joining Settlement to LoanDeduction on both merchant_id and settle_date puts each deduction beside its day; grouping by the settlement's key and SUM(d.amount) gives the total. Merchant 1 on 2 February is the reason: neither 1,500 nor 1,000 is more than half of 4,000, but together they are.

The condition is on an aggregate, so it goes in HAVING. Writing it as SUM(d.amount) * 2 > s.gross_amount keeps it in integers; > s.gross_amount / 2 is the same test because MySQL's / is exact decimal division. Exactly half (merchant 2 on 1 February) is not strictly more and is not flagged. Days with no deduction never join and so are never flagged.

The alternative sums the deductions in a derived table and joins the result to the settlements, filtering with WHERE. Both read each table once.

Reference solution (MySQL)

SELECT s.merchant_id, s.settle_date, s.gross_amount, SUM(d.amount) AS deducted
FROM Settlement s
JOIN LoanDeduction d ON d.merchant_id = s.merchant_id AND d.settle_date = s.settle_date
GROUP BY s.merchant_id, s.settle_date, s.gross_amount
HAVING SUM(d.amount) * 2 > s.gross_amount
ORDER BY s.merchant_id, s.settle_date

Another way

SELECT s.merchant_id, s.settle_date, s.gross_amount, t.deducted
FROM Settlement s
JOIN (SELECT merchant_id, settle_date, SUM(amount) AS deducted FROM LoanDeduction GROUP BY merchant_id, settle_date) t
  ON t.merchant_id = s.merchant_id AND t.settle_date = s.settle_date
WHERE t.deducted > s.gross_amount / 2
ORDER BY s.merchant_id, s.settle_date

← Paytm: Users Who Paid Electricity in Every Month of Q1 2025 · Paytm Money: SIPs That Bounced Twice in a Row →