City Performance Scorecard
Return city, total_revenue, and avg_rating for each city that has both payment and review facts. Order by total_revenue descending, then city.
- Joins
- Subqueries
- Aggregation
- Numeric functions
- Sorting
Challenge brief
Understand the request
Executive — Market Scorecard Executives review a combined revenue and quality scorecard per city each quarter to make market investment decisions.
Combine independently measured city revenue and review quality without cross-fact fanout.
Return
- city
- total_revenue
- avg_rating
Constraints
- Aggregate payment revenue by city independently
- Aggregate review ratings by city independently
- Combine the city-level measures after aggregation
- Order by revenue descending, then city
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)
payments
booking_idINTEGERamountINTEGER
reviews
booking_idINTEGERratingINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Revenue transactions and review facts have independent multiplicities.
Hint 2
Reduce each fact stream to city grain before combining them.
Hint 3
Join the two city summaries and use city as the revenue tie-break.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH city_revenue AS (SELECT l.city, SUM(p.amount) AS total_revenue FROM bookings b JOIN listings l ON b.listing_id = l.listing_id JOIN payments p ON b.booking_id = p.booking_id GROUP BY l.city), city_ratings AS (SELECT 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 l.city) SELECT cr.city, cr.total_revenue, ct.avg_rating FROM city_revenue cr JOIN city_ratings ct ON cr.city = ct.city ORDER BY cr.total_revenue DESC, cr.cityWhy this works
The CTE joins all four tables through bookings as the hub. GROUP BY city aggregates both SUM(payment amount) and AVG(review rating). Because only bookings 1–8 have payments and reviews, unpaid bookings 9–11 are excluded by the INNER JOINs.
Success check
Returns one row per eligible city without multiplying payments by reviews
Expected result
Use this output to verify values, aliases, ordering, and row count.
| city | total_revenue | avg_rating |
|---|---|---|
| New York | 800 | 4 |
| San Francisco | 800 | 4.5 |
| Paris | 640 | 5 |
| Toronto | 560 | 4 |
| London | 360 | 5 |
| Bangalore | 300 | 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.