- ****************************************************************************
- ********************************************
Table: rideshare_trips
col_name | col_type -----------------+--------------------- id | bigint client_id | bigint driver_id | bigint status | varchar(20) -- 'completed', 'cancelled by driver', 'cancelled by client' request_dt | date
Sample results
driver_id | cancellation_rate
-----------+---------------------
20008 | 50.0000000000000000
Solution postgres
SELECT
driver_id,
COUNT(CASE WHEN status = 'cancelled by client' THEN ID ELSE NULL END) * 100.0/COUNT(*) AS cancellation_rate
FROM rideshare_trips
GROUP BY driver_id
ORDER BY 2 DESC
LIMIT 1;
Explanation
This query retrieves information from a table called "rideshare_trips" and calculates the cancellation rate for each driver. The SELECT statement specifies which columns to retrieve: driver_id and cancellation_rate.
The calculation for the cancellation_rate is done using a COUNT function combined with a CASE statement. The COUNT function counts the number of trips where the status is 'cancelled by client,' and the CASE statement assigns a value of ID for those trips and NULL for others. The multiplication by 100.0 and division by COUNT(*) is done to get the cancellation rate as a percentage.
The GROUP BY statement groups the results by driver_id, so the cancellation rate is calculated for each driver. The ORDER BY statement sorts the results by the second column (cancellation_rate) in descending order. Finally, the LIMIT 1 statement selects only the top result, which is the driver with the highest cancellation rate.
Expected results
More Lyft questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 154 | Rideshare completion rate lyft | easy |
|
| 156 | Cancellation rate by unbanned users lyft | hard |
|
| 157 | Number of trips before a driver got banned lyft | medium |
|
| 158 | First trip completion rate lyft | medium |
|