Occupied Flats on the First of Each Month

Hard
370.

Occupied Flats on the First of Each Month

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

ColumnType
flat_novarchar
localityvarchar
bhkint

Primary key: flat_no.

Table: FlatTenancy

ColumnType
tenancy_idint
flat_novarchar
start_datedate
end_datedate

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.

Example

The rows must come back in the order shown.

RentalFlat

flat_nolocalitybhk
BN-101Baner2
BN-102Baner3
WK-201Wakad1
KH-305Kharadi2

FlatTenancy

tenancy_idflat_nostart_dateend_date
1BN-1012024-06-012025-02-28
2BN-1012025-04-01NULL
3BN-1022025-01-012025-03-01
4WK-2012025-02-152025-05-31
5BN-1022025-03-012025-04-30

Output

month_startoccupied_flatsvacant_flats
2025-01-0122
2025-02-0122
2025-03-0122
2025-04-0131
2025-05-0122
2025-06-0113
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.

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

ColumnType
flat_novarchar
localityvarchar
bhkint

Primary key: flat_no.

Table: FlatTenancy

ColumnType
tenancy_idint
flat_novarchar
start_datedate
end_datedate

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_nolocalitybhk
BN-101Baner2
BN-102Baner3
WK-201Wakad1
KH-305Kharadi2

FlatTenancy

tenancy_idflat_nostart_dateend_date
1BN-1012024-06-012025-02-28
2BN-1012025-04-01NULL
3BN-1022025-01-012025-03-01
4WK-2012025-02-152025-05-31
5BN-1022025-03-012025-04-30

Output

month_startoccupied_flatsvacant_flats
2025-01-0122
2025-02-0122
2025-03-0122
2025-04-0131
2025-05-0122
2025-06-0113

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_start

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