Average Nightly Rate Earned per Room

Problem

Table: `RateCards`

```text
+------------+------------+------------+-------+
| Column | Type | ... |
+------------+------------+------------+-------+
| room_id | int | |
| from_date | date | period start |
| to_date | date | period end |
| rate | int | nightly rate |
+------------+------------+------------+-------+
(room_id, from_date, to_date) is the primary key. For one room, no two periods
overlap. Each row sets the nightly rate for the room from from_date to to_date,
inclusive.
```

Table: `Bookings`

```text
+------------+------------+-------+
| Column | Type | ... |
+------------+------------+-------+
| room_id | int | |
| stay_date | date | night booked |
| nights | int | nights sold |
+------------+------------+-------+
This table may contain duplicate rows. Each row records nights of the room
sold starting on stay_date.
```

For each room in `RateCards`, compute the weighted average nightly rate actually earned, weighting each booking by its `nights`, rounded to 2 decimals. A room with no bookings has an average of 0.

Return `room_id` and `avg_rate`.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns room_id, avg_rate.

Starter code (SQL)

SELECT *
FROM RateCards;

Related SQL questions

• Above-Average CampusesEasy
• Adjacent Open Parking SpotsEasy
• App Ad Click-Through RateEasy
• Asset Labels With a Three-Digit CodeEasy
• Average Listening Sessions per ListenerEasy
• Average Reviewer Tenure per GrantEasy