Loading problem...
A ride-sharing analytics team needs to understand role crossover behavior in the trip network.
Table: Rides
Business rule:
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:
Supported submission environments:
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 |[
{"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.
Rides:
| ride_id | driver_id | passenger_id |
|---------|-----------|--------------|
| 1 | 3 | 10 |
| 2 | 4 | 3 |
| 3 | 5 | 3 |
| 4 | 5 | 8 |
| 5 | 8 | 3 |[
{"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.
Rides:
[][]Explanation
No rides means there are no drivers to report.
Constraints