Courier Speed
ASKED IN INTERVIEWDoor Dash
Problem Statement
DoorDash logistics team wants to identify high-performing couriers. A courier is considered high-performing if their average delivery speed is at least 20% faster than the average delivery speed of all couriers in the same city.
Write an SQL query to find these couriers.
Rules:
- Speed is defined as distance divided by time (distance / duration).
- Distance is in kilometers and duration is in minutes.
- A courier is faster if their speed value is higher.
- 20% faster means the courier speed must be >= 1.2 * City Average Speed.
- Output columns: courier_id, city_id, courier_avg_speed, city_avg_speed.
- Round all speed values to 2 decimal places.
- Results should be ordered by courier_id in ascending order.
Table Schema:
- Couriers: Information about the delivery partners.
- Deliveries: Records of individual completed deliveries including distance and time.
Tests your understanding of
Window Functions, Aggregation, Mathematical Operations and Joins
Input Tables
Couriers
| courier_id(INTEGER) | city_id(INTEGER) |
|---|---|
| 1 | 501 |
| 2 | 501 |
| 3 | 501 |
| 4 | 502 |
| 5 | 502 |
| 6 | 502 |
Deliveries
| delivery_id(INTEGER) | courier_id(INTEGER) | distance(DECIMAL) | duration(INTEGER) |
|---|---|---|---|
| 10 | 1 | 5 | 10 |
| 11 | 1 | 6 | 12 |
| 12 | 2 | 3 | 15 |
| 13 | 3 | 4 | 20 |
| 14 | 4 | 10 | 15 |
| 15 | 5 | 5 | 20 |
| 16 | 6 | 2 | 10 |
Expected Output
| courier_id(INTEGER) | city_id(INTEGER) | courier_avg_speed(DECIMAL) | city_avg_speed(DECIMAL) |
|---|---|---|---|
| 1 | 501 | 0.5 | 0.3 |
| 4 | 502 | 0.67 | 0.37 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsAggregationMathematical OperationsJoins
20-25 min
51%