SQL for Occupancy and Revenue | Dataford Interview Questions

Write a SQL query to calculate weekly occupancy, average daily rate, and revenue by property using booking and room tables.

Use the week beginning 2025-01-06 through 2025-01-12. Include all properties, including those with no qualifying occupied nights. Treat confirmed, checked-in, and completed bookings as occupied, and exclude cancelled bookings and stays outside the week.

Output

One row per property for the specified week
Columns: property_name, week_start, occupancy_pct, average_daily_rate, revenue
Occupancy is occupied room nights divided by available room nights, expressed as a percentage; order by property_name ascending

Schema

rooms

Column	Type	Description
room_idPK	INT	Unique room identifier
property_id	INT	Property identifier
property_name	VARCHAR(100)	Property name
room_number	VARCHAR(10)	Room number or label

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
room_id	INT	Booked room identifier
check_in	DATE	Arrival date
check_out	DATE	Departure date
nightly_rate	NUMERIC(10,2)	Booked nightly room rate
status	VARCHAR(20)	Booking status

Your question is SQL for Occupancy and Revenue. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem