157. Number of trips before a driver got banned

medium 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

(1 row)

Solution postgres

WITH banned_drivers AS (
        ROW_NUMBER() OVER(PARTITION BY driver_id ORDER BY request_dt) row_number
    FROM rideshare_trips T
    INNER JOIN rideshare_users U
    ON T.driver_id = U.user_id
    WHERE banned_at IS NOT NULL
    AND request_dt < banned_at
num_trips_before_banned AS (
    SELECT driver_id, MAX(row_number) AS num_trips
    FROM banned_drivers
    GROUP BY driver_id
SELECT AVG(num_trips) avg_trips
FROM  num_trips_before_banned


This query is used to calculate the average number of trips made by drivers before they were banned from the rideshare platform.

  • The first part of the query creates a temporary table called "banned_drivers" that selects the driver ID and the row number (based on the request date) of all trips made by drivers who have been banned. The table is joined with the users table to get additional information about the drivers. The WHERE clause filters out all trips made after the driver was banned.
  • The second part of the query creates another temporary table called "num_trips_before_banned" that groups the banned drivers by their ID and calculates the maximum row number (i.e. the number of trips made before they were banned).
  • Finally, the query selects the average of the "num_trips" column from the "num_trips_before_banned" table, which gives the average number of trips made by drivers before they were banned.

Expected results

More Lyft questions

ID Title Level FTPR
154 Rideshare completion rate lyft easy
155 Driver with the highest cancellation rate lyft easy
156 Cancellation rate by unbanned users lyft hard
158 First trip completion rate lyft medium