Traveller Tiers
Return user_id, total_nights, and traveller_tier for every user. Use three ordered buckets labelled Occasional, Regular, and Frequent. Order by total_nights descending, then user_id.
- Window functions
- Joins
- Subqueries
- Aggregation
- CASE expressions
Challenge brief
Understand the request
Growth — Lifecycle Marketing Lifecycle marketing assigns activity tiers for re-engagement.
Segment the complete registered guest population by total booked nights.
Return
- user_id
- total_nights
- traveller_tier
Constraints
- Preserve every registered user
- Report zero nights for users without bookings
- Use total nights and user ID as the stable bucket order
- Map the three buckets to the stated labels
- Order by total nights descending, then user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
users
user_idINTEGER
bookings
user_idINTEGERnightsINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Begin with the full user population and derive a zero-safe nights total.
Hint 2
Use the user key to make equal-night bucket placement reproducible.
Hint 3
Translate the three ordered buckets into the requested tier labels.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH user_nights AS (SELECT u.user_id, COALESCE(SUM(b.nights), 0) AS total_nights FROM users u LEFT JOIN bookings b ON u.user_id = b.user_id GROUP BY u.user_id), tiers AS (SELECT user_id, total_nights, NTILE(3) OVER (ORDER BY total_nights, user_id) AS bucket FROM user_nights) SELECT user_id, total_nights, CASE WHEN bucket = 1 THEN 'Occasional' WHEN bucket = 2 THEN 'Regular' ELSE 'Frequent' END AS traveller_tier FROM tiers ORDER BY total_nights DESC, user_idWhy this works
The first CTE sums nights per guest. The second applies NTILE(3) to divide guests into three equal-sized buckets ordered by nights. CASE maps bucket numbers to readable tier labels. The final ORDER BY ensures highest-mileage guests appear first.
Success check
Returns one deterministic travel tier per registered user, including zero-activity users
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | total_nights | traveller_tier |
|---|---|---|
| 2 | 9 | Frequent |
| 3 | 8 | Frequent |
| 5 | 4 | Regular |
| 8 | 4 | Frequent |
| 6 | 3 | Regular |
| 4 | 2 | Occasional |
| 7 | 2 | Regular |
| 1 | 1 | Occasional |
| 9 | 0 | Occasional |
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.