Borrowers With Repeatedly Overdue EMIs

Medium
58.

Borrowers With Repeatedly Overdue EMIs

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

ColumnType
loan_idint
borrower_namevarchar
principalint
emi_amountint

Primary key: loan_id.

One row per personal loan.

Table: EmiSchedule

ColumnType
loan_idint
installment_noint
due_datedate
paid_datedate

Primary key (together): loan_id, installment_no.

One row per instalment of a loan. paid_date is NULL while the instalment is unpaid.

Example

The rows must come back in the order shown.

Loan

loan_idborrower_nameprincipalemi_amount
1Rahul Sharma30000014200
2Sneha Iyer1500007100
3Kabir Singh50000023650
4Emma Das2000009450

EmiSchedule

loan_idinstallment_nodue_datepaid_date
112024-09-052024-10-20
122024-10-05NULL
132024-11-05NULL
212024-09-052024-10-05
222024-10-052024-11-05
312024-09-052024-09-04
322024-10-052024-11-25
332024-11-05NULL
342024-12-05NULL
412024-11-01NULL

Output

loan_idborrower_nameoverdue_emis
1Rahul Sharma3
3Kabir Singh2
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.

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

ColumnType
loan_idint
borrower_namevarchar
principalint
emi_amountint

Primary key: loan_id.

One row per personal loan.

Table: EmiSchedule

ColumnType
loan_idint
installment_noint
due_datedate
paid_datedate

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_idborrower_nameprincipalemi_amount
1Rahul Sharma30000014200
2Sneha Iyer1500007100
3Kabir Singh50000023650
4Emma Das2000009450

EmiSchedule

loan_idinstallment_nodue_datepaid_date
112024-09-052024-10-20
122024-10-05NULL
132024-11-05NULL
212024-09-052024-10-05
222024-10-052024-11-05
312024-09-052024-09-04
322024-10-052024-11-25
332024-11-05NULL
342024-12-05NULL
412024-11-01NULL

Output

loan_idborrower_nameoverdue_emis
1Rahul Sharma3
3Kabir Singh2

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_id

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

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