Paytm: Merchant Loan Outstanding as of 31 January 2025

Hard
870.

Paytm: Merchant Loan Outstanding as of 31 January 2025

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

ColumnTypeValues
loan_idint
merchant_idint
productenumdaily_deduction, fixed_emi

Primary key: loan_id.

One row per loan sanctioned to a merchant.

Table: LoanLedger

ColumnTypeValues
entry_idint
loan_idint
posted_atdatetime
entry_typeenumdisbursal, fee, repayment, waiver
amountint

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.

Example

The rows must come back in the order shown.

MerchantLoan

loan_idmerchant_idproduct
111daily_deduction
212fixed_emi
313daily_deduction
414daily_deduction
515fixed_emi

LoanLedger

entry_idloan_idposted_atentry_typeamount
112025-01-02 10:00:00disbursal10000
212025-01-02 10:00:00fee300
312025-01-10 21:00:00repayment2000
412025-01-31 23:59:59repayment1000
512025-02-01 00:00:00repayment5000
622025-01-15 12:00:00disbursal5000
722025-01-15 12:00:00fee150
832025-02-01 09:00:00disbursal8000
942024-12-20 11:00:00disbursal3000
1042024-12-20 11:00:00fee90
7 more rows

Output

loan_idmerchant_idoutstandinglast_repaid_on
11173002025-01-31 23:59:59
2125150NULL
51526202025-01-12 21:30:00
41402025-01-25 21:00:00
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 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

ColumnType
loan_idint
merchant_idint
productenum(daily_deduction, fixed_emi)

Primary key: loan_id.

One row per loan sanctioned to a merchant.

Table: LoanLedger

ColumnType
entry_idint
loan_idint
posted_atdatetime
entry_typeenum(disbursal, fee, repayment, waiver)
amountint

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_idmerchant_idproduct
111daily_deduction
212fixed_emi
313daily_deduction
414daily_deduction
515fixed_emi

LoanLedger

entry_idloan_idposted_atentry_typeamount
112025-01-02 10:00:00disbursal10000
212025-01-02 10:00:00fee300
312025-01-10 21:00:00repayment2000
412025-01-31 23:59:59repayment1000
512025-02-01 00:00:00repayment5000
622025-01-15 12:00:00disbursal5000
722025-01-15 12:00:00fee150
832025-02-01 09:00:00disbursal8000
942024-12-20 11:00:00disbursal3000
1042024-12-20 11:00:00fee90
1142025-01-05 21:00:00repayment2000
1242025-01-20 15:00:00waiver90
1342025-01-25 21:00:00repayment1000
1452025-01-10 10:00:00disbursal4000
1552025-01-10 10:00:00fee120
1652025-01-12 21:00:00repayment1000
1752025-01-12 21:30:00repayment500

Output

loan_idmerchant_idoutstandinglast_repaid_on
11173002025-01-31 23:59:59
2125150NULL
51526202025-01-12 21:30:00
41402025-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_id

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