Hotel Rooms Priced Above the Average Tariff

Easy
529.

Hotel Rooms Priced Above the Average Tariff

EasySubqueries

MySQL

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

ColumnType
room_idint
hotel_namevarchar
room_typevarchar
tariffint

Primary key: room_id.

One row per room type the aggregator sells; tariff is the nightly rack rate in rupees.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Room

room_idhotel_nameroom_typetariff
1Seaview ResidencyDeluxe6500
2Seaview ResidencySuite14000
3Lakeside HaveliStandard4000
4Lakeside HaveliSuite9500
5Pine Ridge LodgeFamily8000

Output

room_idhotel_nameroom_typetariff
2Seaview ResidencySuite14000
4Lakeside HaveliSuite9500
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.

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

ColumnType
room_idint
hotel_namevarchar
room_typevarchar
tariffint

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_idhotel_nameroom_typetariff
1Seaview ResidencyDeluxe6500
2Seaview ResidencySuite14000
3Lakeside HaveliStandard4000
4Lakeside HaveliSuite9500
5Pine Ridge LodgeFamily8000

Output

room_idhotel_nameroom_typetariff
2Seaview ResidencySuite14000
4Lakeside HaveliSuite9500

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_tariff

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