Top Driver Earnings Per Hour

Medium

SQL & Data Manipulation

Asked at 3 companies3AggregationsRankingDate Functions

Also asked at

Problem

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

Schema

drivers

Column	Type	Description
driver_idPK	INT	Unique identifier for each driver
driver_name	VARCHAR(100)	Driver full name
city	VARCHAR(50)	Driver's primary city
active_status	VARCHAR(20)	Current status of the driver

trips

Column	Type	Description
trip_idPK	INT	Unique identifier for each trip
driver_id	INT	Driver assigned to the trip
trip_status	VARCHAR(20)	Trip completion status
started_at	TIMESTAMP	Trip start timestamp
ended_at	TIMESTAMP	Trip end timestamp
fare_amount	DECIMAL(10,2)	Fare earned from the trip

Sample Data

Example 1

Input

{"trips":[["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","2","completed","2024-05-20 13:00:00","2024-05-20 14:00:00","25.00"],["105","3","completed","2024-05-08 14:00:00","2024-05-08 15:00:00","65.00"],["106","3","completed","2024-05-18 18:00:00","2024-05-18 19:00:00","100.00"],["107","4","cancelled","2024-05-0

Output[]

Problem

Task

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

Schema

drivers

Column	Type	Description
driver_idPK	INT	Unique identifier for each driver
driver_name	VARCHAR(100)	Driver full name
city	VARCHAR(50)	Driver's primary city
active_status	VARCHAR(20)	Current status of the driver

trips

Column	Type	Description
trip_idPK	INT	Unique identifier for each trip
driver_id	INT	Driver assigned to the trip
trip_status	VARCHAR(20)	Trip completion status
started_at	TIMESTAMP	Trip start timestamp
ended_at	TIMESTAMP	Trip end timestamp
fare_amount	DECIMAL(10,2)	Fare earned from the trip

Sample Data

Example 1

Input

{"trips":[["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","2","completed","2024-05-20 13:00:00","2024-05-20 14:00:00","25.00"],["105","3","completed","2024-05-08 14:00:00","2024-05-08 15:00:00","65.00"],["106","3","completed","2024-05-18 18:00:00","2024-05-18 19:00:00","100.00"],["107","4","cancelled","2024-05-0

Output[]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

LCompare Driver Hourly Earnings by ZoneEasy

Average ETA and Outlier TripsEasy

Top City-Pair Revenue by WeekdayMedium

Next question

Task

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