Find drivers whose average completed ride duration is more than 20% below the average for their city. Return driver_id, city, the driver's average duration in minutes, the city average, and the percentage difference. Only count rides with status 'completed'. Round all numeric values to 2 decimal places, ordered by percentage difference ascending.
Schema
drivers
driver_idINTEGER
nameVARCHAR
cityVARCHAR
joined_dateDATE
rides
ride_idINTEGER
driver_idINTEGER
statusVARCHAR
start_timeTIMESTAMP
end_timeTIMESTAMP
status values: 'completed', 'cancelled'
Target output
Target output
| driver_id | city | driver_avg_mins | city_avg_mins | pct_difference |
|---|
| 14 | Chicago | 8.00 | 12 | -33.33 |
| 22 | Austin | 8.50 | 11 | -22.73 |
What this question tests
Tests comparing each entity to a benchmark computed from its own group: driver averages against city averages, built with CTEs or window functions. Compare-to-cohort is one of the most reused interview shapes in marketplace analytics, because every operations team asks exactly this question.
More SQL interview questions
