Repeat Bookers
Return user_id, listing_id, and times_booked for repeated pairs. Order by frequency descending, then user_id and listing_id.
- Subqueries
- Aggregation
- Filtering
- Sorting
Challenge brief
Understand the request
Loyalty — Repeat Stay Insights Loyalty uses repeat guest-listing pairs as a satisfaction signal.
Find guests who booked the same listing more than once.
Return
- user_id
- listing_id
- times_booked
Constraints
- Aggregate at guest-listing pair grain
- Keep pairs with more than one booking
- Order by frequency descending, then user ID and listing ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
bookings
user_idINTEGERlisting_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
The grouping grain combines guest and listing.
Hint 2
Filter repeated pairs after counting bookings.
Hint 3
Use both pair keys to stabilize equal frequencies.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH booking_counts AS (SELECT user_id, listing_id, COUNT(*) AS times_booked FROM bookings GROUP BY user_id, listing_id), repeat_pairs AS (SELECT user_id, listing_id, times_booked FROM booking_counts WHERE times_booked > 1) SELECT user_id, listing_id, times_booked FROM repeat_pairs ORDER BY times_booked DESC, user_id, listing_idWhy this works
The first CTE counts bookings at guest-listing grain. The second retains counts above one, and the final query presents those repeat pairs in deterministic order.
Success check
Returns each qualifying guest-listing pair once in stable order
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | listing_id | times_booked |
|---|---|---|
| 2 | 1 | 2 |
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.