Average Stay Length by City
Return city and avg_nights (average nights per booking) for each city, ordered by avg_nights descending then city alphabetically.
- Joins
- Subqueries
- Aggregation
- Numeric functions
- Sorting
Challenge brief
Understand the request
Supply — Availability Optimisation The supply team uses average stay length per city to recommend minimum-night settings to hosts, maximising their occupancy and revenue.
Calculate the average number of nights booked per stay in each city.
Return
- city
- avg_nights
Constraints
- Average stay nights at city grain
- Round averages to two decimal places
- Order by average nights descending, then city
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
bookings
listing_idINTEGERnightsINTEGER
listings
listing_idINTEGERcityVARCHAR(50)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A booking reaches its city through the listing catalog.
Hint 2
Average nights after grouping by city.
Hint 3
Use city to resolve equal averages.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH city_stays AS (
SELECT l.city, ROUND(AVG(b.nights), 2) AS avg_nights
FROM bookings b
JOIN listings l ON b.listing_id = l.listing_id
GROUP BY l.city
)
SELECT city, avg_nights
FROM city_stays
ORDER BY avg_nights DESC, cityWhy this works
The CTE groups all bookings by city (via listings) and computes the average nights per stay. The outer query applies the required ordering — avg_nights descending with city as the tie-breaker.
Success check
Returns one stable row per booked city with its average stay length
Expected result
Use this output to verify values, aliases, ordering, and row count.
| city | avg_nights |
|---|---|
| Bangalore | 5 |
| Paris | 4 |
| Toronto | 4 |
| New York | 3 |
| London | 2 |
| San Francisco | 2 |
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.