Airbnb-style Company ChallengeMediumVerified answerSQLite live

Guests Who Explored Multiple Listings

Return user_id and unique_listings for guests with more than one distinct listing. Order by unique_listings descending, then user_id.

  • Aggregation
  • HAVING
  • Sorting
  • Distinct values

Challenge brief

Understand the request

Product — Discovery Discovery measures multi-listing exploration as a search-engagement signal.

Find guests who booked more than one distinct listing.

Return

  • user_id
  • unique_listings

Constraints

  • Count distinct listing IDs per guest
  • Keep guests with more than one distinct listing
  • Order by distinct listing 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
  • listing_idINTEGER

Hints, when you need them

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

Hint 1

The business grain is one guest across unique listings.

Hint 2

Deduplicate listing IDs inside the guest-level count.

Hint 3

Filter grouped users after aggregation.

Verified SQL answer

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

Reveal solution and explanation
SELECT user_id, COUNT(DISTINCT listing_id) AS unique_listings
FROM bookings
GROUP BY user_id
HAVING COUNT(DISTINCT listing_id) > 1
ORDER BY unique_listings DESC, user_id

Why this works

COUNT(DISTINCT listing_id) per user counts unique properties visited. HAVING > 1 filters to multi-listing explorers. The secondary ORDER BY user_id stabilises the output.

Success check

Returns one row per qualifying guest without repeat-booking inflation

Expected result

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

user_idunique_listings
22
32

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.