Hotel revenue management

Nakheel Hotels is a fictional group of seven boutique hotels: two in Riyadh (a luxury hotel in Olaya and a business hotel in the Diplomatic Quarter), and one each in Jeddah, Makkah, Madinah, Abha and Taif. The Taif hotel only opened in March 2025. The group's new revenue manager wants to set 2026 pricing and budgets and has asked for a proper revenue analysis of 2025, built from the reservation system rather than the finance summary.

Hotel revenue analysis has its own vocabulary and its own traps:

• Occupancy is room-nights sold divided by room-nights available.
• ADR (average daily rate) is room revenue divided by paid room-nights sold.
• RevPAR (revenue per available room) is room revenue divided by room-nights available. It equals occupancy × ADR and is the single best summary of how well a hotel uses its rooms.

The first trap is time. A booking is one row with a check-in and a check-out, but its revenue belongs to the nights it covers, and a stay from 29 May to 3 June earns money in two months. You will expand bookings into nights with generate_series so that every night lands in its own month.

The second trap is supply. Available rooms change: a hotel opened mid-year and two rooms went out of service. The rest of the project covers what the revenue manager has to decide: how much is lost to cancellations and no-shows, whether corporate rates really are cheaper, how seasonal each hotel is, how loyal guests are, and how much guests spend beyond the room.

All hotels, guests and amounts are fictional.