Vacancy Gaps Between Tenants of a Flat

Medium
358.

Vacancy Gaps Between Tenants of a Flat

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

ColumnType
lease_idint
flat_novarchar
tenant_namevarchar
start_datedate
end_datedate

Primary key: lease_id.

end_date is the tenant's last day in the flat. Leases of one flat never overlap.

Example

The rows must come back in the order shown.

FlatLease

lease_idflat_notenant_namestart_dateend_date
1A-302Aarav Shah2023-04-012024-03-31
2A-302Diya Rao2024-04-012024-09-30
3A-302Harsh Mehta2024-12-152025-06-14
4B-1204Tanvi Iyer2023-06-012024-05-31
5B-1204Liam Das2024-07-012025-06-30
6C-701Ira Nair2024-01-102024-12-09
7C-701Kabir Singh2025-01-102025-12-09
8D-105Maria Joshi2024-02-012025-01-31

Output

flat_nolease_idvacant_days
A-302375
C-701731
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.

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

ColumnType
lease_idint
flat_novarchar
tenant_namevarchar
start_datedate
end_datedate

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_idflat_notenant_namestart_dateend_date
1A-302Aarav Shah2023-04-012024-03-31
2A-302Diya Rao2024-04-012024-09-30
3A-302Harsh Mehta2024-12-152025-06-14
4B-1204Tanvi Iyer2023-06-012024-05-31
5B-1204Liam Das2024-07-012025-06-30
6C-701Ira Nair2024-01-102024-12-09
7C-701Kabir Singh2025-01-102025-12-09
8D-105Maria Joshi2024-02-012025-01-31

Output

flat_nolease_idvacant_days
A-302375
C-701731

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_id

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

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