Average Rating per Listing
Return listing_id, city, and avg_rating rounded to 2 decimal places for each reviewed listing. Order by avg_rating descending, then listing_id.
- Joins
- Aggregation
- Numeric functions
- Sorting
Challenge brief
Understand the request
Quality — Host Performance The quality team monitors average ratings per listing to identify properties at risk of losing Superhost status.
Calculate the average guest rating for each listing.
Return
- listing_id
- city
- avg_rating
Constraints
- Include listings represented by review facts
- Average every matching rating at listing grain
- Order by average rating descending, then listing ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
bookings
booking_idINTEGERlisting_idINTEGER
listings
listing_idINTEGERcityVARCHAR(50)
reviews
booking_idINTEGERratingINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A review reaches its listing through the related booking.
Hint 2
Aggregate ratings at listing and city grain.
Hint 3
Use listing ID to resolve equal averages.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT b.listing_id, l.city, ROUND(AVG(r.rating), 2) AS avg_rating FROM bookings b JOIN listings l ON b.listing_id = l.listing_id JOIN reviews r ON b.booking_id = r.booking_id GROUP BY b.listing_id, l.city ORDER BY avg_rating DESC, b.listing_idWhy this works
rating lives in reviews (linked via booking_id); city lives in listings (linked via listing_id). Both JOINs are needed to bring all three together. GROUP BY listing and ROUND(AVG) gives the quality metric per property.
Success check
Returns one stable row per reviewed listing with its two-decimal average rating
Expected result
Use this output to verify values, aliases, ordering, and row count.
| listing_id | city | avg_rating |
|---|---|---|
| 3 | London | 5 |
| 6 | Paris | 5 |
| 5 | San Francisco | 4.5 |
| 1 | New York | 4 |
| 2 | Bangalore | 4 |
| 4 | Toronto | 4 |
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.