Nights booked per city
mediumNights booked per city
Airbnb SQL Interview Question
Airbnb's city teams plan local marketing around demand, measured in nights booked. Every booking is for a listing, and every listing is in a city.
Find the total number of nights booked in each city as nights_booked, counting only completed bookings. A stay from June 1 to June 5 is 4 nights. Sort the rows by nights_booked, highest first.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
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
| city | nights_booked |
|---|---|
| Paris | 10 |
| Tokyo | 9 |
| Lisbon | 4 |
Explanation
Paris has four completed bookings in the example, lasting 2, 2, 5 and 1 nights, which adds up to 10. One Tokyo booking was canceled, so only its 7-night and 2-night stays count, for a total of 9.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Airbnb
Difficulty
medium
Topic
dates
Your query
Starting the SQL engine...