Listings by Price Tier
Return listing_id, city, price_per_night, and price_tier. Budget is under 100, Mid-range is 100 through 170, and Premium is above 170. Order by price_per_night descending, then listing_id.
- CASE expressions
- Sorting
Challenge brief
Understand the request
Product — Pricing The pricing team wants to tag each listing with a price tier to power a filter on the search results page.
Categorise every listing as Budget, Mid-range, or Premium based on nightly price.
Return
- listing_id
- city
- price_per_night
- price_tier
Constraints
- Classify every listing using the stated price boundaries
- Order by nightly price descending, then listing ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
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
Translate the three price ranges into mutually exclusive branches.
Hint 2
Evaluate lower boundaries before the fallback tier.
Hint 3
Use the listing key to break equal-price ties.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT listing_id, city, price_per_night, CASE WHEN price_per_night < 100 THEN 'Budget' WHEN price_per_night <= 170 THEN 'Mid-range' ELSE 'Premium' END AS price_tier FROM listings ORDER BY price_per_night DESC, listing_idWhy this works
CASE WHEN assigns each listing to a tier based on its nightly price. The conditions are ordered from lowest to highest — the first matching branch wins. No JOIN needed; all data lives in listings.
Success check
Returns every listing with exactly one correctly bounded price tier
Expected result
Use this output to verify values, aliases, ordering, and row count.
| listing_id | city | price_per_night | price_tier |
|---|---|---|---|
| 5 | San Francisco | 200 | Premium |
| 3 | London | 180 | Premium |
| 6 | Paris | 160 | Mid-range |
| 1 | New York | 150 | Mid-range |
| 7 | Lisbon | 150 | Mid-range |
| 4 | Toronto | 140 | Mid-range |
| 2 | Bangalore | 60 | Budget |
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.