Listing Demand Summary
Return listing_id, city, price_per_night, total_bookings, and total_nights for all listings, ordered by total_bookings descending then listing_id.
- Joins
- Aggregation
- NULL handling
- Sorting
Challenge brief
Understand the request
Supply — Occupancy Analytics The supply team uses booking volume and total nights to estimate occupancy rates and advise hosts on availability settings.
Report booking demand and booked nights for every catalog listing.
Return
- listing_id
- city
- price_per_night
- total_bookings
- total_nights
Constraints
- Preserve every listing, including listings without bookings
- Count matched bookings and report zero for no demand
- Sum matched nights and report zero for no demand
- Order by booking count descending, then listing ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
bookings
listing_idINTEGERnightsINTEGER
listings
listing_idINTEGERcityVARCHAR(50)price_per_nightINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Begin from catalog supply so listings without demand remain visible.
Hint 2
Count a nullable booking key and protect the nights sum.
Hint 3
Aggregate at listing grain and order by demand then listing key.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT l.listing_id, l.city, l.price_per_night, COUNT(b.booking_id) AS total_bookings, COALESCE(SUM(b.nights), 0) AS total_nights FROM listings l LEFT JOIN bookings b ON l.listing_id = b.listing_id GROUP BY l.listing_id, l.city, l.price_per_night ORDER BY total_bookings DESC, l.listing_idWhy this works
The CTE groups all bookings by listing and aggregates COUNT (demand) and SUM(nights) (occupancy volume). city and price_per_night come from listings via JOIN. The outer query applies the required sort.
Success check
Returns one row per listing with zero-safe demand measures
Expected result
Use this output to verify values, aliases, ordering, and row count.
| listing_id | city | price_per_night | total_bookings | total_nights |
|---|---|---|---|---|
| 1 | New York | 150 | 4 | 12 |
| 3 | London | 180 | 2 | 4 |
| 5 | San Francisco | 200 | 2 | 4 |
| 2 | Bangalore | 60 | 1 | 5 |
| 4 | Toronto | 140 | 1 | 4 |
| 6 | Paris | 160 | 1 | 4 |
| 7 | Lisbon | 150 | 0 | 0 |
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.