Number of Times a Driver Was a Passenger

Number of Times a Driver Was a Passenger

What is this problem about?

The Number of Times a Driver Was a Passenger SQL problem asks you to count how many times each driver has appeared as a passenger in any ride. Join the rides table to itself: once for driver IDs and once for passenger IDs, and count matches. This is a self-join aggregation problem.

Why is this asked in interviews?

Amazon asks this SQL problem to test self-join techniques — using a single table to find relationships between different roles. It validates knowledge of LEFT JOIN for preserving all drivers (even those with zero passenger appearances), GROUP BY for aggregation, and COALESCE for null handling. The database interview pattern is the core.

Algorithmic pattern used

Self-join + GROUP BY. Join the Rides table (as drivers) with the Rides table (as passengers) on driver.driver_id = passenger.passenger_id. Group by driver_id. Count the number of passenger appearances for each driver. Use LEFT JOIN to include drivers who were never passengers (count = 0). Use COALESCE or COUNT(passenger_match) to return 0 for non-passenger drivers.

Example explanation

Rides: Driver A drove 3 times. Passenger A appeared 2 times. Driver B drove 2 times. Passenger B appeared 0 times. Result: Driver A → 2, Driver B → 0.

Common mistakes candidates make

• Using INNER JOIN (excludes drivers who were never passengers).
• Not handling NULL passenger appearances (should return 0, not NULL).
• Aggregating on the wrong column (count passenger appearances, not driver appearances).
• Grouping by both tables' IDs instead of just the driver's ID.

Interview preparation tip

Self-join problems require aliasing the same table twice. The pattern: FROM Rides d LEFT JOIN Rides p ON d.driver_id = p.passenger_id. This finds all times each driver appeared as a passenger. LEFT JOIN preserves all drivers; COUNT(p.id) returns 0 when no passenger match exists. Practice self-join problems for "follower counts," "mutual connections," and "role overlap" analysis — they all use this aliased self-join pattern.