Airbnb-style Company ChallengeMediumVerified answerSQLite live

Unpaid Bookings

Return booking_id, user_id, listing_id, nights, and booking_date for all bookings with no matching payment, ordered by booking_id.

  • Subqueries
  • Filtering
  • Sorting

Challenge brief

Understand the request

Finance — Collections Finance runs a daily collections sweep to flag bookings that were confirmed but never paid so they can follow up before check-in.

Identify bookings that have no payment record.

Return

  • booking_id
  • user_id
  • listing_id
  • nights
  • booking_date

Constraints

  • Include bookings with no matching payment transaction
  • Remain correct when payment booking IDs contain NULL
  • Order by booking ID

Data you will use

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

bookings

  • booking_idINTEGER
  • user_idINTEGER
  • listing_idINTEGER
  • nightsINTEGER
  • booking_dateDATE

payments

  • booking_idINTEGER

Hints, when you need them

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

Hint 1

Evaluate payment absence for each booking.

Hint 2

Use a correlated absence check that is safe when the payment key can be NULL.

Hint 3

Stabilize the result with booking ID.

Verified SQL answer

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

Reveal solution and explanation
SELECT b.booking_id, b.user_id, b.listing_id, b.nights, b.booking_date FROM bookings b WHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.booking_id = b.booking_id) ORDER BY b.booking_id

Why this works

The subquery returns every booking_id that has a payment. NOT IN excludes those, leaving only the three unpaid bookings (9, 10, 11).

Success check

Returns every unpaid booking without NULL-sensitive exclusions

Expected result

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

booking_iduser_idlisting_idnightsbooking_date
92322023-08-15
103132023-08-20
112142023-09-01

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.