Interview Question Bank · generate_series per row · EXTRACT(ISODOW) · CASE in SUM
5. Share of room revenue from weekend nights
MediumHospitalityQuestion #211
Concept: generate_series per row · EXTRACT(ISODOW) · CASE in SUMshow
When each row covers a range of days, a question about particular days needs one row per day: CROSS JOIN generate_series(start, end - 1, INTERVAL '1 day') expands every row into its days, and a date range that ends on a checkout or return date stops the day before. EXTRACT(ISODOW ...) numbers Monday 1 to Sunday 7 (plain DOW makes Sunday 0). SUM(CASE WHEN ... THEN amount ELSE 0 END) gives 0 rather than NULL when no day matches.
Example (different data):
SELECT r.car_id, SUM(r.daily_price) AS revenue, SUM(CASE WHEN EXTRACT(ISODOW FROM d) IN (6, 7) THEN r.daily_price ELSE 0 END) AS sat_sun_revenue FROM rentals r CROSS JOIN generate_series(r.pickup_on, r.return_on - 1, INTERVAL '1 day') AS d GROUP BY r.car_id;
Your task
Tidewell Hotels prices Friday and Saturday nights separately and wants to know how much of each hotel's room revenue those nights bring in.
A completed stay (status 'stayed') earns its nightly_rate for each night from check_in up to the night before check_out. Weekend nights are Friday and Saturday nights. For each hotel, return hotel (name), room_revenue (all nights), weekend_revenue (Friday and Saturday nights, 0 if none) and weekend_share_pct (weekend_revenue as a % of room_revenue, rounded to 1 decimal). Sort by weekend_share_pct descending, then hotel.
Starting Postgres in your browser…
Checking your access…