Most Booked Origin-Destination Route
Airbnb● MediumFULLTIMEOAProblem statement
Two tables describe users and their listing reservations:
usersstores each user's home city inorigin.user_bookingstores one row per reservation and the booked listing's city indestination.
Find the origin-destination route or routes with the largest number of bookings. Count reservation rows, return every route tied for the maximum, and order tied routes by origin and then destination, both ascending.
Return the columns origin, destination, and booking_count.
Table schema
MySQLPostgreSQLPandas
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
users
Users and their home cities.
| Column | Type | Nullable | Description |
|---|---|---|---|
| guest_idPK | Integer | No | Unique user identifier. |
| origin | Text | No | The user's home city. |
user_booking
Reservations made by users for listings in destination cities.
| Column | Type | Nullable | Description |
|---|---|---|---|
| reservation_idPK | Integer | No | Unique reservation identifier. |
| guest_id | Integer | No | User who made the reservation. |
| listing_id | Integer | No | Booked listing identifier. |
| destination | Text | No | City of the booked listing. |
| ts_booking | Timestamp | No | Timestamp when the booking was made. |
| ds_booking | Date | No | Calendar date of the booking. |
Foreign key: guest_id → users(guest_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| origin | Text | No | — |
| destination | Text | No | — |
| booking_count | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
users.guest_idanduser_booking.reservation_idare unique and non-null.- Every
user_booking.guest_idreferences one row inusers. originanddestinationare non-null city names.- The input tables may be empty.
- Each reservation row contributes exactly one booking to its route.