Interview Question Bank · Aggregate before joining · clipping stays to a month
2. Host occupancy rate
HardMarketplaceQuestion #129
Concept: Aggregate before joining · clipping stays to a monthshow
Joining a one-row-per-listing table to many bookings repeats the listing's columns once per booking, so SUM(nights_available) after that join counts the same nights several times. Total the bookings per listing in a CTE first, then LEFT JOIN that single row per listing. To count only the nights inside a month, clip each stay with LEAST(check_out, next_month_start) - GREATEST(check_in, month_start), and keep only stays that overlap the month so the result never goes negative.
Example (different data):
SELECT room_id, SUM(LEAST(ends_on, DATE '2025-04-01') - GREATEST(starts_on, DATE '2025-03-01')) AS march_days FROM room_holds WHERE starts_on < DATE '2025-04-01' AND ends_on > DATE '2025-03-01' GROUP BY room_id;
Your task
Burrowstay's host success team ranks hosts by June 2025 occupancy: how many of the nights they opened up actually got booked.
For June 2025, return host_id, nights_available (total across the host's listings), nights_booked (nights of confirmed bookings that fall in June; a stay covers the nights from check_in up to check_out - 1, and a stay that starts before June or ends after it counts only its June nights) and occupancy_pct (nights_booked as a percentage of nights_available, ROUND(..., 1)). Include every host, with 0 nights_booked when they had no June bookings. Sort by occupancy_pct highest first, then host_id.
Starting Postgres in your browser…
Checking your access…