City Revenue Dashboard
Return city, total_revenue, and paid_bookings for each city with payments. Order by total_revenue descending, then city.
- Joins
- Aggregation
- Sorting
- Distinct values
Challenge brief
Understand the request
Finance — City P&L Finance builds a city-level P&L each month. They need both revenue and transaction volume per market.
Show total paid revenue and number of paid bookings for each city.
Return
- city
- total_revenue
- paid_bookings
Constraints
- Sum every matched payment transaction by city
- Count distinct paid bookings
- 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
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Payments reach cities through bookings and listings.
Hint 2
Revenue counts transactions while paid-booking volume uses booking grain.
Hint 3
Use city to resolve equal revenue.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT l.city, SUM(p.amount) AS total_revenue, COUNT(DISTINCT b.booking_id) AS paid_bookings 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 ORDER BY total_revenue DESC, l.cityWhy this works
INNER JOIN to payments naturally excludes the 3 unpaid bookings. SUM(amount) gives revenue; COUNT(DISTINCT booking_id) gives transaction volume. Both metrics are needed for the city P&L view.
Success check
Returns stable city-level payment revenue and distinct paid-booking counts
Expected result
Use this output to verify values, aliases, ordering, and row count.
| city | total_revenue | paid_bookings |
|---|---|---|
| New York | 800 | 2 |
| San Francisco | 800 | 2 |
| Paris | 640 | 1 |
| Toronto | 560 | 1 |
| London | 360 | 1 |
| Bangalore | 300 | 1 |
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.