Occupied Flats on the First of Each Month — SQL Problem
A landlord's dashboard charts, for the first day of each month from January to June 2025, how many of its flats had a tenant that day.
- Difficulty: Hard
- Topics: Dates, Joins, Aggregation
- Dialect: MySQL
- Problem: #370
Problem statement
A landlord's dashboard charts, for the first day of each month from January to June 2025, how many of its flats had a tenant that day. A tenancy covers a day when start_date ≤ day ≤ end_date, or end_date is NULL (still running). Every one of the six months must appear, even when no flat was occupied.
Return month_start ('2025-01-01' … '2025-06-01'), occupied_flats and vacant_flats (flats in RentalFlat minus occupied), ordered by month_start.
Tables
Table: RentalFlat
| Column | Type |
|---|---|
| flat_no | varchar |
| locality | varchar |
| bhk | int |
Primary key: flat_no.
Table: FlatTenancy
| Column | Type |
|---|---|
| tenancy_id | int |
| flat_no | varchar |
| start_date | date |
| end_date | date |
Primary key: tenancy_id.
end_date is the tenant's last day, NULL while the tenancy runs. A flat's tenancies may touch but a flat is never counted twice on one day.
Examples
Example 1
RentalFlat
| flat_no | locality | bhk |
|---|---|---|
| BN-101 | Baner | 2 |
| BN-102 | Baner | 3 |
| WK-201 | Wakad | 1 |
| KH-305 | Kharadi | 2 |
FlatTenancy
| tenancy_id | flat_no | start_date | end_date |
|---|---|---|---|
| 1 | BN-101 | 2024-06-01 | 2025-02-28 |
| 2 | BN-101 | 2025-04-01 | NULL |
| 3 | BN-102 | 2025-01-01 | 2025-03-01 |
| 4 | WK-201 | 2025-02-15 | 2025-05-31 |
| 5 | BN-102 | 2025-03-01 | 2025-04-30 |
Output
| month_start | occupied_flats | vacant_flats |
|---|---|---|
| 2025-01-01 | 2 | 2 |
| 2025-02-01 | 2 | 2 |
| 2025-03-01 | 2 | 2 |
| 2025-04-01 | 3 | 1 |
| 2025-05-01 | 2 | 2 |
| 2025-06-01 | 1 | 3 |
How to solve Occupied Flats on the First of Each Month
The output's rows are dates that may not appear in any table, so the query must make them. A recursive CTE starts at 2025-01-01 and adds a month with DATE_ADD(…, INTERVAL 1 MONTH) until June — six rows, bounded by the WHERE in the recursive step. Six literal rows joined by UNION ALL work as well for a fixed range.
LEFT JOIN FlatTenancy to the months with the covering condition — started on or before the day, and ending on or after it or not at all — in the ON clause, so a month with no tenant still yields one row with NULLs, and COUNT(DISTINCT t.flat_no) counts 0 there. The end date is inclusive: BN-102's first tenancy ends on 1 March and the next starts that day, and the flat is counted once, which is what DISTINCT is for. BN-101's tenancy ending 28 February leaves it vacant on 1 March.
Vacant flats are the total from RentalFlat minus the occupied count. The alternative counts flats with and without a covering tenancy using EXISTS per month.
Reference solution (MySQL)
WITH RECURSIVE months AS (
SELECT CAST('2025-01-01' AS DATE) AS month_start
UNION ALL
SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM months WHERE month_start < '2025-06-01'
)
SELECT m.month_start,
COUNT(DISTINCT t.flat_no) AS occupied_flats,
(SELECT COUNT(*) FROM RentalFlat) - COUNT(DISTINCT t.flat_no) AS vacant_flats
FROM months m
LEFT JOIN FlatTenancy t
ON t.start_date <= m.month_start
AND (t.end_date IS NULL OR t.end_date >= m.month_start)
GROUP BY m.month_start
ORDER BY m.month_startAnother way
WITH months AS (SELECT CAST('2025-01-01' AS DATE) AS month_start UNION ALL SELECT CAST('2025-02-01' AS DATE) UNION ALL SELECT CAST('2025-03-01' AS DATE) UNION ALL SELECT CAST('2025-04-01' AS DATE) UNION ALL SELECT CAST('2025-05-01' AS DATE) UNION ALL SELECT CAST('2025-06-01' AS DATE)) SELECT m.month_start, (SELECT COUNT(*) FROM RentalFlat f WHERE EXISTS (SELECT 1 FROM FlatTenancy t WHERE t.flat_no = f.flat_no AND t.start_date <= m.month_start AND COALESCE(t.end_date, '9999-12-31') >= m.month_start)) AS occupied_flats, (SELECT COUNT(*) FROM RentalFlat f WHERE NOT EXISTS (SELECT 1 FROM FlatTenancy t WHERE t.flat_no = f.flat_no AND t.start_date <= m.month_start AND COALESCE(t.end_date, '9999-12-31') >= m.month_start)) AS vacant_flats FROM months m ORDER BY m.month_start← Double-Booked Beds in a Co-Living Space · Top Two Agents per City by Commission, Ties Included →