Most Booked Origin-Destination Route
Reported by candidates from Airbnb's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The mistake that sinks a first attempt on this Airbnb OA question is writing LIMIT 1 and calling it done. The question was reported in February 2022, and the catch is right in the prompt: return every route tied for the maximum. It's a SQL problem, a join from users to user_booking, grouped by origin and destination, with a tie-aware filter on top. If the query editor goes blank on you mid-assessment, StealthCoder is the invisible safety net that reads the problem and hands you a working query. Know the shape first and you probably won't need it.
The problem
Two tables describe users and their listing reservations: users stores each user's home city in origin. user_booking stores one row per reservation and the booked listing's city in destination. 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. Tables users: guest_id PK (Integer), origin (Text) user_booking: reservation_id PK (Integer), guest_id (Integer), listing_id (Integer), destination (Text), ts_booking (Timestamp), ds_booking (Date) Constraints users.guest_id and user_booking.reservation_id are unique and non-null. Every user_booking.guest_id references one row in users. origin and destination are non-null city names. The input tables may be empty. Each reservation row contributes exactly one booking to its route.
Reported by candidates. Source: FastPrep
Pattern and pitfall
Join user_booking to users on guest_id, then GROUP BY origin, destination and COUNT(*) as booking_count. Every reservation row counts once, so COUNT(*) is correct. The trick is the tie. LIMIT 1 drops tied routes and fails hidden tests. Use a HAVING clause comparing the count to the max of the grouped counts, either via a CTE with a MAX subquery or RANK() OVER (ORDER BY booking_count DESC) filtered to rank 1. Both work. Then ORDER BY origin ASC, destination ASC. Pitfalls: grouping by guest_id by accident, using COUNT(DISTINCT guest_id) and undercounting repeat bookers, and forgetting the empty-table case. With empty tables your query should return zero rows, and the CTE approach does that cleanly. If you blank on the tie logic during the live OA, StealthCoder is the hedge that gives you the CTE version.
StealthCoder is the hedge for the one pattern you didn't drill. It runs invisibly during the screen share.
You can drill Most Booked Origin-Destination Route cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. If you're reading this with an OA window open, you're who this was built for.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Airbnb's OA.
Airbnb reuses patterns across OAs. If you're reading this with an OA window open, you're who this was built for. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Most Booked Origin-Destination Route FAQ
What's the trick in the Airbnb most booked route question?+
Handle ties. The prompt says return every route tied for the maximum, so LIMIT 1 fails. Compute counts per origin and destination in a CTE, then filter where the count equals the maximum count. RANK() = 1 also works. Finish with the ordering by origin then destination.
Should I use COUNT(*) or COUNT(DISTINCT guest_id)?+
COUNT(*). The problem says to count reservation rows, and each row is exactly one booking. DISTINCT on guest_id would collapse repeat bookings by the same user on the same route and give you wrong counts on the hidden tests.
RANK or a MAX subquery, which is safer?+
Both return ties correctly. RANK() with a filter on 1 reads cleanly. A HAVING COUNT(*) = (SELECT MAX(c) FROM cte) subquery works on any engine, including ones with weak window support. Pick whichever you can write without syntax errors under pressure.
What happens if the tables are empty?+
The constraints say the input may be empty. A grouped query over zero rows returns zero rows, and the MAX filter matches nothing, so you return an empty result. That's the expected output. Don't add COALESCE tricks or fake rows.
How do I prepare for this in 48 hours?+
Write the query three times from scratch. Once with a CTE and MAX, once with RANK, once with HAVING and a subquery. Practice the join on guest_id and the two-column ORDER BY. That covers the whole pattern, and it's the same shape as most top-N-with-ties SQL questions.