Revenue per Listing
Return listing_id, city, and total_revenue for each listing that has received payments. Order by total_revenue descending, then listing_id.
- Joins
- Aggregation
- Sorting
Challenge brief
Understand the request
Finance — Host Payouts Finance computes host payout amounts based on the total revenue each listing has collected from paid bookings.
Calculate total paid revenue generated by each listing.
Return
- listing_id
- city
- total_revenue
Constraints
- Include only listings with matched booking payments
- Sum all payment transactions at listing grain
- Order by revenue 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)
payments
booking_idINTEGERamountINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Payment transactions connect to listings through bookings.
Hint 2
Aggregate every matched amount at listing grain.
Hint 3
Resolve equal revenue with listing ID.
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, 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 b.listing_id, l.city ORDER BY total_revenue DESC, b.listing_idWhy this works
amount lives in payments (join via booking_id); city lives in listings (join via listing_id). SUM(amount) per listing gives the total revenue each property earned from paid bookings only.
Success check
Returns one stable row per paid listing with complete transaction revenue
Expected result
Use this output to verify values, aliases, ordering, and row count.
| listing_id | city | total_revenue |
|---|---|---|
| 1 | New York | 800 |
| 5 | San Francisco | 800 |
| 6 | Paris | 640 |
| 4 | Toronto | 560 |
| 3 | London | 360 |
| 2 | Bangalore | 300 |
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.