Interview Guides

Average Time to Second Uber Ride | Dataford Interview Questions - Dataford - Ace your Interview

Average Time to Second Uber Ride

Easy

SQL & Data Manipulation

Asked at 1 company1Window FunctionsLag/LeadDate Functions

Also asked at

Problem

Task

Uber wants to understand how quickly new riders come back for a second completed trip. Write a SQL query to calculate the average number of days between a user's first completed ride and second completed ride.

Use only the uber_trips table.

Requirements

Consider only rows where trip_status = 'completed'.
For each rider_id, identify the first and second completed rides by request_ts.
Exclude riders with fewer than 2 completed rides.
Return a single row with the average gap in days, rounded to 2 decimal places.

Table Definition

column	type	description
trip_id	INT	Unique trip identifier
rider_id	INT	Uber rider identifier
city_name	VARCHAR(50)	City where the trip was requested
request_ts	TIMESTAMP	Trip request timestamp
trip_status	VARCHAR(20)	Trip status such as completed or canceled

Sample Data

trip_id	rider_id	city_name	request_ts	trip_status
1005	103	Chicago	2024-01-10 18:00:00	completed
1001	101	San Francisco	2024-01-01 08:00:00	completed
1003	102	New York	2024-01-03 09:00:00	canceled
1002	101	San Francisco	2024-01-05 09:30:00	completed
1009	105	Los Angeles	2024-01-07 08:00:00	completed
1010	105	Los Angeles	2024-01-07 20:00:00	completed

Expected Output

avg_days_between_first_and_second_ride
2.67

Schema

uber_trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
rider_id	INT	Uber rider identifier
city_name	VARCHAR(50)	City where the trip was requested
request_ts	TIMESTAMP	Trip request timestamp
trip_status	VARCHAR(20)	Trip lifecycle status

Sample Data

Example 1

Input

