Trips and Users

Trips and Users

What is this problem about?

The "Trips and Users coding problem" is a high-level SQL challenge that simulates a ride-sharing service's data analysis. You are given a Trips table (recording all rides) and a Users table (identifying banned and unbanned users). Your goal is to find the cancellation rate of requests with unbanned users (both client and driver must be unbanned) for each day between specific dates. The cancellation rate is calculated as the number of cancelled requests divided by the total number of requests by unbanned users on that day.

Why is this asked in interviews?

This "Trips and Users interview question" is a favorite at data-heavy companies like Uber and Meta. it tests your ability to perform complex joins with multiple filters and aggregations. Specifically, it evaluates how you handle "conditional counting" (calculating rates based on specific statuses) and how you efficiently join a large transaction table with a lookup table while excluding certain records.

Algorithmic pattern used

The "Database interview pattern" for this problem involves a JOIN between Trips and Users (twice, once for the client and once for the driver) or using WHERE clauses with NOT IN or IN subqueries to filter out banned users. After filtering, you use GROUP BY request_at and a conditional SUM or COUNT (e.g., SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END)) to calculate the daily cancellation rate. Using ROUND to format the final decimal value is also a key requirement.

Example explanation

Trips:

• T1: Client 1, Driver 10, Date '2020-10-01', Status 'completed'
• T2: Client 2, Driver 11, Date '2020-10-01', Status 'cancelled_by_driver' Users:
• 1: Unbanned, 2: Banned, 10: Unbanned, 11: Unbanned Logic:

1. T1: Both unbanned. Include.
2. T2: Client 2 is banned. Exclude. Result for 2020-10-01:

• Total unbanned trips: 1 (T1)
• Total cancelled unbanned trips: 0 Cancellation Rate: 0.00.

Common mistakes candidates make

One major pitfall in the "Trips and Users coding problem" is forgetting to check that both the client and the driver are unbanned. Many candidates only check the client. Another mistake is incorrect date filtering or failing to round the final result to two decimal places. Using an INNER JOIN that accidentally removes valid days with zero cancellations can also lead to incomplete results.

Interview preparation tip

When working on the "Database interview pattern," practice using CASE WHEN inside SUM() functions. This is a powerful way to perform "Pivot Table" style logic in SQL, allowing you to calculate percentages and rates across grouped categories in a single pass.