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
| Column | Type |
|---|---|
| merchant_id | int |
| settle_date | date |
| gross_amount | int |
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
| Column | Type |
|---|---|
| deduction_id | int |
| merchant_id | int |
| loan_id | int |
| settle_date | date |
| amount | int |
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_id | settle_date | gross_amount |
|---|---|---|
| 1 | 2025-02-01 | 10000 |
| 1 | 2025-02-02 | 4000 |
| 2 | 2025-02-01 | 6000 |
| 2 | 2025-02-02 | 3000 |
| 3 | 2025-02-01 | 8000 |
LoanDeduction
| deduction_id | merchant_id | loan_id | settle_date | amount |
|---|---|---|---|---|
| 1 | 1 | 501 | 2025-02-01 | 3000 |
| 2 | 1 | 501 | 2025-02-02 | 1500 |
| 3 | 1 | 502 | 2025-02-02 | 1000 |
| 4 | 2 | 503 | 2025-02-01 | 3000 |
| 5 | 2 | 503 | 2025-02-02 | 1600 |
| 6 | 3 | 504 | 2025-02-01 | 4100 |
Output
| merchant_id | settle_date | gross_amount | deducted |
|---|---|---|---|
| 1 | 2025-02-02 | 4000 | 2500 |
| 2 | 2025-02-02 | 3000 | 1600 |
| 3 | 2025-02-01 | 8000 | 4100 |
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_dateAnother 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 →