Find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between '2023-10-01' and '2023-10-03'. Round Cancellation Rate to two decimal places.
This advanced problem mirrors real operational analytics at Uber. The company needs to track cancellation rates while excluding fraudulent or banned users from metrics to get accurate business insights. This question tests your ability to work with multiple tables, apply complex filtering logic, and perform conditional aggregations—all critical skills for data-driven decision making in ride-sharing platforms.
Key SQL concepts tested: JOIN operations across multiple tables, subqueries with IN clause for filtering, CASE WHEN for conditional logic, GROUP BY for daily aggregation, ROUND() for precision control, and date range filtering with BETWEEN. The challenge is coordinating filters across both client and driver dimensions simultaneously.
Uber's data team uses similar queries daily to: monitor service quality metrics excluding test accounts, calculate driver performance scores while filtering out banned riders, generate executive dashboards showing clean operational KPIs, detect anomalies in cancellation patterns by geography and time, and feed machine learning models with high-quality training data free from fraudulent activity.
When tackling this Uber problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT
request_at AS Day,
ROUND(
SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END) * 1.0 / COUNT(*),
2
) AS 'Cancellation Rate'
FROM trips
WHERE client_id IN (
SELECT users_id FROM users WHERE banned = 'No'
)
AND driver_id IN (
SELECT users_id FROM users WHERE banned = 'No'
)
AND request_at BETWEEN '2023-10-01' AND '2023-10-03'
GROUP BY request_at;Subquery filters pre-exclude banned users before aggregation, keeping the main query logic clean. The CASE expression inside SUM counts cancelled and incomplete trips, while dividing by COUNT(*) gives the daily rate. Multiplying by 1.0 forces floating-point division in MySQL where integer division would truncate to zero. This approach is straightforward and works on all SQL dialects. The downside is that the IN subqueries can be slow on large users tables without an index on (users_id, banned); the optimizer may re-evaluate them per-row rather than materializing them once.
SELECT
t.request_at AS Day,
ROUND(
COUNT(CASE WHEN t.status != 'completed' THEN 1 END) * 1.0 / COUNT(*),
2
) AS 'Cancellation Rate'
FROM trips t
JOIN users c ON t.client_id = c.users_id AND c.banned = 'No'
JOIN users d ON t.driver_id = d.users_id AND d.banned = 'No'
WHERE t.request_at BETWEEN '2023-10-01' AND '2023-10-03'
GROUP BY t.request_at;Joining users twice — once for clients and once for drivers — lets the optimizer use hash or merge joins, which are typically more efficient than IN subqueries on large datasets. The CASE inside COUNT (without an ELSE) returns NULL for completed trips, and COUNT ignores NULLs, neatly counting only cancellations. This approach leverages join-predicate pushdown and benefits from composite indexes on (users_id, banned). It is preferred in production environments. A subtle risk is that a trip with both a banned client and banned driver would be excluded correctly because both JOINs must match.
Performance of this query hinges heavily on index design. A composite index on trips(request_at, client_id, driver_id) allows the database to satisfy the date range filter and look up user IDs without a full table scan. On the users table, an index on (users_id, banned) enables index-only scans for the banned-status check. The IN subquery approach may cause the optimizer to materialize the subquery result as a hash table and probe it once per trip row — efficient if the users table is large and the banned list is small, but potentially slower if the optimizer chooses nested-loop evaluation. The JOIN approach typically outperforms IN at scale because it exposes selectivity to the optimizer earlier. For a three-day window the date filter is highly selective, so a partial index on trips(request_at) covering just the date range can dramatically reduce I/O.
A very common error is filtering only banned clients but forgetting to also filter banned drivers, producing an inflated denominator. Another pitfall is using status = 'cancelled' (a guess) instead of status != 'completed', missing statuses like 'cancelled_by_driver' or 'cancelled_by_client'. Integer division is a silent killer in MySQL: omitting * 1.0 or CAST(... AS FLOAT) makes the division return 0 for rates less than 1.0. Candidates often also forget the BETWEEN date filter, computing the rate across all dates. Finally, using COUNT(status != 'completed') instead of SUM(CASE ...) or COUNT(CASE ...) is wrong — COUNT counts non-NULL values, not truthy values, so it counts every row.
Real-world trip tables often have multiple non-successful statuses such as 'cancelled_by_driver', 'cancelled_by_client', 'no_show', or 'expired'. Using != 'completed' captures all failure modes without enumerating them explicitly, making the query resilient to new status values. If the problem guarantees only two statuses then both forms are equivalent, but the != 'completed' formulation is the safer, more general answer and signals production-quality thinking to interviewers.
For a dynamic range, parameterize the BETWEEN clause. For a very large range — months or years of data — consider a date-partitioned table where each partition covers one day or month, so the query prunes irrelevant partitions entirely. You could also pre-aggregate daily cancellation rates into a summary table updated incrementally, then query the summary for historical ranges, reducing real-time computation to only the most recent incomplete day.
ROUND(0.0, 2) returns 0.00 and ROUND(1.0, 2) returns 1.00 — both are valid edge cases. A day where every trip is completed has rate 0.00; a day where every trip is cancelled has rate 1.00. These edge cases should be explicitly tested. Note that if all trips for a given day are filtered out due to banned users, that day will not appear in the GROUP BY result at all — an important distinction from appearing with a NULL rate.