{"uber_trips":[["1005","103","Chicago","2024-01-10 18:00:00","completed"],["1001","101","San Francisco","2024-01-01 08:00:00","completed"],["1003","102","New York","2024-01-03 09:00:00","canceled"],["1002","101","San Francisco","2024-01-05 09:30:00","completed"],["1009","105","Los Angeles","2024-01-07 08:00:00","completed"],["1010","105","Los Angeles","2024-01-07 20:00:00","completed"],["1004","102","New York","2024-01-04 10:00:00","completed"],["1008","104",null,"2024-01-02 12:00:00","completed

Output[["2.19"]]

Problem

Task

Use only the uber_trips table.

Requirements

Consider only rows where trip_status = 'completed'.
For each rider_id, identify the first and second completed rides by request_ts.
Exclude riders with fewer than 2 completed rides.
Return a single row with the average gap in days, rounded to 2 decimal places.

Table Definition

column	type	description
trip_id	INT	Unique trip identifier
rider_id	INT	Uber rider identifier
city_name	VARCHAR(50)	City where the trip was requested
request_ts	TIMESTAMP	Trip request timestamp
trip_status	VARCHAR(20)	Trip status such as completed or canceled

Sample Data

trip_id	rider_id	city_name	request_ts	trip_status
1005	103	Chicago	2024-01-10 18:00:00	completed
1001	101	San Francisco	2024-01-01 08:00:00	completed
1003	102	New York	2024-01-03 09:00:00	canceled
1002	101	San Francisco	2024-01-05 09:30:00	completed
1009	105	Los Angeles	2024-01-07 08:00:00	completed
1010	105	Los Angeles	2024-01-07 20:00:00	completed

Expected Output

avg_days_between_first_and_second_ride
2.67

Schema

uber_trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
rider_id	INT	Uber rider identifier
city_name	VARCHAR(50)	City where the trip was requested
request_ts	TIMESTAMP	Trip request timestamp
trip_status	VARCHAR(20)	Trip lifecycle status

Sample Data

Example 1

Input

{"uber_trips":[["1005","103","Chicago","2024-01-10 18:00:00","completed"],["1001","101","San Francisco","2024-01-01 08:00:00","completed"],["1003","102","New York","2024-01-03 09:00:00","canceled"],["1002","101","San Francisco","2024-01-05 09:30:00","completed"],["1009","105","Los Angeles","2024-01-07 08:00:00","completed"],["1010","105","Los Angeles","2024-01-07 20:00:00","completed"],["1004","102","New York","2024-01-04 10:00:00","completed"],["1008","104",null,"2024-01-02 12:00:00","completed

Output[["2.19"]]

Practice SQL

PostgreSQL

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

Up next

Average Time to Second RideMedium

Average ETA and Outlier TripsEasy

Same-Day Riders and EatersMedium

Next question

Average Time to Second Uber Ride

Easy

SQL & Data Manipulation

Asked at 1 company1Window FunctionsLag/LeadDate Functions

Also asked at

Problem

Task

Use only the uber_trips table.

Requirements

Consider only rows where trip_status = 'completed'.
For each rider_id, identify the first and second completed rides by request_ts.
Exclude riders with fewer than 2 completed rides.
Return a single row with the average gap in days, rounded to 2 decimal places.

Table Definition

column	type	description
trip_id	INT	Unique trip identifier
rider_id	INT	Uber rider identifier
city_name	VARCHAR(50)	City where the trip was requested
request_ts	TIMESTAMP	Trip request timestamp
trip_status	VARCHAR(20)	Trip status such as completed or canceled

Sample Data

trip_id	rider_id	city_name	request_ts	trip_status
1005	103	Chicago	2024-01-10 18:00:00	completed
1001	101	San Francisco	2024-01-01 08:00:00	completed
1003	102	New York	2024-01-03 09:00:00	canceled
1002	101	San Francisco	2024-01-05 09:30:00	completed
1009	105	Los Angeles	2024-01-07 08:00:00	completed
1010	105	Los Angeles	2024-01-07 20:00:00	completed

Expected Output

avg_days_between_first_and_second_ride
2.67

Schema

uber_trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
rider_id	INT	Uber rider identifier
city_name	VARCHAR(50)	City where the trip was requested
request_ts	TIMESTAMP	Trip request timestamp
trip_status	VARCHAR(20)	Trip lifecycle status

Sample Data

Example 1

Input

{"uber_trips":[["1005","103","Chicago","2024-01-10 18:00:00","completed"],["1001","101","San Francisco","2024-01-01 08:00:00","completed"],["1003","102","New York","2024-01-03 09:00:00","canceled"],["1002","101","San Francisco","2024-01-05 09:30:00","completed"],["1009","105","Los Angeles","2024-01-07 08:00:00","completed"],["1010","105","Los Angeles","2024-01-07 20:00:00","completed"],["1004","102","New York","2024-01-04 10:00:00","completed"],["1008","104",null,"2024-01-02 12:00:00","completed

Output[["2.19"]]

Problem

Task

Use only the uber_trips table.

Requirements

Consider only rows where trip_status = 'completed'.
For each rider_id, identify the first and second completed rides by request_ts.
Exclude riders with fewer than 2 completed rides.
Return a single row with the average gap in days, rounded to 2 decimal places.

Table Definition

column	type	description
trip_id	INT	Unique trip identifier
rider_id	INT	Uber rider identifier
city_name	VARCHAR(50)	City where the trip was requested
request_ts	TIMESTAMP	Trip request timestamp
trip_status	VARCHAR(20)	Trip status such as completed or canceled

Sample Data

trip_id	rider_id	city_name	request_ts	trip_status
1005	103	Chicago	2024-01-10 18:00:00	completed
1001	101	San Francisco	2024-01-01 08:00:00	completed
1003	102	New York	2024-01-03 09:00:00	canceled
1002	101	San Francisco	2024-01-05 09:30:00	completed
1009	105	Los Angeles	2024-01-07 08:00:00	completed
1010	105	Los Angeles	2024-01-07 20:00:00	completed

Expected Output

avg_days_between_first_and_second_ride
2.67

Schema

uber_trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
rider_id	INT	Uber rider identifier
city_name	VARCHAR(50)	City where the trip was requested
request_ts	TIMESTAMP	Trip request timestamp
trip_status	VARCHAR(20)	Trip lifecycle status

Sample Data

Example 1

Input

{"uber_trips":[["1005","103","Chicago","2024-01-10 18:00:00","completed"],["1001","101","San Francisco","2024-01-01 08:00:00","completed"],["1003","102","New York","2024-01-03 09:00:00","canceled"],["1002","101","San Francisco","2024-01-05 09:30:00","completed"],["1009","105","Los Angeles","2024-01-07 08:00:00","completed"],["1010","105","Los Angeles","2024-01-07 20:00:00","completed"],["1004","102","New York","2024-01-04 10:00:00","completed"],["1008","104",null,"2024-01-02 12:00:00","completed

Output[["2.19"]]

Practice SQL

PostgreSQL

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

Up next

Average Time to Second RideMedium

Average ETA and Outlier TripsEasy

Same-Day Riders and EatersMedium

Next question