Drill 6 of 9 in Hospitality questions
0

Interview Question Bank · Nights via generate_series · days in month · two denominators

6. ADR and RevPAR per hotel per month

HardHospitalityQuestion #213

Concept: Nights via generate_series · days in month · two denominatorsshow

Hotel KPIs share a numerator, room revenue, and differ in the denominator: ADR (average daily rate) divides by room nights sold, RevPAR (revenue per available room) by room nights available, which is rooms × days in the month. A stay that crosses a month end belongs to two months, so expand stays into nights before grouping by month. Days in a month come from date arithmetic: (month_start + INTERVAL '1 month')::date - month_start.

Example (different data):

WITH days AS (
  SELECT r.lot_id, r.price, d::date AS day
  FROM parking_permits r
  CROSS JOIN generate_series(r.start_on, r.end_on - 1, INTERVAL '1 day') AS d
)
SELECT l.name, date_trunc('month', day)::date AS month,
  ROUND(SUM(price) / COUNT(*), 2) AS avg_paid_per_day,
  ROUND(SUM(price) / (l.spaces * ((date_trunc('month', day) + INTERVAL '1 month')::date - date_trunc('month', day)::date)), 2) AS revenue_per_space_day
FROM days JOIN lots l ON l.id = days.lot_id
GROUP BY l.name, l.spaces, date_trunc('month', day);

Your task

Tidewell Hotels' owners compare properties on ADR and RevPAR every month. Finance needs the first quarter of 2025, with each night counted in the month it was slept in.

Count each completed stay's nights (status 'stayed'; every night from check_in up to the night before check_out) in the month they fall in, keeping nights from 2025-01-01 to 2025-03-31. For each hotel and month with at least one night sold, return hotel (name), month (as 'YYYY-MM'), nights_sold, room_revenue (sum of nightly_rate), adr (room_revenue / nights_sold) and revpar (room_revenue / (rooms × days in that month)), both rounded to 2 decimals. Sort by hotel, then month.

Starting Postgres in your browser…

Checking your access…