Bookings by City
Return city and booking_count for every catalog city, including cities with no bookings. Order by booking_count descending, then city.
- Joins
- Aggregation
- Sorting
Challenge brief
Understand the request
Market Expansion The market expansion team tracks demand by city to decide where to prioritise host acquisition efforts.
Measure booking demand for every city represented in the listing catalog.
Return
- city
- booking_count
Constraints
- Preserve every catalog city
- Count matched bookings and report zero for cities without demand
- Order by booking count descending, then city
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
bookings
listing_idINTEGER
listings
listing_idINTEGERcityVARCHAR(50)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Start from listing supply so cities without demand remain visible.
Hint 2
Optionally match bookings through listing ID.
Hint 3
Count a nullable booking key at city grain.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT l.city, COUNT(b.booking_id) AS booking_count FROM listings l LEFT JOIN bookings b ON l.listing_id = b.listing_id GROUP BY l.city ORDER BY booking_count DESC, l.cityWhy this works
city is in listings; each booking row is in bookings. JOIN on listing_id links them. GROUP BY city and COUNT(*) gives the demand per market.
Success check
Returns one row per catalog city with a zero-safe booking count
Expected result
Use this output to verify values, aliases, ordering, and row count.
| city | booking_count |
|---|---|
| New York | 4 |
| London | 2 |
| San Francisco | 2 |
| Bangalore | 1 |
| Paris | 1 |
| Toronto | 1 |
| Lisbon | 0 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Uber
Independent Uber-style mobility marketplace SQL practice covering trips, drivers, riders, pricing, payments, and promotions.
Amazon
Independent Amazon-style e-commerce, warehouse, inventory, and customer analytics SQL practice.
Meta
Independent Meta-style social-product, engagement, content, community, messaging, and advertising SQL practice.
Return to the complete interview preparation experience.