Hotel Rooms Priced Above the Average Tariff — SQL Problem
A booking aggregator lists the rack tariff of every room type it sells.
- Difficulty: Easy
- Topics: Subqueries
- Dialect: MySQL
- Problem: #529
Problem statement
A booking aggregator lists the rack tariff of every room type it sells. The pricing team wants the rooms whose tariff is strictly above the average tariff of all rooms in the table.
Return room_id, hotel_name, room_type and tariff for those rooms, in any order. A room exactly at the average is not included.
Tables
Table: Room
| Column | Type |
|---|---|
| room_id | int |
| hotel_name | varchar |
| room_type | varchar |
| tariff | int |
Primary key: room_id.
One row per room type the aggregator sells; tariff is the nightly rack rate in rupees.
Examples
Example 1
Room
| room_id | hotel_name | room_type | tariff |
|---|---|---|---|
| 1 | Seaview Residency | Deluxe | 6500 |
| 2 | Seaview Residency | Suite | 14000 |
| 3 | Lakeside Haveli | Standard | 4000 |
| 4 | Lakeside Haveli | Suite | 9500 |
| 5 | Pine Ridge Lodge | Family | 8000 |
Output
| room_id | hotel_name | room_type | tariff |
|---|---|---|---|
| 2 | Seaview Residency | Suite | 14000 |
| 4 | Lakeside Haveli | Suite | 9500 |
How to solve Hotel Rooms Priced Above the Average Tariff
WHERE tariff > AVG(tariff) is not allowed: WHERE filters single rows before any aggregate exists. The average has to be computed separately and then compared against, and a scalar subquery does exactly that — (SELECT AVG(tariff) FROM Room) returns one value, which every row is compared with.
The comparison is strict, so a room priced exactly at the average stays out. The tariffs are whole rupees and the average is an exact decimal in both MySQL and the judge, so the equality case behaves the same everywhere.
The same value can be joined on as a one-row derived table (CROSS JOIN), or attached to every row with the window AVG(tariff) OVER () and filtered in an outer query. The subquery is not correlated, so the database computes it once: two passes over the table in total. On an empty table the average is NULL and nothing is returned.
Reference solution (MySQL)
SELECT room_id, hotel_name, room_type, tariff
FROM Room
WHERE tariff > (SELECT AVG(tariff) FROM Room)Another way
SELECT r.room_id, r.hotel_name, r.room_type, r.tariff FROM Room r CROSS JOIN (SELECT AVG(tariff) AS avg_tariff FROM Room) a WHERE r.tariff > a.avg_tariffAnother way
SELECT room_id, hotel_name, room_type, tariff FROM (SELECT r.*, AVG(tariff) OVER () AS avg_tariff FROM Room r) x WHERE tariff > avg_tariff← Hotel Loyalty Members per Email Domain · Loyalty Tier Headcount From Points Balance →