Flipkart: Pay Later Bills Not Cleared by the Due Date
Every month Flipkart Pay Later raises one bill per account, due on the 5th of the next month. Customers may repay a bill in several parts.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #664
Problem statement
Every month Flipkart Pay Later raises one bill per account, due on the 5th of the next month. Customers may repay a bill in several parts. Collections wants the bills that were short on the due date.
For each bill, paid_by_due is the sum of its repayments made on or before due_on (0 if none). Return bill_id, account_id, paid_by_due and shortfall (amount - paid_by_due) for every bill where paid_by_due is less than the bill amount. Repayments after the due date do not help. Order by shortfall descending, then bill_id.
A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.
Tables
Table: PayLaterBill
| Column | Type |
|---|---|
| bill_id | int |
| account_id | int |
| bill_month | char |
| amount | int |
| due_on | date |
Primary key: bill_id.
One row per monthly bill; bill_month is 'YYYY-MM' and amount is in rupees.
Table: Repayment
| Column | Type |
|---|---|
| repayment_id | int |
| bill_id | int |
| paid_on | date |
| amount | int |
Primary key: repayment_id.
One row per payment towards a bill; bill_id always names a bill.
Examples
Example 1
PayLaterBill
| bill_id | account_id | bill_month | amount | due_on |
|---|---|---|---|---|
| 1 | 11 | 2024-08 | 4000 | 2024-09-05 |
| 2 | 12 | 2024-08 | 2500 | 2024-09-05 |
| 3 | 11 | 2024-09 | 3000 | 2024-10-05 |
| 4 | 13 | 2024-09 | 1200 | 2024-10-05 |
Repayment
| repayment_id | bill_id | paid_on | amount |
|---|---|---|---|
| 1 | 1 | 2024-09-01 | 2500 |
| 2 | 1 | 2024-09-05 | 1500 |
| 3 | 2 | 2024-09-04 | 1000 |
| 4 | 2 | 2024-09-08 | 1500 |
| 5 | 3 | 2024-10-06 | 3000 |
Output
| bill_id | account_id | paid_by_due | shortfall |
|---|---|---|---|
| 3 | 11 | 0 | 3000 |
| 2 | 12 | 1000 | 1500 |
| 4 | 13 | 0 | 1200 |
How to solve Flipkart: Pay Later Bills Not Cleared by the Due Date
Each bill needs the total of its repayments up to its own due date. The LEFT JOIN carries both conditions in its ON clause — the repayment belongs to the bill, and r.paid_on <= b.due_on — so a late repayment is never joined, and a bill with nothing paid on time keeps one NULL-filled row. Had the date test been in WHERE, those bills would have disappeared instead of showing their full shortfall.
After grouping by bill, SUM(r.amount) is NULL for such a bill, so COALESCE(…, 0) turns it into 0 paid. The HAVING clause keeps the bills where the on-time total is below the amount; a bill paid off exactly on the due date has a shortfall of 0 and is left out.
The alternative computes paid_by_due with a correlated scalar subquery per bill and filters in an outer query. Both read each bill's repayments once; an index on Repayment(bill_id, paid_on) serves either.
Reference solution (MySQL)
SELECT b.bill_id, b.account_id,
COALESCE(SUM(r.amount), 0) AS paid_by_due,
b.amount - COALESCE(SUM(r.amount), 0) AS shortfall
FROM PayLaterBill b
LEFT JOIN Repayment r ON r.bill_id = b.bill_id AND r.paid_on <= b.due_on
GROUP BY b.bill_id, b.account_id, b.amount
HAVING COALESCE(SUM(r.amount), 0) < b.amount
ORDER BY shortfall DESC, b.bill_idAnother way
SELECT bill_id, account_id, paid_by_due, amount - paid_by_due AS shortfall
FROM (SELECT b.bill_id, b.account_id, b.amount,
(SELECT IFNULL(SUM(r.amount), 0) FROM Repayment r WHERE r.bill_id = b.bill_id AND r.paid_on <= b.due_on) AS paid_by_due
FROM PayLaterBill b) t
WHERE paid_by_due < amount
ORDER BY shortfall DESC, bill_id← Flipkart: Top Three Brands per Category Plus an Others Line · Flipkart: Running GMV Through the Sale by Category →