Average Daily Rate by Hotel and Month

Medium
532.

Average Daily Rate by Hotel and Month

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

ColumnType
hotel_idint
hotel_namevarchar

Primary key: hotel_id.

One row per hotel; names are unique.

Table: Stay

ColumnTypeValues
stay_idint
hotel_idint
check_indate
check_outdate
nightly_rateint
statusenumchecked_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.

Example

The rows must come back in the order shown.

Hotel

hotel_idhotel_name
1Pink City Palace
2Backwater Retreat

Stay

stay_idhotel_idcheck_incheck_outnightly_ratestatus
112024-10-032024-10-064000checked_out
212024-10-202024-10-218000checked_out
312024-10-252024-10-276000cancelled
412024-11-302024-12-025200checked_out
522024-11-022024-11-057600checked_out
622024-11-152024-11-176400checked_out
722024-12-242024-12-269200no_show

Output

hotel_namestay_monthroom_nightsadr
Backwater Retreat2024-1157120
Pink City Palace2024-1045000
Pink City Palace2024-1125200
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
hotel_idint
hotel_namevarchar

Primary key: hotel_id.

One row per hotel; names are unique.

Table: Stay

ColumnType
stay_idint
hotel_idint
check_indate
check_outdate
nightly_rateint
statusenum(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_idhotel_name
1Pink City Palace
2Backwater Retreat

Stay

stay_idhotel_idcheck_incheck_outnightly_ratestatus
112024-10-032024-10-064000checked_out
212024-10-202024-10-218000checked_out
312024-10-252024-10-276000cancelled
412024-11-302024-12-025200checked_out
522024-11-022024-11-057600checked_out
622024-11-152024-11-176400checked_out
722024-12-242024-12-269200no_show

Output

hotel_namestay_monthroom_nightsadr
Backwater Retreat2024-1157120
Pink City Palace2024-1045000
Pink City Palace2024-1125200

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_month

Another 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 →