Guest Total Spend
Return user_id, country, and total_spent for each guest with paid bookings. Order by total_spent descending, then user_id.
- Joins
- Aggregation
- Sorting
Challenge brief
Understand the request
Finance — Guest Lifetime Value Finance uses total guest spend to calculate lifetime value (LTV) and decide eligibility for loyalty rewards.
Calculate how much each guest has spent in total on paid bookings.
Return
- user_id
- country
- total_spent
Constraints
- Include guests with matched booking payments
- Sum every payment transaction at guest grain
- Order by spend descending, then user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
bookings
booking_idINTEGERuser_idINTEGER
users
user_idINTEGERcountryVARCHAR(50)
payments
booking_idINTEGERamountINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A payment reaches its guest through the related booking.
Hint 2
Aggregate payment amounts at user and country grain.
Hint 3
Use user ID to resolve equal spend.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT b.user_id, u.country, SUM(p.amount) AS total_spent FROM bookings b JOIN users u ON b.user_id = u.user_id JOIN payments p ON b.booking_id = p.booking_id GROUP BY b.user_id, u.country ORDER BY total_spent DESC, b.user_idWhy this works
country comes from users; payment amount from payments. Both join via bookings as the bridge. SUM per user gives total lifetime spend from paid bookings.
Success check
Returns one stable row per paying guest with complete transaction spend
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | country | total_spent |
|---|---|---|
| 8 | DE | 640 |
| 6 | FR | 600 |
| 5 | US | 560 |
| 2 | IN | 500 |
| 4 | CA | 360 |
| 3 | UK | 300 |
| 7 | US | 300 |
| 1 | US | 200 |
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.