Hotel management to data analyst: occupancy, ADR and RevPAR

Start with the query

Here is the whole idea in one SQLite script. It uses a synthetic 10-room property over three nights, with bookings from three channels.

CREATE TABLE room_nights (
  stay_date TEXT,
  room_no INTEGER,
  channel TEXT,       -- 'direct', 'ota', 'corporate'
  room_revenue REAL   -- net room revenue for that night, in ₹
);

INSERT INTO room_nights VALUES
('2026-09-18',101,'direct',4200),('2026-09-18',102,'ota',3600),('2026-09-18',103,'ota',3500),
('2026-09-18',104,'corporate',3900),('2026-09-18',105,'corporate',3900),('2026-09-18',106,'direct',4500),
('2026-09-19',101,'ota',4100),('2026-09-19',102,'ota',3950),('2026-09-19',103,'direct',4800),
('2026-09-19',104,'direct',4800),('2026-09-19',105,'ota',3800),('2026-09-19',106,'ota',3800),
('2026-09-19',107,'corporate',3900),('2026-09-19',108,'direct',5000),('2026-09-19',109,'ota',3700),
('2026-09-20',101,'corporate',3900),('2026-09-20',102,'corporate',3900),('2026-09-20',103,'ota',3300),
('2026-09-20',104,'direct',3800);

CREATE TABLE inventory (stay_date TEXT, rooms_available INTEGER);
INSERT INTO inventory VALUES ('2026-09-18',10),('2026-09-19',10),('2026-09-20',9);

-- Daily occupancy, ADR and RevPAR
SELECT
  i.stay_date,
  COUNT(r.room_no)                                   AS rooms_sold,
  i.rooms_available,
  ROUND(100.0 * COUNT(r.room_no) / i.rooms_available, 1) AS occupancy_pct,
  ROUND(SUM(r.room_revenue) / COUNT(r.room_no), 0)   AS adr,
  ROUND(SUM(r.room_revenue) / i.rooms_available, 0)  AS revpar
FROM inventory i
LEFT JOIN room_nights r ON r.stay_date = i.stay_date
GROUP BY i.stay_date, i.rooms_available
ORDER BY i.stay_date;

-- Revenue per available room-night contributed by each channel, over the period
SELECT
  channel,
  COUNT(*) AS room_nights,
  ROUND(AVG(room_revenue), 0) AS channel_adr,
  ROUND(SUM(room_revenue) / (SELECT SUM(rooms_available) FROM inventory), 0) AS revpar_contribution
FROM room_nights
GROUP BY channel
ORDER BY revpar_contribution DESC;

What that query is really doing

The first result reproduces three figures every night auditor knows. Occupancy is rooms sold over rooms available. ADR is room revenue over rooms sold. RevPAR is room revenue over rooms available, which is also occupancy multiplied by ADR. The LEFT JOIN from inventory matters: if a night had zero sales, it should still appear with zero revenue, not vanish.

The third night shows why an analyst reads the inputs before the output. Available rooms drop to 9 — in a real property that might be a room out of order, and whether out-of-order rooms count as "available" changes RevPAR. Hotels and chains define this differently. Write down which definition you used; that sentence is the kind of detail an interviewer probes.

The second query splits the period's RevPAR by channel. On this synthetic data, OTA sells the most room-nights and contributes the most RevPAR, yet at the lowest ADR of the three channels, and the revenue column is assumed to be net of commission. A revenue manager's next question is not "which channel sells most?" but "would those OTA nights have sold direct anyway, at a higher rate?" — the displacement question. The data here cannot answer it; a comparison of high-demand and low-demand nights could start to. That is a question you already understand from working the desk; now you can frame it with numbers.

The artefacts from hotel work that carry over

• Night audit report and manager's flash — the daily source of occupancy, ADR, RevPAR, and the reconciliation habit that comes with closing the day.
• PMS reports — arrivals, departures, no-shows, cancellations, length of stay. Each is an analysable column in a booking table.
• Rate grid and restrictions — minimum length of stay, closed-to-arrival dates. These explain odd patterns in the data.
• OTA extranet reports and channel manager logs — commission, pickup, cancellation timing.
• F&B covers and banquet function sheets — a second revenue stream that most analyst candidates cannot read at all.

The first portfolio project

Extend the query into a small project on a synthetic 90-day booking file: booking pace (how many room-nights for a future date were on the books 30, 14 and 7 days out), cancellation rate by channel and lead time, and a one-page Power BI report. The finding should be a decision — for example, whether a non-refundable rate on long-lead OTA bookings would reduce late cancellations, stated as a hypothesis to test, not a proven result.

Gaps hospitality training leaves

Hotel management courses teach operations and service, not joins, window functions, or statistical caution. Specifically, build:

1. 1SQL beyond GROUP BY — date functions, window functions for booking pace, and joins across bookings, rates and inventory.
2. 2A dashboard tool used with restraint.
3. 3Basic Python with pandas for cleaning messy exports.
4. 4The discipline to state definitions and sample sizes, especially with small properties where one group booking swings a month.

Revenue analyst versus general data analyst

A revenue analyst, reservations or distribution analyst, or MIS analyst in hospitality, travel or an online travel business uses your context immediately. A general junior data analyst role in consumer businesses is a reasonable second target once you add a non-hotel project. Check your resume against analyst skills with the free skill check.

Sample answer: "Why move from operations to data?"

"After three years at the front office and two as a night auditor, I realised the numbers I closed every night — occupancy, ADR, RevPAR — were only ever read as totals. I taught myself SQL and rebuilt a synthetic version of our night audit from room-night rows, then split RevPAR by booking channel. The OTA channel contributed the most revenue but at the lowest average rate, which raised the question of whether some of those nights would have sold direct on busy dates. I'd like to do that analysis full time, and I understand hotel data well enough to know where it misleads."

If you want guided practice in SQL, Power BI and verifying AI-assisted analysis, the Data Analyst programme is structured around deliverables of this kind.