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.