Number of Times a Driver Was a Passenger - Problem

Given a Rides table containing ride information with drivers and passengers, write a SQL solution to report the ID of each driver and the number of times they were a passenger in other rides.

Each row contains a ride_id, driver_id, and passenger_id. Note that driver_id != passenger_id for any ride.

Return the result table in any order showing each driver's ID and their passenger count.

Table Schema

Rides
Column Name Type Description
ride_id PK int Unique identifier for each ride
driver_id int ID of the driver for this ride
passenger_id int ID of the passenger for this ride
Primary Key: ride_id
Note: Each row represents one ride with a driver and passenger. Driver and passenger are always different people.

Input & Output

Example 1 — Multiple Drivers with Passenger Trips
Input Table:
ride_id driver_id passenger_id
1 1 2
2 2 1
3 3 1
4 3 2
Output:
driver_id cnt
1 2
2 2
💡 Note:

Driver 1 appears as a passenger in rides 2 and 3 (driven by drivers 2 and 3), so count = 2. Driver 2 appears as a passenger in ride 1 (driven by driver 1), so count = 1. Driver 3 never appears as a passenger, so they are not included in the result.
Example 2 — Driver Never a Passenger
Input Table:
ride_id driver_id passenger_id
1 1 2
2 1 3
Output:
driver_id cnt
💡 Note:

Driver 1 never appears as a passenger in any ride, so the result is empty. Only drivers who have been passengers at least once appear in the output.
Example 3 — All Drivers as Passengers
Input Table:
ride_id driver_id passenger_id
1 1 2
2 2 1
Output:
driver_id cnt
1 1
2 1
💡 Note:

Driver 1 appears as passenger in ride 2, and driver 2 appears as passenger in ride 1. Each has been a passenger exactly once.

Constraints

  • 1 ≤ ride_id ≤ 100
  • 1 ≤ driver_id, passenger_id ≤ 100
  • driver_id != passenger_id

Visualization

Tap to expand
Driver Passenger Count Analysis INPUT: Rides Table ride_id driver_id pass_id 1 10 20 2 20 30 3 30 10 4 10 30 5 20 10 Unique Drivers: 10 20 30 driver_id != passenger_id for every ride Find how many times each driver was a passenger ALGORITHM STEPS 1 Get All Drivers SELECT DISTINCT driver_id FROM Rides 2 LEFT JOIN Rides Match driver_id with passenger_id in Rides 3 COUNT Matches Count passenger occurrences for each driver 4 GROUP BY driver_id Aggregate results per driver SELECT d.driver_id, COUNT(r.passenger_id) FROM (SELECT DISTINCT driver_id FROM Rides) d LEFT JOIN Rides r ON... GROUP BY d.driver_id FINAL RESULT driver_id cnt 10 2 20 1 30 2 Breakdown: Driver 10: passenger in rides 3, 5 --> cnt = 2 Driver 20: passenger in ride 1 --> cnt = 1 Driver 30: passenger in rides 2, 4 --> cnt = 2 OK - All drivers counted! Key Insight: Use LEFT JOIN to ensure all drivers appear in the result, even if they were never a passenger. The self-join connects the Rides table to itself: matching driver_id from distinct drivers with passenger_id from all rides. COUNT on the passenger_id gives the occurrence count. TutorialsPoint - Number of Times a Driver Was a Passenger | Optimal Solution

Related Problems

Asked in
Amazon 28 Microsoft 22 Apple 15
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen