Task
RideNow wants to identify its highest-efficiency drivers based on earnings per hour in the previous calendar month. Write a SQL query to return the drivers whose earnings per hour place them in the top 10% for that period.
Requirements
- Use only trips that were completed in the previous calendar month.
- For each driver, calculate:
- total earnings as the sum of
fare_amount
- total hours worked as the sum of trip duration in hours
- earnings per hour = total earnings / total hours worked
- Exclude drivers whose total worked hours are
0 or NULL.
- Return only drivers in the top 10% by earnings per hour using a ranking or percentile approach.
- Output
driver_id, driver_name, total_earnings, total_hours, and earnings_per_hour, ordered by earnings_per_hour descending.
Table Definitions
drivers
| column | type | description |
|---|
| driver_id | INT | Primary key for each driver |
| driver_name | VARCHAR(100) | Driver full name |
| city | VARCHAR(50) | Driver's primary city |
| active_status | VARCHAR(20) | Current driver status |
trips
| column | type | description |
|---|
| trip_id | INT | Primary key for each trip |
| driver_id | INT | Driver assigned to the trip |
| trip_status | VARCHAR(20) | Trip status such as completed or cancelled |
| started_at | TIMESTAMP | Trip start timestamp |
| ended_at | TIMESTAMP | Trip end timestamp |
| fare_amount | DECIMAL(10,2) | Fare earned for the trip |
Sample Data
drivers
| driver_id | driver_name | city | active_status |
|---|
| 1 | Alice Kim | Chicago | active |
| 2 | Ben Ortiz | Chicago | active |
| 3 | Carla Singh | Boston | active |
| 4 | Diego Park | Boston | inactive |
| 5 | Elena Rossi | Seattle | active |
trips
| trip_id | driver_id | trip_status | started_at | ended_at | fare_amount |
|---|
| 101 | 1 | completed | 2024-05-03 08:00:00 | 2024-05-03 09:00:00 | 40.00 |
| 102 | 1 | completed | 2024-05-10 10:00:00 | 2024-05-10 11:30:00 | 75.00 |
| 103 | 2 | completed | 2024-05-06 09:00:00 | 2024-05-06 11:00:00 | 70.00 |
| 104 | 3 | completed | 2024-05-08 14:00:00 | 2024-05-08 15:00:00 | 65.00 |
| 105 | 4 | cancelled | 2024-05-09 12:00:00 | 2024-05-09 12:30:00 | 0.00 |
Expected Output
| driver_id | driver_name | total_earnings | total_hours | earnings_per_hour |
|---|
| 3 | Carla Singh | 165.00 | 2.00 | 82.50 |
