Interview Guides

Compare Driver Hourly Earnings by Zone | Dataford Interview Questions - Dataford - Ace your Interview

Compare Driver Hourly Earnings by Zone

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsData Wrangling

Also asked at

Problem

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

Schema

driver_rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
driver_id	INT	Driver identifier
zone	VARCHAR(50)	Service zone where the ride occurred
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

Example 1

Input

{"driver_rides":[[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"],[7,206,"Suburbs",30,"21.00","2024-06-01"],[8,207,"Downtown",90,"80.00","2024-06-03"],[9,208,"Suburbs",60,"38.00","2024-06-04"],[10,209,null,40,"25.00","2024-06-02"],["1","74","East End","23","70","2024-05-29"],["2","36","Hillcr

Output[["Downtown","50.00"],["Suburbs","39.56"]]

Problem

Task

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

Schema

driver_rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
driver_id	INT	Driver identifier
zone	VARCHAR(50)	Service zone where the ride occurred
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

Example 1

Input

{"driver_rides":[[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"],[7,206,"Suburbs",30,"21.00","2024-06-01"],[8,207,"Downtown",90,"80.00","2024-06-03"],[9,208,"Suburbs",60,"38.00","2024-06-04"],[10,209,null,40,"25.00","2024-06-02"],["1","74","East End","23","70","2024-05-29"],["2","36","Hillcr

Output[["Downtown","50.00"],["Suburbs","39.56"]]

Practice SQL

PostgreSQL

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

Up next

Top Driver Earnings Per HourMedium

Average ETA and Outlier TripsEasy

Top City-Pair Revenue by WeekdayMedium

Next question

Compare Driver Hourly Earnings by Zone

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsData Wrangling

Also asked at

Problem

Task

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

Schema

driver_rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
driver_id	INT	Driver identifier
zone	VARCHAR(50)	Service zone where the ride occurred
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

Example 1

Input

{"driver_rides":[[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"],[7,206,"Suburbs",30,"21.00","2024-06-01"],[8,207,"Downtown",90,"80.00","2024-06-03"],[9,208,"Suburbs",60,"38.00","2024-06-04"],[10,209,null,40,"25.00","2024-06-02"],["1","74","East End","23","70","2024-05-29"],["2","36","Hillcr

Output[["Downtown","50.00"],["Suburbs","39.56"]]

Problem

Task

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

Schema

driver_rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
driver_id	INT	Driver identifier
zone	VARCHAR(50)	Service zone where the ride occurred
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

Example 1

Input

{"driver_rides":[[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"],[7,206,"Suburbs",30,"21.00","2024-06-01"],[8,207,"Downtown",90,"80.00","2024-06-03"],[9,208,"Suburbs",60,"38.00","2024-06-04"],[10,209,null,40,"25.00","2024-06-02"],["1","74","East End","23","70","2024-05-29"],["2","36","Hillcr

Output[["Downtown","50.00"],["Suburbs","39.56"]]

Practice SQL

PostgreSQL

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

Up next

Top Driver Earnings Per HourMedium

Average ETA and Outlier TripsEasy

Top City-Pair Revenue by WeekdayMedium

Next question