Borrowers With Repeatedly Overdue EMIs — SQL Joins Problem
The collections team pulled the EMI register on 2024-12-31. An instalment counts as overdue when it was paid more than 30 days after its due date, or when…
- Difficulty: Medium
- Topics: Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #58
Problem statement
The collections team pulled the EMI register on 2024-12-31. An instalment counts as overdue when it was paid more than 30 days after its due date, or when it is still unpaid (paid_date NULL) and its due date is more than 30 days before 2024-12-31. Paying exactly 30 days late is not overdue, and an unpaid instalment due 30 days or less before the pull date is not overdue yet.
Return loan_id, borrower_name and overdue_emis (the number of overdue instalments) for every loan with at least 2 overdue instalments. Order the rows by overdue_emis descending, then by loan_id ascending.
Tables
Table: Loan
| Column | Type |
|---|---|
| loan_id | int |
| borrower_name | varchar |
| principal | int |
| emi_amount | int |
Primary key: loan_id.
One row per personal loan.
Table: EmiSchedule
| Column | Type |
|---|---|
| loan_id | int |
| installment_no | int |
| due_date | date |
| paid_date | date |
Primary key: loan_id, installment_no.
One row per instalment of a loan. paid_date is NULL while the instalment is unpaid.
Examples
Example 1
Loan
| loan_id | borrower_name | principal | emi_amount |
|---|---|---|---|
| 1 | Rahul Sharma | 300000 | 14200 |
| 2 | Sneha Iyer | 150000 | 7100 |
| 3 | Kabir Singh | 500000 | 23650 |
| 4 | Emma Das | 200000 | 9450 |
EmiSchedule
| loan_id | installment_no | due_date | paid_date |
|---|---|---|---|
| 1 | 1 | 2024-09-05 | 2024-10-20 |
| 1 | 2 | 2024-10-05 | NULL |
| 1 | 3 | 2024-11-05 | NULL |
| 2 | 1 | 2024-09-05 | 2024-10-05 |
| 2 | 2 | 2024-10-05 | 2024-11-05 |
| 3 | 1 | 2024-09-05 | 2024-09-04 |
| 3 | 2 | 2024-10-05 | 2024-11-25 |
| 3 | 3 | 2024-11-05 | NULL |
| 3 | 4 | 2024-12-05 | NULL |
| 4 | 1 | 2024-11-01 | NULL |
Output
| loan_id | borrower_name | overdue_emis |
|---|---|---|
| 1 | Rahul Sharma | 3 |
| 3 | Kabir Singh | 2 |
How to solve Borrowers With Repeatedly Overdue EMIs
The rule has two arms. For a paid instalment the delay is DATEDIFF(paid_date, due_date); for an unpaid one the delay so far is DATEDIFF('2024-12-31', due_date). Either way the instalment is overdue when the delay is more than 30 days, so exactly 30 is on the safe side. Filter the instalments with that condition, join the loan for the borrower's name, group by loan and keep the groups with COUNT(*) >= 2.
The two arms collapse into one with COALESCE(paid_date, '2024-12-31'): an unpaid instalment is treated as paid on the pull date. That makes conditional aggregation natural — SUM(CASE WHEN … THEN 1 ELSE 0 END) over all instalments — and the same idea can be written as DATE_ADD(due_date, INTERVAL 30 DAY) < … in a correlated count.
Forgetting the unpaid arm is the usual mistake: DATEDIFF(NULL, due_date) is NULL, so the worst borrowers — those who stopped paying — would silently drop out. The query is one pass over the schedule with a group-by on the loan.
Reference solution (MySQL)
SELECT l.loan_id, l.borrower_name, COUNT(*) AS overdue_emis
FROM Loan l
JOIN EmiSchedule e ON e.loan_id = l.loan_id
WHERE (e.paid_date IS NOT NULL AND DATEDIFF(e.paid_date, e.due_date) > 30)
OR (e.paid_date IS NULL AND DATEDIFF('2024-12-31', e.due_date) > 30)
GROUP BY l.loan_id, l.borrower_name
HAVING COUNT(*) >= 2
ORDER BY overdue_emis DESC, l.loan_idAnother way
SELECT l.loan_id, l.borrower_name,
SUM(CASE WHEN DATEDIFF(COALESCE(e.paid_date, '2024-12-31'), e.due_date) > 30 THEN 1 ELSE 0 END) AS overdue_emis
FROM Loan l JOIN EmiSchedule e ON e.loan_id = l.loan_id
GROUP BY l.loan_id, l.borrower_name
HAVING overdue_emis >= 2 ORDER BY overdue_emis DESC, l.loan_idAnother way
SELECT * FROM (
SELECT l.loan_id, l.borrower_name,
(SELECT COUNT(*) FROM EmiSchedule e WHERE e.loan_id = l.loan_id
AND DATE_ADD(e.due_date, INTERVAL 30 DAY) < COALESCE(e.paid_date, '2024-12-31')) AS overdue_emis
FROM Loan l
) t WHERE overdue_emis >= 2 ORDER BY overdue_emis DESC, loan_id← Current Balance From the Account Ledger · Largest Debit on Each Savings Account →