Airbnb-style Company ChallengeHardVerified answerSQLite live

High-Value Guests

Return user_id and total_spent for guests whose total spend is above the average per-guest total spend. Order by total_spent descending, then user_id.

  • Joins
  • Subqueries
  • Aggregation
  • Filtering
  • Sorting

Challenge brief

Understand the request

Finance — VIP Programme Finance is defining a VIP tier. The threshold is spending more than the average total spend of all paying guests.

Find guests whose total spend exceeds the average total spend across all paying guests.

Return

  • user_id
  • total_spent

Constraints

  • Calculate spend once at guest grain
  • Compare each guest total with the average of those guest totals
  • 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_idINTEGER
  • user_idINTEGER

payments

  • booking_idINTEGER
  • amountINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

First reduce payment transactions to one total per guest.

Hint 2

Compute the comparison benchmark from those guest totals.

Hint 3

Filter above the benchmark and stabilize equal totals with user ID.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH user_spend AS (SELECT b.user_id, SUM(p.amount) AS total_spent FROM bookings b JOIN payments p ON b.booking_id = p.booking_id GROUP BY b.user_id), avg_spend AS (SELECT AVG(total_spent) AS avg_total FROM user_spend) SELECT us.user_id, us.total_spent FROM user_spend us CROSS JOIN avg_spend a WHERE us.total_spent > a.avg_total ORDER BY us.total_spent DESC, us.user_id

Why this works

The first CTE computes each guest's total spend from paid bookings. The second CTE averages those totals ($426.25). The main query keeps only guests above that average. Using two CTEs avoids comparing a per-guest total to a raw per-payment average — a common logic error.

Success check

Returns stable above-average guests using the average of aggregated guest totals

Expected result

Use this output to verify values, aliases, ordering, and row count.

user_idtotal_spent
8640
6600
5560
2500

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.