Average Daily Rate by Hotel and Month — SQL Problem
ADR (average daily rate) is the room revenue divided by the room nights sold: a 3-night stay at 4000 and a 1-night stay at 8000 give (12000 + 8000) / 4 =…
- Difficulty: Medium
- Topics: Aggregation, Dates, Joins
- Dialect: MySQL
- Problem: #532
Problem statement
ADR (average daily rate) is the room revenue divided by the room nights sold: a 3-night stay at 4000 and a 1-night stay at 8000 give (12000 + 8000) / 4 = 5000, not the average of the two rates. Only stays with status checked_out count, and a stay belongs to the month of its check_in.
Return hotel_name, stay_month (as YYYY-MM), room_nights and adr (rounded to 2 decimals) for every hotel and month with at least one checked-out stay, ordered by hotel_name, then stay_month.
Tables
Table: Hotel
| Column | Type |
|---|---|
| hotel_id | int |
| hotel_name | varchar |
Primary key: hotel_id.
One row per hotel; names are unique.
Table: Stay
| Column | Type |
|---|---|
| stay_id | int |
| hotel_id | int |
| check_in | date |
| check_out | date |
| nightly_rate | int |
| status | enum(checked_out, cancelled, no_show) |
Primary key: stay_id.
One room per stay at one nightly_rate (rupees) for every night; check_out is after check_in.
Examples
Example 1
Hotel
| hotel_id | hotel_name |
|---|---|
| 1 | Pink City Palace |
| 2 | Backwater Retreat |
Stay
| stay_id | hotel_id | check_in | check_out | nightly_rate | status |
|---|---|---|---|---|---|
| 1 | 1 | 2024-10-03 | 2024-10-06 | 4000 | checked_out |
| 2 | 1 | 2024-10-20 | 2024-10-21 | 8000 | checked_out |
| 3 | 1 | 2024-10-25 | 2024-10-27 | 6000 | cancelled |
| 4 | 1 | 2024-11-30 | 2024-12-02 | 5200 | checked_out |
| 5 | 2 | 2024-11-02 | 2024-11-05 | 7600 | checked_out |
| 6 | 2 | 2024-11-15 | 2024-11-17 | 6400 | checked_out |
| 7 | 2 | 2024-12-24 | 2024-12-26 | 9200 | no_show |
Output
| hotel_name | stay_month | room_nights | adr |
|---|---|---|---|
| Backwater Retreat | 2024-11 | 5 | 7120 |
| Pink City Palace | 2024-10 | 4 | 5000 |
| Pink City Palace | 2024-11 | 2 | 5200 |
How to solve Average Daily Rate by Hotel and Month
The trap in ADR is averaging the wrong thing. AVG(nightly_rate) treats a one-night stay and a five-night stay as equally important; ADR weights each rate by the nights it was sold for. So each stay contributes nightly_rate * nights of revenue and nights of inventory, and the ratio is taken after summing both: SUM(rate * nights) / SUM(nights).
Nights come from DATEDIFF(check_out, check_in). The month bucket is DATE_FORMAT(check_in, '%Y-%m') — or LEFT(check_in, 7) on the stored text — and the query groups by hotel and that bucket after keeping only checked_out stays, so cancellations and no-shows add neither revenue nor nights. A hotel-month with no checked-out stay simply forms no group, so the division never sees a zero.
The ratio is rounded to two decimals because MySQL and other engines carry different precision on a division. The result is ordered by hotel name and month, which the statement fixes. The CTE version computes the nights once per stay and then aggregates; both are a single pass plus a sort of the groups.
Reference solution (MySQL)
SELECT h.hotel_name,
DATE_FORMAT(s.check_in, '%Y-%m') AS stay_month,
SUM(DATEDIFF(s.check_out, s.check_in)) AS room_nights,
ROUND(SUM(s.nightly_rate * DATEDIFF(s.check_out, s.check_in)) / SUM(DATEDIFF(s.check_out, s.check_in)), 2) AS adr
FROM Stay s
JOIN Hotel h ON h.hotel_id = s.hotel_id
WHERE s.status = 'checked_out'
GROUP BY h.hotel_name, DATE_FORMAT(s.check_in, '%Y-%m')
ORDER BY h.hotel_name, stay_monthAnother way
WITH n AS (
SELECT hotel_id, LEFT(check_in, 7) AS stay_month, DATEDIFF(check_out, check_in) AS nights, nightly_rate
FROM Stay WHERE status = 'checked_out'
)
SELECT h.hotel_name, n.stay_month, SUM(n.nights) AS room_nights, ROUND(SUM(n.nights * n.nightly_rate) / SUM(n.nights), 2) AS adr
FROM n JOIN Hotel h ON h.hotel_id = n.hotel_id
GROUP BY h.hotel_name, n.stay_month
ORDER BY h.hotel_name ASC, n.stay_month ASC← Hotel Occupancy on New Year's Eve · On-Time Arrival Performance by Airline →