156. Cancellation rate by unbanned users

hard lyft

  • **************************************************************************************
  • ***************************************************************************

Table 1: 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

Table 2: rideshare_users

All users on the ridesharing platform. If a user has two roles, they will have multiple rows, e.g., one row for the client, one row for the driver with different user_id.

  col_name       | col_type
user_id          |  bigint   
role             |  varchar(10) -- 'client' or 'driver'
joined_at        |  timestamp  -- when the user account is created
banned_at        |  timestamp -- when the user accounts is banned, null if not banned

Sample results


Solution postgres

WITH banned_clients AS (
    SELECT user_id, banned_at
    FROM rideshare_users
    WHERE banned_at IS NOT NULL
    AND role = 'client'
banned_drivers AS (
    SELECT user_id, banned_at
    FROM rideshare_users
    WHERE banned_at IS NOT NULL
    AND role = 'driver'
    100 - COUNT(CASE WHEN status = 'completed' THEN ID ELSE NULL END) * 100.0 / COUNT(*) AS cancellation_rate
FROM rideshare_trips T
LEFT JOIN banned_clients C
ON C.user_id = T.client_id
AND request_dt > C.banned_at
LEFT JOIN banned_drivers D
ON D.user_id = T.driver_id
AND request_dt > D.banned_at
AND D.user_id IS NULL


This query is trying to calculate the cancellation rate of trips in the rideshare platform. It first creates two subqueries using a common table expression (CTE) to identify users who have been banned, one for clients and one for drivers.

Then it joins the trips table with the banned clients and drivers tables, filtering out any trips where the client or driver is banned at the time of the request.

Finally, it counts the number of completed trips and divides it by the total number of trips to get the cancellation rate, subtracting it from 100 to get the percentage. The result is a single row with one column, the cancellation rate.

Expected results

More Lyft questions

ID Title Level FTPR
154 Rideshare completion rate lyft easy
155 Driver with the highest cancellation rate lyft easy
157 Number of trips before a driver got banned lyft medium
158 First trip completion rate lyft medium