Number Of Times A Driver Was A Passenger - Leetcode Solution

Problem Statement

We are given a table called "trip" with the following columns: id, rider_id, driver_id. The table represents the data of trips taken by riders with drivers. There can be multiple trips taken by the same riders and/or drivers. A driver can also ride as a passenger in someone else's trip.

The task is to write a SQL query that returns the id and the total number of times each driver was a passenger. The result should be sorted in descending order of the total number of times a driver was a passenger.

Solution

We can solve this problem by using a self-join on the trip table, where we join the table with itself on the driver_id column. The left join is used to include those records where the driver was a passenger and not a driver. We group the result by the driver_id column and use the count function to count the number of times a driver was a passenger.

The SQL query for this problem is as follows:

SELECT t.driver_id, COUNT(tp.id) AS num_of_times_as_passenger
FROM trip t
LEFT JOIN trip tp ON t.driver_id = tp.rider_id
GROUP BY t.driver_id
ORDER BY num_of_times_as_passenger DESC;

In this query, we use a left join to include all the records from the trip table, even if there is no matching record in the self-join. The group by clause is used to group the result by the driver_id column, and the count function counts the number of records where the driver_id column matches the rider_id column. Finally, the result is sorted in descending order based on the number of times a driver was a passenger.

Example

Consider the following trip table:

| id | rider_id | driver_id | |----|----------|-----------| | 1 | 23 | 1 | | 2 | 33 | 1 | | 3 | 23 | 2 | | 4 | 34 | 3 | | 5 | 45 | 3 | | 6 | 56 | 2 | | 7 | 3 | 4 |

Applying the above SQL query on this table will result in the following output:

| driver_id | num_of_times_as_passenger | |-----------|---------------------------| | 3 | 2 | | 2 | 1 | | 1 | 0 | | 4 | 0 |

As we can see in the above result, driver_id 3 has been a passenger two times, driver_id 2 has been a passenger one time, and driver_id 1 and 4 have not been a passenger.

1