Cancellation rate excluding banned users

SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.

A ride-hailing company wants its daily cancellation rate, but only from trips where neither the rider nor the driver is banned, because banned users distort the numbers. trips has a rider, a driver, a status (completed, cancelled_by_driver or cancelled_by_rider) and a request_date. ride_users.banned is 'Yes' or 'No'. For each request date, return the share of trips that were cancelled (by either side), counting only trips where both users are not banned. Round to 2 decimals. Days with no such trip are not returned. Return day (the request date) and cancellation_rate, sorted by day.

Requirements

• Keep only trips where the rider is not banned and the driver is not banned.
• Treat both cancelled statuses as a cancellation.
• For each day, divide the number of cancelled trips by the number of trips.
• Round the rate to 2 decimals.
• Do not return days that have no trips after the filtering.
• Sort by day.

Constraints

• The tables are trips and ride_users. Join ride_users twice, once for the rider and once for the driver.
• status is one of completed, cancelled_by_driver or cancelled_by_rider.
• The rate is a fraction between 0 and 1, not a percent.
• Return only these 2 columns, in this order: day, cancellation_rate.

Examples

Input: trips trip_id | rider_id | driver_id | status | request_date 1 | 1 | 10 | completed | 2024-05-01 2 | 2 | 11 | cancelled_by_driver | 2024-05-01 3 | 3 | 11 | cancelled_by_rider | 2024-05-01 4 | 4 | 12 | completed | 2024-05-01 5 | 4 | 11 | cancelled_by_driver | 2024-05-01 6 | 1 | 10 | completed | 2024-05-01 7 | 1 | 10 | completed | 2024-05-02 8 | 3 | 10 | cancelled_by_driver | 2024-05-02 9 | 4 | 11 | completed | 2024-05-02 10 | 2 | 12 | completed | 2024-05-03 11 | 2 | 10 | cancelled_by_rider | 2024-05-03 ride_users user_id | banned | role 1 | false | rider 2 | true | rider 3 | false | rider 4 | false | rider 10 | false | driver 11 | false | driver 12 | true | driver Output: day | cancellation_rate 2024-05-01 | 0.5 2024-05-02 | 0.33 Why this passes: On 2024-05-01 and 2024-05-02 trips with a banned rider or driver are excluded before the rate is taken. All trips on 2024-05-03 involve a banned user, so that day is absent.

Input: trips trip_id | rider_id | driver_id | status | request_date 1 | 1 | 10 | completed | 2024-05-01 2 | 1 | 10 | cancelled_by_driver | 2024-05-01 ride_users user_id | banned | role 1 | false | rider 10 | false | driver Output: day | cancellation_rate 2024-05-01 | 0.5 Why this passes: Two valid trips, one cancelled.

Topics: lakebench, sql, joins, rate, filtering.

More SQL interview questions · All interview problems · Learn data engineering