Airbnb-style Company ChallengeHardVerified answerSQLite live

Top 3 Most Active Guests

Return user_id, country, and booking_count for the top 3 guests by booking volume, ordered by booking_count descending then user_id ascending.

  • Joins
  • Aggregation
  • Sorting
  • Top-N

Challenge brief

Understand the request

Loyalty — Top Guest Recognition The loyalty team highlights the top 3 most-active guests each month for early access to new listings and priority support.

Identify the three guests with the most bookings.

Return

  • user_id
  • country
  • booking_count

Constraints

  • Aggregate booking volume at guest grain
  • Return exactly three guests
  • Order by booking count descending, then user ID

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

bookings

  • user_idINTEGER

users

  • user_idINTEGER
  • countryVARCHAR(50)

Hints, when you need them

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

Hint 1

Count bookings for each guest before limiting rows.

Hint 2

Sort volume descending with user ID as the tie-break.

Hint 3

Retain only the first three ordered guests.

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, COUNT(*) AS booking_count
FROM bookings b
JOIN users u ON b.user_id = u.user_id
GROUP BY b.user_id, u.country
ORDER BY booking_count DESC, b.user_id
LIMIT 3

Why this works

GROUP BY user gives booking frequency; JOIN brings in country. ORDER BY booking_count DESC then user_id breaks ties. LIMIT 3 returns the top three guests.

Success check

Returns exactly three deterministic booking-volume leaders

Expected result

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

user_idcountrybooking_count
2IN3
3UK2
1US1

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.