Ride-Share: ETA Accuracy
Uber, Lyft, Grab
Problem Statement
Uber’s marketplace health depends on accurate ETAs. If a driver is consistently arriving much later (or earlier) than predicted, it may indicate a technical issue with their GPS or a behavioral pattern.
Write a query to find "Inaccurate Drivers." A driver is considered inaccurate if:
- They have completed at least 3 total trips in the dataset.
- For at least 80% of their total trips, the absolute difference between predicted_arrival_time and actual_arrival_time is greater than 5 minutes.
Rules:
- Absolute difference means if a driver is 6 minutes late OR 6 minutes early, both count as "off-schedule."
- Use the timestamp difference to determine the minutes.
- Output columns: driver_id, off_schedule_percentage, avg_deviation_minutes.
- Sort by off_schedule_percentage descending, then avg_deviation_minutes descending.
Tests your understanding of
Window Functions, Date Arithmetic, Filtering and Aggregation
Input Tables
trips
| trip_id(INTEGER) | driver_id(INTEGER) | predicted_arrival_time(TIMESTAMP) | actual_arrival_time(TIMESTAMP) |
|---|---|---|---|
| 1 | 101 | 2026-03-04 10:00:00 | 2026-03-04 10:07:00 |
| 2 | 101 | 2026-03-04 11:00:00 | 2026-03-04 11:06:00 |
| 3 | 101 | 2026-03-04 12:00:00 | 2026-03-04 12:08:00 |
| 4 | 102 | 2026-03-04 10:00:00 | 2026-03-04 10:01:00 |
| 5 | 102 | 2026-03-04 11:00:00 | 2026-03-04 11:02:00 |
| 6 | 102 | 2026-03-04 12:00:00 | 2026-03-04 12:01:00 |
| 7 | 103 | 2026-03-04 14:00:00 | 2026-03-04 14:10:00 |
| 8 | 103 | 2026-03-04 15:00:00 | 2026-03-04 15:15:00 |
| 9 | 103 | 2026-03-04 16:00:00 | 2026-03-04 16:02:00 |
Expected Output
| driver_id(INTEGER) | off_schedule_percentage(DECIMAL) | avg_deviation_minutes(DECIMAL) |
|---|---|---|
| 101 | 100 | 7 |
Tags
HardWindow FunctionsDate ArithmeticFilteringAggregation
30-40 min
34%