Average Nightly Rate Earned per Room

PandasEasyJunior level~10 min

Problem

You are given two DataFrames.

`ratecards` has columns `room_id`, `from_date`, `to_date`, and `rate`. Each row sets the nightly `rate` for a room over an inclusive date period `from_date`..`to_date`. For one room, no two periods overlap.

`bookings` has columns `room_id`, `stay_date`, and `nights`. Each row records `nights` of a room sold starting on `stay_date`. This DataFrame may contain duplicate rows.

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`.

Input data

Example rows — the live problem includes the full dataset.

ratecards

room_id	from_date	to_date	rate
1	2021-02-17	2021-02-28	5
1	2021-03-01	2021-03-22	20
2	2021-02-01	2021-02-20	15
2	2021-02-21	2021-03-31	30

bookings

room_id	stay_date	nights
1	2021-02-25	100
1	2021-03-01	15
2	2021-02-10	200
2	2021-03-22	30

Expected output

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

Starter code (Pandas (Python))

import pandas as pd

def average_nightly_rate(ratecards, bookings) -> pd.DataFrame:
    # Your code here
    return ratecards

Solve this Pandas question free

Write Pandas (Python) and run it instantly in your browser — even on your phone. No signup needed to try.

Solve it now → Create free account

Solution & explanation

Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.