Randomly Group Passenger IDs | Dataford Interview Questions

American Airlines wants to run a randomized experiment using eligible AAdvantage passengers from recent flight bookings. Write a PostgreSQL query that assigns each eligible passenger ID to one of three mutually exclusive groups: 10% treatment, 10% holdout, and 80% control.

Requirements

Include only passengers with a confirmed booking between 2026-07-01 and 2026-07-31.
Remove duplicate passengers who have multiple qualifying bookings.
Randomize the eligible passengers, then assign the first 10% to treatment_10_pct, the next 10% to holdout_10_pct, and the remainder to control_80_pct.
Return each passenger ID, assigned group, and position within that group. For sample data with 10 eligible passengers, the groups should contain 1, 1, and 8 passengers.

Schema

passengers

Column	Type	Description
passenger_idPK	INTEGER	Unique passenger identifier
passenger_name	VARCHAR(100)	Passenger name

flight_bookings

Column	Type	Description
booking_idPK	INTEGER	Unique booking identifier
passenger_id	INTEGER	Passenger associated with the booking
flight_number	VARCHAR(10)	American Airlines flight number
flight_date	DATE	Scheduled flight date
booking_status	VARCHAR(20)	Booking status

Your question is Randomly Group Passenger IDs. 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