- ******************************************************************************
- ****************************************
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
avg_trips
--------------------
1.5000000000000000
(1 row)
Solution postgres
WITH banned_drivers AS (
SELECT
driver_id,
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
;
Explanation
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.
Copied
Expected results
Your 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 |
|