Vacancy Gaps Between Tenants of a Flat — SQL Problem
A landlord company wants to know which flats sat empty for long between one tenant moving out and the next moving in. A flat's leases never overlap.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #358
Problem statement
A landlord company wants to know which flats sat empty for long between one tenant moving out and the next moving in. A flat's leases never overlap. The vacant days before a lease are the days strictly between the previous lease's end_date and this lease's start_date — a lease starting the day after the last one ended has 0 vacant days.
Return flat_no, lease_id (the lease that ended the vacancy) and vacant_days for every gap of more than 30 days, ordered by vacant_days descending, then flat_no, then lease_id. A flat's first lease has no gap before it.
Tables
Table: FlatLease
| Column | Type |
|---|---|
| lease_id | int |
| flat_no | varchar |
| tenant_name | varchar |
| start_date | date |
| end_date | date |
Primary key: lease_id.
end_date is the tenant's last day in the flat. Leases of one flat never overlap.
Examples
Example 1
FlatLease
| lease_id | flat_no | tenant_name | start_date | end_date |
|---|---|---|---|---|
| 1 | A-302 | Aarav Shah | 2023-04-01 | 2024-03-31 |
| 2 | A-302 | Diya Rao | 2024-04-01 | 2024-09-30 |
| 3 | A-302 | Harsh Mehta | 2024-12-15 | 2025-06-14 |
| 4 | B-1204 | Tanvi Iyer | 2023-06-01 | 2024-05-31 |
| 5 | B-1204 | Liam Das | 2024-07-01 | 2025-06-30 |
| 6 | C-701 | Ira Nair | 2024-01-10 | 2024-12-09 |
| 7 | C-701 | Kabir Singh | 2025-01-10 | 2025-12-09 |
| 8 | D-105 | Maria Joshi | 2024-02-01 | 2025-01-31 |
Output
| flat_no | lease_id | vacant_days |
|---|---|---|
| A-302 | 3 | 75 |
| C-701 | 7 | 31 |
How to solve Vacancy Gaps Between Tenants of a Flat
The gap belongs to a pair of consecutive leases of the same flat, so each lease needs its predecessor's end_date. LAG(end_date) OVER (PARTITION BY flat_no ORDER BY start_date) fetches exactly that: the partition keeps flats apart and the ordering by start date makes "previous" mean the lease before in time. A flat's first lease gets NULL, so its vacant_days is NULL and > 30 filters it out.
Be careful with the off-by-one: a lease ending on 30 September and the next starting on 1 October leave no empty day, and DATEDIFF returns 1 — hence the - 1. In the example, C-701 ended on 9 December and was let again on 10 January: 31 days between, 30 vacant, so it is not listed.
Without window functions, the previous end date is MAX(end_date) among the flat's leases ending before this start (a correlated subquery), or a self join with a NOT EXISTS that no lease sits between the two. Both are quadratic per flat; the window is one sort.
Reference solution (MySQL)
SELECT flat_no, lease_id, vacant_days
FROM (
SELECT flat_no, lease_id,
DATEDIFF(start_date, LAG(end_date) OVER (PARTITION BY flat_no ORDER BY start_date)) - 1 AS vacant_days
FROM FlatLease
) g
WHERE vacant_days > 30
ORDER BY vacant_days DESC, flat_no, lease_idAnother way
SELECT flat_no, lease_id, vacant_days FROM (SELECT l.flat_no, l.lease_id, DATEDIFF(l.start_date, (SELECT MAX(p.end_date) FROM FlatLease p WHERE p.flat_no = l.flat_no AND p.end_date < l.start_date)) - 1 AS vacant_days FROM FlatLease l) t WHERE vacant_days > 30 ORDER BY vacant_days DESC, flat_no, lease_idAnother way
SELECT cur.flat_no, cur.lease_id, DATEDIFF(cur.start_date, prev.end_date) - 1 AS vacant_days FROM FlatLease cur JOIN FlatLease prev ON prev.flat_no = cur.flat_no AND prev.end_date < cur.start_date WHERE NOT EXISTS (SELECT 1 FROM FlatLease mid WHERE mid.flat_no = cur.flat_no AND mid.end_date > prev.end_date AND mid.end_date < cur.start_date) AND DATEDIFF(cur.start_date, prev.end_date) - 1 > 30 ORDER BY vacant_days DESC, cur.flat_no, cur.lease_id← Broker Site-Visit Conversion Rate · Monthly Rent Collection: On Time, Late and Unpaid →