Task
RideNow wants a simple comparison of driver pay efficiency across service areas. Write a SQL query to calculate the average earnings per hour for rides in the Downtown zone versus the Suburbs zone.
Requirements
- Use only rows where
zone is Downtown or Suburbs.
- For each zone, calculate average earnings per hour as
SUM(earnings) / SUM(ride_minutes / 60.0).
- Exclude rows where
ride_minutes is NULL or less than or equal to 0.
- Return
zone and avg_earnings_per_hour, ordered by avg_earnings_per_hour descending.
Table Definition
driver_rides
| column | type | description |
|---|
| ride_id | INT | Unique ride identifier |
| driver_id | INT | Driver identifier |
| zone | VARCHAR(50) | Service zone for the ride |
| ride_minutes | INT | Ride duration in minutes |
| earnings | DECIMAL(10,2) | Driver earnings for the ride |
| ride_date | DATE | Date of the ride |
Sample Data
| ride_id | driver_id | zone | ride_minutes | earnings | ride_date |
|---|
| 1 | 201 | Suburbs | 45 | 30.00 | 2024-06-03 |
| 2 | 202 | Downtown | 30 | 28.00 | 2024-06-01 |
| 3 | 201 | Downtown | 60 | 42.00 | 2024-06-02 |
| 4 | 203 | Airport | 50 | 40.00 | 2024-06-01 |
| 5 | 204 | Suburbs | 0 | 12.00 | 2024-06-02 |
| 6 | 205 | Downtown | NULL | 18.00 | 2024-06-04 |
Expected Output
| zone | avg_earnings_per_hour |
|---|
| Downtown | 50.00 |
| Suburbs | 38.00 |
