Tenants Late on Rent Three Months Running — SQL Problem
A rental company escalates a lease when its rent was late for three or more consecutive calendar months.
- Difficulty: Hard
- Topics: Window Functions, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #366
Problem statement
A rental company escalates a lease when its rent was late for three or more consecutive calendar months. Each month's rent row has rent_month (the first of the month); rent is late when paid_on is NULL or after rent_month + 5 days. A month with no rent row (the lease was on a rent holiday) breaks a streak.
Return one row per streak of 3+ consecutive late months: lease_id, tenant_name, streak_start and streak_end (the first and last rent_month of the streak) and months. Order by lease_id, then streak_start.
Tables
Table: Tenancy
| Column | Type |
|---|---|
| lease_id | int |
| tenant_name | varchar |
| flat_no | varchar |
Primary key: lease_id.
Table: MonthlyRent
| Column | Type |
|---|---|
| lease_id | int |
| rent_month | date |
| paid_on | date |
Primary key: lease_id, rent_month.
rent_month is always the first day of a month. paid_on is NULL when the rent was never received.
Examples
Example 1
Tenancy
| lease_id | tenant_name | flat_no |
|---|---|---|
| 1 | Rahul Sharma | A-302 |
| 2 | Zara Khan | B-1204 |
| 3 | John Mehta | C-701 |
| 4 | Emma Bose | D-105 |
MonthlyRent
| lease_id | rent_month | paid_on |
|---|---|---|
| 1 | 2024-10-01 | 2024-10-03 |
| 1 | 2024-11-01 | 2024-11-09 |
| 1 | 2024-12-01 | NULL |
| 1 | 2025-01-01 | 2025-01-20 |
| 1 | 2025-02-01 | 2025-02-06 |
| 4 | 2024-11-01 | NULL |
| 4 | 2024-12-01 | NULL |
| 4 | 2025-01-01 | 2025-01-10 |
| 2 | 2024-11-01 | 2024-11-12 |
| 2 | 2024-12-01 | 2024-12-15 |
| 2 | 2025-02-01 | 2025-02-10 |
| 2 | 2025-03-01 | 2025-03-04 |
| 3 | 2024-12-01 | 2024-12-06 |
| 3 | 2025-01-01 | 2025-01-08 |
| 3 | 2025-02-01 | 2025-02-07 |
Output
| lease_id | tenant_name | streak_start | streak_end | months |
|---|---|---|---|---|
| 1 | Rahul Sharma | 2024-11-01 | 2025-01-01 | 3 |
| 4 | Emma Bose | 2024-11-01 | 2025-01-01 | 3 |
How to solve Tenants Late on Rent Three Months Running
This is gaps and islands. Filter to the late months (paid_on NULL or more than 5 days after the first of the month) and number the months continuously with YEAR * 12 + MONTH, so December 2024 and January 2025 are neighbours. Along a run of consecutive late months, the month number rises by 1 and so does ROW_NUMBER() within the lease — their difference is constant, and it changes the moment a month is skipped. That difference is the island key: group by lease and key, and each group is one streak with MIN/MAX as its ends and COUNT(*) as its length.
A gap breaks a streak either way: an on-time month is filtered out, and a missing month (Zara's January rent holiday) is simply absent, so in both cases the month numbers jump. Mind the grace boundary: rent for 1 February paid on the 6th is exactly 5 days later and on time, so Rahul's streak is November–January (paid on the 9th, never, the 20th) — three months. John paid on the 6th (on time), 8th and 7th: only two late months. Emma's streak runs across the new year, which the YEAR * 12 + MONTH numbering handles.
The alternative marks a streak start wherever LAG of the month number is not the previous month and takes a running SUM of the marks as the group id. Both are a sort per lease.
Reference solution (MySQL)
WITH late AS (
SELECT lease_id, rent_month,
YEAR(rent_month) * 12 + MONTH(rent_month)
- ROW_NUMBER() OVER (PARTITION BY lease_id ORDER BY rent_month) AS island
FROM MonthlyRent
WHERE paid_on IS NULL OR paid_on > DATE_ADD(rent_month, INTERVAL 5 DAY)
)
SELECT l.lease_id, t.tenant_name,
MIN(l.rent_month) AS streak_start,
MAX(l.rent_month) AS streak_end,
COUNT(*) AS months
FROM late l
JOIN Tenancy t ON t.lease_id = l.lease_id
GROUP BY l.lease_id, t.tenant_name, l.island
HAVING COUNT(*) >= 3
ORDER BY l.lease_id, streak_startAnother way
WITH flagged AS (
SELECT lease_id, rent_month, YEAR(rent_month) * 12 + MONTH(rent_month) AS mi,
CASE WHEN paid_on IS NULL OR DATEDIFF(paid_on, rent_month) > 5 THEN 1 ELSE 0 END AS is_late
FROM MonthlyRent
), marked AS (
SELECT lease_id, rent_month, mi,
CASE WHEN LAG(mi) OVER (PARTITION BY lease_id ORDER BY mi) = mi - 1 THEN 0 ELSE 1 END AS starts
FROM flagged WHERE is_late = 1
), grouped AS (
SELECT lease_id, rent_month,
SUM(starts) OVER (PARTITION BY lease_id ORDER BY mi ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
FROM marked
)
SELECT g.lease_id, t.tenant_name, MIN(g.rent_month) AS streak_start, MAX(g.rent_month) AS streak_end, COUNT(*) AS months
FROM grouped g JOIN Tenancy t ON t.lease_id = g.lease_id
GROUP BY g.lease_id, t.tenant_name, g.grp
HAVING COUNT(*) >= 3
ORDER BY g.lease_id, streak_start← Towers Where Every Unit Cleared March Dues · Median Registered Rate per Sq Ft by Locality →