Tenants Late on Rent Three Months Running

Hard
366.

Tenants Late on Rent Three Months Running

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

ColumnType
lease_idint
tenant_namevarchar
flat_novarchar

Primary key: lease_id.

Table: MonthlyRent

ColumnType
lease_idint
rent_monthdate
paid_ondate

Primary key (together): lease_id, rent_month.

rent_month is always the first day of a month. paid_on is NULL when the rent was never received.

Example

The rows must come back in the order shown.

Tenancy

lease_idtenant_nameflat_no
1Rahul SharmaA-302
2Zara KhanB-1204
3John MehtaC-701
4Emma BoseD-105

MonthlyRent

lease_idrent_monthpaid_on
12024-10-012024-10-03
12024-11-012024-11-09
12024-12-01NULL
12025-01-012025-01-20
12025-02-012025-02-06
42024-11-01NULL
42024-12-01NULL
42025-01-012025-01-10
22024-11-012024-11-12
22024-12-012024-12-15
5 more rows

Output

lease_idtenant_namestreak_startstreak_endmonths
1Rahul Sharma2024-11-012025-01-013
4Emma Bose2024-11-012025-01-013
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.

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

ColumnType
lease_idint
tenant_namevarchar
flat_novarchar

Primary key: lease_id.

Table: MonthlyRent

ColumnType
lease_idint
rent_monthdate
paid_ondate

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_idtenant_nameflat_no
1Rahul SharmaA-302
2Zara KhanB-1204
3John MehtaC-701
4Emma BoseD-105

MonthlyRent

lease_idrent_monthpaid_on
12024-10-012024-10-03
12024-11-012024-11-09
12024-12-01NULL
12025-01-012025-01-20
12025-02-012025-02-06
42024-11-01NULL
42024-12-01NULL
42025-01-012025-01-10
22024-11-012024-11-12
22024-12-012024-12-15
22025-02-012025-02-10
22025-03-012025-03-04
32024-12-012024-12-06
32025-01-012025-01-08
32025-02-012025-02-07

Output

lease_idtenant_namestreak_startstreak_endmonths
1Rahul Sharma2024-11-012025-01-013
4Emma Bose2024-11-012025-01-013

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_start

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