Number of Times a Driver Was a Passenger - Practice Coding Problems

Number of Times a Driver Was a Passenger - Problem

Database Medium

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	1

💡 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

Understanding the Visualization

Input Table

Rides with driver and passenger IDs

Self-Join

Match drivers with passenger records

Count Result

Group and count passenger trips per driver

Key Takeaway

🎯 Key Insight: Self-joins are perfect for analyzing relationships within the same table

Asked in

A Amazon 28 M Microsoft 22 🍎 Apple 15

Use self-join to match driver_id with passenger_id, then GROUP BY driver and COUNT(*) to get passenger trip counts.

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.

Common Approaches

Approach	Time	Space	Notes
✓ Self-Join with Group By	O(n²)	O(n)	Join the Rides table with itself to match drivers with their passenger trips

Self-Join with Group By — Algorithm Steps

Step 1: Self-join Rides table matching driver_id with passenger_id
Step 2: Group by driver_id and count the matches
Step 3: Select driver_id and count as result

Visualization

Tap to expand

Step-by-Step Walkthrough

Self-Join

Match driver_id with passenger_id

Group

Group by driver_id

Count

Count passenger trips per driver

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Self-join requires comparing each driver with all passenger records

⚠ Quadratic Growth

Space Complexity

O(n)

Storage for join results and grouping

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Self-Join with Group By — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler