SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.
trips has a rider and a driver for each request, and a status: completed, cancelled_by_driver or cancelled_by_rider. ride_users.banned is 'Yes' or 'No'. For each request_date, return the share of trips that were cancelled (either status), considering only trips where both the rider and the driver are not banned. Round to 2 decimal places. Days with no such trip are not returned. Columns: day (the request date), cancellation_rate. Order by day.
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
Interview-style drill: Daily cancellation rate over trips where neither the rider nor the driver is banned.
`trips` has a rider and a driver for each request, and a `status`: `completed`, `cancelled_by_driver` or `cancelled_by_rider`. `ride_users.banned` is `'Yes'` or `'No'`. For each `request_date`, return the share of trips that were cancelled (either status), considering only trips where **both** the rider and the driver are not banned. Round to 2 decimal places. Days with no such trip are not returned. Columns: `day` (the request date), `cancellation_rate`. Order by `day`.