Co-Living Bed Occupancy by Property

Medium
363.

Co-Living Bed Occupancy by Property

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

ColumnType
property_idint
property_namevarchar
cityvarchar

Primary key: property_id.

Property names are unique.

Table: Bed

ColumnTypeValues
bed_idint
property_idint
room_typeenumsingle, twin, triple

Primary key: bed_id.

Table: Stay

ColumnType
stay_idint
bed_idint
resident_namevarchar
check_indate
check_outdate

Primary key: stay_id.

Stays on one bed never overlap. check_out is NULL while the resident still lives there.

Example

The rows must come back in the order shown.

CoLivingProperty

property_idproperty_namecity
1Nest KoramangalaBengaluru
2Nest BanerPune
3Nest Hitech CityHyderabad

Bed

bed_idproperty_idroom_type
111single
121twin
131twin
141triple
212single
222twin

Stay

stay_idbed_idresident_namecheck_incheck_out
111Aisha Khan2025-01-05NULL
212Dev Iyer2025-03-012025-06-30
312Olivia Rao2025-06-30NULL
413Karan Nair2025-04-102025-07-15
514Sara Das2025-07-01NULL
621Noah Gupta2025-02-012025-05-31
722Ira Verma2025-05-012025-07-01

Output

property_namebedsoccupiedoccupancy_pct
Nest Baner2150
Nest Hitech City00NULL
Nest Koramangala4375
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.

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

ColumnType
property_idint
property_namevarchar
cityvarchar

Primary key: property_id.

Property names are unique.

Table: Bed

ColumnType
bed_idint
property_idint
room_typeenum(single, twin, triple)

Primary key: bed_id.

Table: Stay

ColumnType
stay_idint
bed_idint
resident_namevarchar
check_indate
check_outdate

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_idproperty_namecity
1Nest KoramangalaBengaluru
2Nest BanerPune
3Nest Hitech CityHyderabad

Bed

bed_idproperty_idroom_type
111single
121twin
131twin
141triple
212single
222twin

Stay

stay_idbed_idresident_namecheck_incheck_out
111Aisha Khan2025-01-05NULL
212Dev Iyer2025-03-012025-06-30
312Olivia Rao2025-06-30NULL
413Karan Nair2025-04-102025-07-15
514Sara Das2025-07-01NULL
621Noah Gupta2025-02-012025-05-31
722Ira Verma2025-05-012025-07-01

Output

property_namebedsoccupiedoccupancy_pct
Nest Baner2150
Nest Hitech City00NULL
Nest Koramangala4375

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_name

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