Driver-Passenger Role Crossover Frequency

Medium

A ride-sharing analytics team needs to understand role crossover behavior in the trip network. Table: Rides ride_id: unique ride identifier driver_id: account that operated the ride passenger_id: account that took the ride Business rule: driver_id and passenger_id are always different in each row. Task: For each distinct person who appears at least once as a driver, report how many rides list that same person as a passenger. In other words, for every output driver_id, cnt is the number of rows in Rides where: Rides.passenger_id = driver_id Output requirements: Return exactly two columns: driver_id cnt Include exactly one row for every distinct driver_id found in Rides.driver_id. Sort by driver_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Rides:
| ride_id | driver_id | passenger_id |
|---------|-----------|--------------|
| 1       | 7         | 1            |
| 2       | 7         | 2            |
| 3       | 11        | 1            |
| 4       | 11        | 7            |
| 5       | 11        | 7            |
| 6       | 11        | 3            |

Output

[
  {"driver_id": 7, "cnt": 2},
  {"driver_id": 11, "cnt": 0}
]

Explanation

Drivers are {7, 11}. ID 7 appears twice as a passenger, while ID 11 never appears as a passenger.

Example 2

Input

Rides:
| ride_id | driver_id | passenger_id |
|---------|-----------|--------------|
| 1       | 3         | 10           |
| 2       | 4         | 3            |
| 3       | 5         | 3            |
| 4       | 5         | 8            |
| 5       | 8         | 3            |

Output

[
  {"driver_id": 3, "cnt": 3},
  {"driver_id": 4, "cnt": 0},
  {"driver_id": 5, "cnt": 0},
  {"driver_id": 8, "cnt": 1}
]

Explanation

Only IDs that are drivers appear in the output. Passenger-only ID 10 is not listed.

Example 3

Input

Rides:
[]

Output

[]

Explanation

No rides means there are no drivers to report.

Constraints

0 <= number of rows in Rides <= 900
1 <= ride_id <= 1000000000
1 <= driver_id <= 1000000000
1 <= passenger_id <= 1000000000
ride_id values are unique
driver_id != passenger_id for every row
Return one row per distinct value in Rides.driver_id
cnt counts ride rows (not distinct passengers)
Sort final output by driver_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: driver_id, cnt

Rides: | ride_id | driver_id | passenger_id | |---------|-----------|--------------| | 1 | 7 | 1 | | 2 | 7 | 2 | | 3 | 11 | 1 | | 4 | 11 | 7 | | 5 | 11 | 7 | | 6 | 11 | 3 |

Rides: | ride_id | driver_id | passenger_id | |---------|-----------|--------------| | 1 | 3 | 10 | | 2 | 4 | 3 | | 3 | 5 | 3 | | 4 | 5 | 8 | | 5 | 8 | 3 |