Booked revenue by city
mediumBooked revenue by city
Airbnb Pandas Interview Question
Airbnb's city teams report how many nights were booked and how much those stays earned.
Using the bookings and listings DataFrames, and completed bookings only, return each city with the total nights booked as nights and the revenue as revenue. A stay's nights are the days between check-in and check-out, and its revenue is nights times the listing's nightly price. Sort the rows by revenue, highest first. Assign the answer to result.
Asked of
- Data Analyst
- Product Analyst
- Business Analyst
- Analytics Engineer
- Data Scientist
bookingsDataFrame16 rows
| Column Name | Type |
|---|---|
| booking_id | int64 |
| listing_id | int64 |
| guest_id | int64 |
| check_in | str |
| check_out | str |
| status | str |
listingsDataFrame10 rows
| Column Name | Type |
|---|---|
| listing_id | int64 |
| host_id | int64 |
| city | str |
| room_type | str |
| price_per_night | int64 |
bookingsExample Input
| booking_id | listing_id | guest_id | check_in | check_out | status |
|---|---|---|---|---|---|
| 5001 | 101 | 201 | 2024-06-01 | 2024-06-05 | completed |
| 5002 | 103 | 202 | 2024-06-02 | 2024-06-04 | completed |
| 5003 | 106 | 203 | 2024-06-03 | 2024-06-10 | completed |
| 5004 | 102 | 204 | 2024-06-05 | 2024-06-06 | canceled |
| 5005 | 104 | 205 | 2024-06-07 | 2024-06-09 | completed |
| 5006 | 109 | 206 | 2024-06-08 | 2024-06-11 | canceled |
| 5007 | 110 | 207 | 2024-06-10 | 2024-06-15 | completed |
| 5008 | 105 | 208 | 2024-06-12 | 2024-06-13 | completed |
listingsExample Input
| listing_id | host_id | city | room_type | price_per_night |
|---|---|---|---|---|
| 101 | 11 | Lisbon | Entire home | 120 |
| 102 | 11 | Lisbon | Private room | 55 |
| 103 | 12 | Paris | Entire home | 210 |
| 104 | 13 | Paris | Private room | 85 |
| 105 | 13 | Paris | Shared room | 40 |
| 106 | 14 | Tokyo | Entire home | 160 |
| 107 | 15 | Tokyo | Private room | 70 |
| 108 | 16 | Lisbon | Shared room | 30 |
| 109 | 17 | Tokyo | Entire home | 190 |
| 110 | 12 | Paris | Entire home | 240 |
Example Output
| city | nights | revenue |
|---|---|---|
| Paris | 10 | 1830 |
| Tokyo | 7 | 1120 |
| Lisbon | 4 | 480 |
Explanation
In the example, Paris had four completed stays: 2 nights at 210, 2 nights at 85, 5 nights at 240 and 1 night at 40, which adds up to 10 nights and 1,830 in revenue. A stay from June 1 to June 5 counts as 4 nights.
The example above is a small slice of the data. Your code runs against the full DataFrames.
Company
Airbnb
Difficulty
medium
Topic
dates
Language
Pandas
Your code
Loading Python in the background. You can start writing.