Paytm: Merchant Loan Outstanding as of 31 January 2025
Every movement on a merchant loan is a row in the loan ledger.
- Difficulty: Hard
- Topics: Aggregation, Conditional Logic, Dates, Joins
- Dialect: MySQL
- Problem: #870
Problem statement
Every movement on a merchant loan is a row in the loan ledger. For the January month-end book, the lending finance team needs each loan's balance as it stood at the end of 31 January 2025 — later postings must not leak in.
Use only entries posted on or before 2025-01-31 23:59:59. A loan's outstanding balance is its disbursal and fee amounts minus its repayment and waiver amounts. Report a loan only if its disbursal was posted by then. Return loan_id, merchant_id, outstanding and last_repaid_on (the posting time of its latest repayment up to that moment, NULL if none). A fully repaid loan shows 0 and is still listed. Return the rows ordered by outstanding descending, then by loan_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: MerchantLoan
| Column | Type |
|---|---|
| loan_id | int |
| merchant_id | int |
| product | enum(daily_deduction, fixed_emi) |
Primary key: loan_id.
One row per loan sanctioned to a merchant.
Table: LoanLedger
| Column | Type |
|---|---|
| entry_id | int |
| loan_id | int |
| posted_at | datetime |
| entry_type | enum(disbursal, fee, repayment, waiver) |
| amount | int |
Primary key: entry_id.
One row per posting; amount is always positive and entry_type says which way it moves the balance. Each loan has one disbursal, and its balance never goes below 0.
Examples
Example 1
MerchantLoan
| loan_id | merchant_id | product |
|---|---|---|
| 1 | 11 | daily_deduction |
| 2 | 12 | fixed_emi |
| 3 | 13 | daily_deduction |
| 4 | 14 | daily_deduction |
| 5 | 15 | fixed_emi |
LoanLedger
| entry_id | loan_id | posted_at | entry_type | amount |
|---|---|---|---|---|
| 1 | 1 | 2025-01-02 10:00:00 | disbursal | 10000 |
| 2 | 1 | 2025-01-02 10:00:00 | fee | 300 |
| 3 | 1 | 2025-01-10 21:00:00 | repayment | 2000 |
| 4 | 1 | 2025-01-31 23:59:59 | repayment | 1000 |
| 5 | 1 | 2025-02-01 00:00:00 | repayment | 5000 |
| 6 | 2 | 2025-01-15 12:00:00 | disbursal | 5000 |
| 7 | 2 | 2025-01-15 12:00:00 | fee | 150 |
| 8 | 3 | 2025-02-01 09:00:00 | disbursal | 8000 |
| 9 | 4 | 2024-12-20 11:00:00 | disbursal | 3000 |
| 10 | 4 | 2024-12-20 11:00:00 | fee | 90 |
| 11 | 4 | 2025-01-05 21:00:00 | repayment | 2000 |
| 12 | 4 | 2025-01-20 15:00:00 | waiver | 90 |
| 13 | 4 | 2025-01-25 21:00:00 | repayment | 1000 |
| 14 | 5 | 2025-01-10 10:00:00 | disbursal | 4000 |
| 15 | 5 | 2025-01-10 10:00:00 | fee | 120 |
| 16 | 5 | 2025-01-12 21:00:00 | repayment | 1000 |
| 17 | 5 | 2025-01-12 21:30:00 | repayment | 500 |
Output
| loan_id | merchant_id | outstanding | last_repaid_on |
|---|---|---|---|
| 1 | 11 | 7300 | 2025-01-31 23:59:59 |
| 2 | 12 | 5150 | NULL |
| 5 | 15 | 2620 | 2025-01-12 21:30:00 |
| 4 | 14 | 0 | 2025-01-25 21:00:00 |
How to solve Paytm: Merchant Loan Outstanding as of 31 January 2025
A ledger stores movements, not balances, so a balance as of a moment is rebuilt by adding up every movement posted up to that moment. The cut comes first: posted_at < '2025-02-01 00:00:00' keeps loan 1's repayment at 23:59:59 on 31 January and drops its 5,000 posted at midnight on 1 February, which belongs to February's book. Writing the bound as <= '2025-01-31' would compare a timestamp with a bare date and lose the whole last day.
Each surviving entry then gets a sign — +amount for a disbursal or fee, -amount for a repayment or waiver — and SUM per loan is the balance. Loan 4 shows how waivers count: 3,000 + 90 − 2,000 − 90 − 1,000 = 0, still listed as fully repaid. MAX(CASE WHEN entry_type = 'repayment' THEN posted_at END) picks the latest repayment inside the cut; a loan with none gets NULL because MAX ignores the NULLs the CASE produces.
A loan disbursed after the cut (loan 3) has no entries left, so the inner join already removes it; HAVING SUM(entry_type = 'disbursal') > 0 states the rule explicitly. The alternative computes the two sides of the balance and the last repayment with correlated subqueries and checks the disbursal with EXISTS. Both read each loan's ledger once with an index on (loan_id, posted_at).
Reference solution (MySQL)
SELECT l.loan_id, l.merchant_id,
SUM(CASE WHEN e.entry_type IN ('disbursal', 'fee') THEN e.amount ELSE -e.amount END) AS outstanding,
MAX(CASE WHEN e.entry_type = 'repayment' THEN e.posted_at END) AS last_repaid_on
FROM MerchantLoan l
JOIN LoanLedger e ON e.loan_id = l.loan_id
WHERE e.posted_at < '2025-02-01 00:00:00'
GROUP BY l.loan_id, l.merchant_id
HAVING SUM(e.entry_type = 'disbursal') > 0
ORDER BY outstanding DESC, l.loan_idAnother way
SELECT loan_id, merchant_id, outstanding, last_repaid_on FROM (
SELECT l.loan_id, l.merchant_id,
(SELECT COALESCE(SUM(e.amount), 0) FROM LoanLedger e WHERE e.loan_id = l.loan_id AND e.posted_at <= '2025-01-31 23:59:59' AND e.entry_type IN ('disbursal', 'fee'))
- (SELECT COALESCE(SUM(e.amount), 0) FROM LoanLedger e WHERE e.loan_id = l.loan_id AND e.posted_at <= '2025-01-31 23:59:59' AND e.entry_type IN ('repayment', 'waiver')) AS outstanding,
(SELECT MAX(e.posted_at) FROM LoanLedger e WHERE e.loan_id = l.loan_id AND e.posted_at <= '2025-01-31 23:59:59' AND e.entry_type = 'repayment') AS last_repaid_on
FROM MerchantLoan l
WHERE EXISTS (SELECT 1 FROM LoanLedger d WHERE d.loan_id = l.loan_id AND d.entry_type = 'disbursal' AND d.posted_at <= '2025-01-31 23:59:59')
) x
ORDER BY outstanding DESC, loan_id← Paytm: Daily Seats Sold for Every Day of a Movie's Run · Paytm: Spending Before and After Cashback for Each Campaign →