Interview Guides

Average ETA and Outlier Trips | Dataford Interview Questions - Dataford - Ace your Interview

Average ETA and Outlier Trips

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsDate FunctionsData Wrangling

Also asked at

Problem

Task

Uber's Operations team wants a quick quality check on trip ETA data from the Driver app. Write a SQL query to calculate the overall average ETA and identify trips whose ETA is unusually high.

Requirements

Use only rows where eta_minutes is not NULL.
Return two metrics in one result set:
- the overall average ETA across all valid trips
- the number of outlier trips where eta_minutes is greater than 2 times the overall average ETA
Output the columns as avg_eta_minutes and outlier_trip_count.

Because this is an easy question, use a single table only.

Table Definition

trip_eta_events

column	type	description
trip_id	INT	Unique trip identifier
city_name	VARCHAR(50)	City where the trip was requested
rider_surface	VARCHAR(50)	Uber surface where ETA was shown
eta_minutes	DECIMAL(5,2)	Estimated time of arrival in minutes
event_date	DATE	Date of the ETA event

Sample Data

trip_id	city_name	rider_surface	eta_minutes	event_date
1008	Chicago	UberX	18.00	2024-06-04
1002	New York	UberX	7.00	2024-06-01
1005	San Francisco	Uber Reserve	25.00	2024-06-03
1001	New York	UberX	5.00	2024-06-01
1009	Chicago	UberXL	0.00	2024-06-04
1004	San Francisco	UberX	6.00	2024-06-02
1007	Chicago	UberX	NULL	2024-06-03
1003	New York	Uber Black	9.00	2024-06-02
1006	San Francisco	UberX	40.00	2024-06-03
1010	Miami	UberX	8.00	2024-06-05

Expected Output

avg_eta_minutes	outlier_trip_count
13.11	2

Schema

trip_eta_events

Column	Type	Description
trip_idPK	INT	Unique trip identifier
city_name	VARCHAR(50)	City where the trip was requested
rider_surface	VARCHAR(50)	Uber surface or product where ETA was displayed
eta_minutes	DECIMAL(5,2)	Estimated time of arrival in minutes
event_date	DATE	Date of the ETA event

Sample Data

Example 1

Input

