Co-Living Bed Occupancy by Property — SQL Joins Problem
A co-living operator reports occupancy at the end of the quarter, the night of 2025-06-30.
- Difficulty: Medium
- Topics: Joins, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #363
Problem statement
A co-living operator reports occupancy at the end of the quarter, the night of 2025-06-30. A bed is occupied that night when some stay on it has check_in on or before 2025-06-30 and either no check_out yet or a check_out after 2025-06-30 (the check-out day itself is not a night stayed).
Return every property with property_name, beds, occupied and occupancy_pct = 100 × occupied ÷ beds rounded to 1 decimal (NULL for a property with no beds yet). Order by property_name.
Tables
Table: CoLivingProperty
| Column | Type |
|---|---|
| property_id | int |
| property_name | varchar |
| city | varchar |
Primary key: property_id.
Property names are unique.
Table: Bed
| Column | Type |
|---|---|
| bed_id | int |
| property_id | int |
| room_type | enum(single, twin, triple) |
Primary key: bed_id.
Table: Stay
| Column | Type |
|---|---|
| stay_id | int |
| bed_id | int |
| resident_name | varchar |
| check_in | date |
| check_out | date |
Primary key: stay_id.
Stays on one bed never overlap. check_out is NULL while the resident still lives there.
Examples
Example 1
CoLivingProperty
| property_id | property_name | city |
|---|---|---|
| 1 | Nest Koramangala | Bengaluru |
| 2 | Nest Baner | Pune |
| 3 | Nest Hitech City | Hyderabad |
Bed
| bed_id | property_id | room_type |
|---|---|---|
| 11 | 1 | single |
| 12 | 1 | twin |
| 13 | 1 | twin |
| 14 | 1 | triple |
| 21 | 2 | single |
| 22 | 2 | twin |
Stay
| stay_id | bed_id | resident_name | check_in | check_out |
|---|---|---|---|---|
| 1 | 11 | Aisha Khan | 2025-01-05 | NULL |
| 2 | 12 | Dev Iyer | 2025-03-01 | 2025-06-30 |
| 3 | 12 | Olivia Rao | 2025-06-30 | NULL |
| 4 | 13 | Karan Nair | 2025-04-10 | 2025-07-15 |
| 5 | 14 | Sara Das | 2025-07-01 | NULL |
| 6 | 21 | Noah Gupta | 2025-02-01 | 2025-05-31 |
| 7 | 22 | Ira Verma | 2025-05-01 | 2025-07-01 |
Output
| property_name | beds | occupied | occupancy_pct |
|---|---|---|---|
| Nest Baner | 2 | 1 | 50 |
| Nest Hitech City | 0 | 0 | NULL |
| Nest Koramangala | 4 | 3 | 75 |
How to solve Co-Living Bed Occupancy by Property
Every property appears, so the query starts at CoLivingProperty and LEFT JOINs Bed, then LEFT JOINs Stay with the night-of test in the ON clause: checked in on or before the 30th, and either still there or checking out after it. Beds with no covering stay stay in the result with NULL stay columns — they are the unoccupied beds. Putting the date test in WHERE would delete them and every property would look full.
The boundary decides two beds in the example: Dev checks out on the 30th and Olivia checks in the same day — the bed is occupied that night by Olivia, once. Counting DISTINCT bed ids keeps a bed from counting twice if two stays ever matched. Noah left in May, Ira is still in on the 30th.
NULLIF(beds, 0) turns the percentage of a property without beds into NULL instead of a division error. The alternative computes an occupied flag per bed with EXISTS and sums it.
Reference solution (MySQL)
SELECT p.property_name,
COUNT(DISTINCT b.bed_id) AS beds,
COUNT(DISTINCT CASE WHEN s.stay_id IS NOT NULL THEN b.bed_id END) AS occupied,
ROUND(100 * COUNT(DISTINCT CASE WHEN s.stay_id IS NOT NULL THEN b.bed_id END) / NULLIF(COUNT(DISTINCT b.bed_id), 0), 1) AS occupancy_pct
FROM CoLivingProperty p
LEFT JOIN Bed b ON b.property_id = p.property_id
LEFT JOIN Stay s ON s.bed_id = b.bed_id
AND s.check_in <= '2025-06-30'
AND (s.check_out IS NULL OR s.check_out > '2025-06-30')
GROUP BY p.property_id, p.property_name
ORDER BY p.property_nameAnother way
SELECT p.property_name, COUNT(b.bed_id) AS beds, COALESCE(SUM(b.occ), 0) AS occupied, ROUND(100 * SUM(b.occ) / NULLIF(COUNT(b.bed_id), 0), 1) AS occupancy_pct FROM CoLivingProperty p LEFT JOIN (SELECT bed_id, property_id, CASE WHEN EXISTS (SELECT 1 FROM Stay s WHERE s.bed_id = bd.bed_id AND s.check_in <= '2025-06-30' AND COALESCE(s.check_out, '9999-12-31') > '2025-06-30') THEN 1 ELSE 0 END AS occ FROM Bed bd) b ON b.property_id = p.property_id GROUP BY p.property_id, p.property_name ORDER BY p.property_name← Days From First Site Visit to Booking · Each Broker's Best Month by Deal Value →