Airbnb-style Company ChallengeEasyVerified answerSQLite live

Bookings per Guest

Return user_id and total_bookings for every registered user, including users with zero bookings. Order by total_bookings descending, then user_id.

  • Joins
  • Aggregation
  • Sorting

Challenge brief

Understand the request

Loyalty Programme The loyalty team uses booking frequency to tier guests and assign reward credits.

Report booking frequency for the complete registered guest population.

Return

  • user_id
  • total_bookings

Constraints

  • Preserve every registered user
  • Count matched bookings, reporting zero when none exist
  • 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.

users

  • user_idINTEGER

bookings

  • user_idINTEGER

Hints, when you need them

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

Hint 1

Begin from the population the report promises to preserve.

Hint 2

Match booking facts optionally and count a nullable booking key.

Hint 3

Group at user grain and stabilize equal counts with user ID.

Verified SQL answer

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

Reveal solution and explanation
SELECT u.user_id, COUNT(b.booking_id) AS total_bookings FROM users u LEFT JOIN bookings b ON u.user_id = b.user_id GROUP BY u.user_id ORDER BY total_bookings DESC, u.user_id

Why this works

The user-preserving join retains accounts without activity. Counting the nullable booking key reports zero for those users, while user ID stabilizes equal totals.

Success check

Returns one row per registered user with a zero-safe booking count

Expected result

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

user_idtotal_bookings
23
32
11
41
51
61
71
81
90

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.