{"trip_eta_events":[[1008,"Chicago","UberX","18.00","2024-06-04"],[1002,"New York","UberX","7.00","2024-06-01"],[1005,"San Francisco","Uber Reserve","25.00","2024-06-03"],[1001,"New York","UberX","5.00","2024-06-01"],[1009,"Chicago","UberXL","0.00","2024-06-04"],[1004,"San Francisco","UberX","6.00","2024-06-02"],[1007,"Chicago","UberX",null,"2024-06-03"],[1003,"New York","Uber Black","9.00","2024-06-02"],[1006,"San Francisco","UberX","40.00","2024-06-03"],[1010,"Miami","UberX","8.00","2024-06-05

Output[["20.83","1"]]

Problem

Task

Uber's Operations team wants a quick quality check on trip ETA data from the Driver app. Write a SQL query to calculate the overall average ETA and identify trips whose ETA is unusually high.

Requirements

Use only rows where eta_minutes is not NULL.
Return two metrics in one result set:
- the overall average ETA across all valid trips
- the number of outlier trips where eta_minutes is greater than 2 times the overall average ETA
Output the columns as avg_eta_minutes and outlier_trip_count.

Because this is an easy question, use a single table only.

Table Definition

trip_eta_events

column	type	description
trip_id	INT	Unique trip identifier
city_name	VARCHAR(50)	City where the trip was requested
rider_surface	VARCHAR(50)	Uber surface where ETA was shown
eta_minutes	DECIMAL(5,2)	Estimated time of arrival in minutes
event_date	DATE	Date of the ETA event

Sample Data

trip_id	city_name	rider_surface	eta_minutes	event_date
1008	Chicago	UberX	18.00	2024-06-04
1002	New York	UberX	7.00	2024-06-01
1005	San Francisco	Uber Reserve	25.00	2024-06-03
1001	New York	UberX	5.00	2024-06-01
1009	Chicago	UberXL	0.00	2024-06-04
1004	San Francisco	UberX	6.00	2024-06-02
1007	Chicago	UberX	NULL	2024-06-03
1003	New York	Uber Black	9.00	2024-06-02
1006	San Francisco	UberX	40.00	2024-06-03
1010	Miami	UberX	8.00	2024-06-05

Expected Output

avg_eta_minutes	outlier_trip_count
13.11	2

Schema

trip_eta_events

Column	Type	Description
trip_idPK	INT	Unique trip identifier
city_name	VARCHAR(50)	City where the trip was requested
rider_surface	VARCHAR(50)	Uber surface or product where ETA was displayed
eta_minutes	DECIMAL(5,2)	Estimated time of arrival in minutes
event_date	DATE	Date of the ETA event

Sample Data

Example 1

Input

{"trip_eta_events":[[1008,"Chicago","UberX","18.00","2024-06-04"],[1002,"New York","UberX","7.00","2024-06-01"],[1005,"San Francisco","Uber Reserve","25.00","2024-06-03"],[1001,"New York","UberX","5.00","2024-06-01"],[1009,"Chicago","UberXL","0.00","2024-06-04"],[1004,"San Francisco","UberX","6.00","2024-06-02"],[1007,"Chicago","UberX",null,"2024-06-03"],[1003,"New York","Uber Black","9.00","2024-06-02"],[1006,"San Francisco","UberX","40.00","2024-06-03"],[1010,"Miami","UberX","8.00","2024-06-05

Output[["20.83","1"]]

Practice SQL

PostgreSQL

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

Up next

Average Time to Second Uber RideEasy

Top Driver Earnings Per HourMedium LCompare Driver Hourly Earnings by ZoneEasy

Next question

Average ETA and Outlier Trips

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsDate FunctionsData Wrangling

Also asked at

Problem

Task

Uber's Operations team wants a quick quality check on trip ETA data from the Driver app. Write a SQL query to calculate the overall average ETA and identify trips whose ETA is unusually high.

Requirements

Use only rows where eta_minutes is not NULL.
Return two metrics in one result set:
- the overall average ETA across all valid trips
- the number of outlier trips where eta_minutes is greater than 2 times the overall average ETA
Output the columns as avg_eta_minutes and outlier_trip_count.

Because this is an easy question, use a single table only.

Table Definition

trip_eta_events

column	type	description
trip_id	INT	Unique trip identifier
city_name	VARCHAR(50)	City where the trip was requested
rider_surface	VARCHAR(50)	Uber surface where ETA was shown
eta_minutes	DECIMAL(5,2)	Estimated time of arrival in minutes
event_date	DATE	Date of the ETA event

Sample Data

trip_id	city_name	rider_surface	eta_minutes	event_date
1008	Chicago	UberX	18.00	2024-06-04
1002	New York	UberX	7.00	2024-06-01
1005	San Francisco	Uber Reserve	25.00	2024-06-03
1001	New York	UberX	5.00	2024-06-01
1009	Chicago	UberXL	0.00	2024-06-04
1004	San Francisco	UberX	6.00	2024-06-02
1007	Chicago	UberX	NULL	2024-06-03
1003	New York	Uber Black	9.00	2024-06-02
1006	San Francisco	UberX	40.00	2024-06-03
1010	Miami	UberX	8.00	2024-06-05

Expected Output

avg_eta_minutes	outlier_trip_count
13.11	2

Schema

trip_eta_events

Column	Type	Description
trip_idPK	INT	Unique trip identifier
city_name	VARCHAR(50)	City where the trip was requested
rider_surface	VARCHAR(50)	Uber surface or product where ETA was displayed
eta_minutes	DECIMAL(5,2)	Estimated time of arrival in minutes
event_date	DATE	Date of the ETA event

Sample Data

Example 1

Input

{"trip_eta_events":[[1008,"Chicago","UberX","18.00","2024-06-04"],[1002,"New York","UberX","7.00","2024-06-01"],[1005,"San Francisco","Uber Reserve","25.00","2024-06-03"],[1001,"New York","UberX","5.00","2024-06-01"],[1009,"Chicago","UberXL","0.00","2024-06-04"],[1004,"San Francisco","UberX","6.00","2024-06-02"],[1007,"Chicago","UberX",null,"2024-06-03"],[1003,"New York","Uber Black","9.00","2024-06-02"],[1006,"San Francisco","UberX","40.00","2024-06-03"],[1010,"Miami","UberX","8.00","2024-06-05

Output[["20.83","1"]]

Problem

Task

Uber's Operations team wants a quick quality check on trip ETA data from the Driver app. Write a SQL query to calculate the overall average ETA and identify trips whose ETA is unusually high.

Requirements

Use only rows where eta_minutes is not NULL.
Return two metrics in one result set:
- the overall average ETA across all valid trips
- the number of outlier trips where eta_minutes is greater than 2 times the overall average ETA
Output the columns as avg_eta_minutes and outlier_trip_count.

Because this is an easy question, use a single table only.

Table Definition

trip_eta_events

column	type	description
trip_id	INT	Unique trip identifier
city_name	VARCHAR(50)	City where the trip was requested
rider_surface	VARCHAR(50)	Uber surface where ETA was shown
eta_minutes	DECIMAL(5,2)	Estimated time of arrival in minutes
event_date	DATE	Date of the ETA event

Sample Data

trip_id	city_name	rider_surface	eta_minutes	event_date
1008	Chicago	UberX	18.00	2024-06-04
1002	New York	UberX	7.00	2024-06-01
1005	San Francisco	Uber Reserve	25.00	2024-06-03
1001	New York	UberX	5.00	2024-06-01
1009	Chicago	UberXL	0.00	2024-06-04
1004	San Francisco	UberX	6.00	2024-06-02
1007	Chicago	UberX	NULL	2024-06-03
1003	New York	Uber Black	9.00	2024-06-02
1006	San Francisco	UberX	40.00	2024-06-03
1010	Miami	UberX	8.00	2024-06-05

Expected Output

avg_eta_minutes	outlier_trip_count
13.11	2

Schema

trip_eta_events

Column	Type	Description
trip_idPK	INT	Unique trip identifier
city_name	VARCHAR(50)	City where the trip was requested
rider_surface	VARCHAR(50)	Uber surface or product where ETA was displayed
eta_minutes	DECIMAL(5,2)	Estimated time of arrival in minutes
event_date	DATE	Date of the ETA event

Sample Data

Example 1

Input

{"trip_eta_events":[[1008,"Chicago","UberX","18.00","2024-06-04"],[1002,"New York","UberX","7.00","2024-06-01"],[1005,"San Francisco","Uber Reserve","25.00","2024-06-03"],[1001,"New York","UberX","5.00","2024-06-01"],[1009,"Chicago","UberXL","0.00","2024-06-04"],[1004,"San Francisco","UberX","6.00","2024-06-02"],[1007,"Chicago","UberX",null,"2024-06-03"],[1003,"New York","Uber Black","9.00","2024-06-02"],[1006,"San Francisco","UberX","40.00","2024-06-03"],[1010,"Miami","UberX","8.00","2024-06-05

Output[["20.83","1"]]

Practice SQL

PostgreSQL

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

Up next

Average Time to Second Uber RideEasy

Top Driver Earnings Per HourMedium LCompare Driver Hourly Earnings by ZoneEasy

Next question