Top Revenue Listing
Return listing_id, city, and total_revenue for the single highest-revenue listing. Break equal revenue by the lower listing_id.
- Window functions
- Joins
- Subqueries
- Aggregation
- Filtering
Challenge brief
Understand the request
Finance — Best Performer Award Finance recognises the top-earning listing each quarter for the Airbnb Host Awards programme.
Rank paid listings and return one deterministic revenue leader.
Return
- listing_id
- city
- total_revenue
Constraints
- Aggregate every payment transaction by listing
- Return exactly one leader
- Break equal revenue by 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
Aggregate payment revenue before choosing a leader.
Hint 2
Rank listing totals from highest to lowest with a stable key tie-break.
Hint 3
Keep only the first ranked listing.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH listing_rev AS (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), ranked AS (SELECT listing_id, city, total_revenue, ROW_NUMBER() OVER (ORDER BY total_revenue DESC, listing_id) AS rn FROM listing_rev) SELECT listing_id, city, total_revenue FROM ranked WHERE rn = 1Why this works
The first CTE computes revenue per listing. The second applies ROW_NUMBER() ordered by revenue descending — filtering to rn = 1 safely returns exactly one winner even if there were a tie.
Success check
Returns one deterministic paid-revenue leader
Expected result
Use this output to verify values, aliases, ordering, and row count.
| listing_id | city | total_revenue |
|---|---|---|
| 1 | New York | 800 |
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.