Loans by Missed Installments
A lender tracks repayment schedules in installments. An installment is missed when its due_date is before '2026-06-30' and its paid_date is NULL.
For every loan in loans (including loans with no installments yet), count missed installments and assign a bucket:
- 0 missed: current
- 1 or 2 missed: watch
- 3 or more: default_risk
Return columns loan_id, missed_installments, bucket. Row order does not matter.
Schema
CREATE TABLE loans ( loan_id INTEGER PRIMARY KEY, borrower TEXT NOT NULL, principal REAL NOT NULL ); CREATE TABLE installments ( loan_id INTEGER NOT NULL, installment_no INTEGER NOT NULL, due_date TEXT NOT NULL, paid_date TEXT, PRIMARY KEY (loan_id, installment_no) );
Sample data
loans
| loan_id | borrower | principal |
|---|---|---|
| 1 | Karim | 10000 |
| 2 | Lina | 5000 |
| 3 | Mo | 7500 |
| 4 | Nia | 2000 |
installments
| loan_id | installment_no | due_date | paid_date |
|---|---|---|---|
| 1 | 1 | 2026-03-31 | 2026-03-30 |
| 1 | 2 | 2026-04-30 | 2026-05-02 |
| 1 | 3 | 2026-05-31 | NULL |
| 2 | 1 | 2026-03-31 | NULL |
| 2 | 2 | 2026-04-30 | NULL |
| 2 | 3 | 2026-05-31 | NULL |
| 2 | 4 | 2026-06-30 | NULL |
| 3 | 1 | 2026-05-31 | 2026-05-31 |
| 3 | 2 | 2026-06-30 | NULL |
Hint 1
Put the "missed" conditions in the LEFT JOIN's ON clause so loans with nothing missed still get a row.
Hint 2
An installment due exactly on 2026-06-30 is not yet missed.
Good practice for: Data Analyst, Business Analyst, Data Scientist, Backend Developer
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.