Handling Duplicate Passenger Bookings | Dataford Interview Questions

How would you identify and handle duplicate passenger bookings in a large relational database?

Write a query that finds duplicate active bookings for the same passenger, flight, and departure date. Treat the earliest booking as the one to retain and list later bookings for review or cancellation.

Output

One row per duplicate booking group with passenger_id, passenger_name, flight_number, departure_date, and duplicate_count.
Include retained_booking_id and comma-separated duplicate_booking_ids.
Exclude cancelled bookings, order by departure_date, flight_number, and passenger_id.

Schema

passengers

Column	Type	Description
passenger_idPK	INT	Unique passenger identifier
full_name	VARCHAR(120)	Passenger full name
loyalty_status	VARCHAR(30)	Passenger loyalty membership status

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
passenger_id	INT	Passenger associated with the booking
flight_number	VARCHAR(10)	Flight number
departure_date	DATE	Scheduled departure date
origin	VARCHAR(3)	Origin airport code
destination	VARCHAR(3)	Destination airport code
booking_status	VARCHAR(20)	Current booking status
booked_at	TIMESTAMP	Booking creation timestamp

Your question is Handling Duplicate Passenger Bookings. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem