Cancellation rate by room type
mediumCancellation rate by room type
Airbnb SQL Interview Question
Airbnb's trust team suspects that some kinds of stays get canceled more often than others. Every booking is for a listing, and each listing is an entire home, a private room or a shared room.
For each room_type, find the percentage of bookings that were canceled, as cancellation_rate_pct, rounded to 1 decimal place. Use all of that room type's bookings as the total.
Asked of
- Data Analyst
- BI Analyst
- Product Analyst
- Data Scientist
bookingsTable16 rows
| Column Name | Type |
|---|---|
| booking_id | BIGINT |
| listing_id | BIGINT |
| guest_id | BIGINT |
| check_in | DATE |
| check_out | DATE |
| status | VARCHAR |
listingsTable10 rows
| Column Name | Type |
|---|---|
| listing_id | BIGINT |
| host_id | BIGINT |
| city | VARCHAR |
| room_type | VARCHAR |
| price_per_night | BIGINT |
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 |
| 5009 | 107 | 209 | 2024-06-14 | 2024-06-16 | completed |
| 5010 | 101 | 210 | 2024-06-15 | 2024-06-18 | canceled |
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
| room_type | cancellation_rate_pct |
|---|---|
| Entire home | 33.3 |
| Private room | 33.3 |
| Shared room | 0 |
Explanation
Entire homes have 6 bookings in the example, and 2 of them were canceled, which is 33.3%. Private rooms have 1 cancellation out of 3 bookings, also 33.3%. The one shared room booking went ahead, so shared rooms have a rate of 0.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Airbnb
Difficulty
medium
Topic
conditional logic
Your query
Starting the SQL engine...