Interview Guides

Average Delivery Time by Region | Dataford Interview Questions - Dataford - Ace your Interview

Average Delivery Time by Region

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsDate Functions

Also asked at

Problem

Task

QuickCart tracks delivery performance across shopper regions. Write a SQL query to find the average delivery time in hours per region for the last month using the orders and shoppers tables.

Requirements

Join orders to shoppers using shopper_id
Only include orders delivered in the previous calendar month based on delivered_at
Exclude rows where delivered_at or placed_at is NULL
Return region and avg_delivery_hours, rounded to 2 decimal places
Sort results by avg_delivery_hours descending, then region ascending

Table Definitions

`orders`

column	type	description
order_id	INT	Unique order identifier
shopper_id	INT	Shopper who placed the order
placed_at	TIMESTAMP	Time the order was placed
delivered_at	TIMESTAMP	Time the order was delivered
order_status	VARCHAR(20)	Current order status

`shoppers`

column	type	description
shopper_id	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper name
region	VARCHAR(50)	Shopper region
signup_channel	VARCHAR(50)	Acquisition channel

Sample Data

`orders`

order_id	shopper_id	placed_at	delivered_at	order_status
101	1	2024-05-03 09:00:00	2024-05-03 11:30:00	delivered
102	2	2024-05-10 14:00:00	2024-05-10 18:00:00	delivered
103	3	2024-05-12 08:15:00	2024-05-12 09:00:00	delivered
104	1	2024-04-28 19:00:00	2024-05-01 08:00:00	delivered
105	4	2024-05-20 12:00:00	NULL	in_transit
106	5	2024-05-22 10:00:00	2024-05-22 16:30:00	delivered

`shoppers`

shopper_id	shopper_name	region	signup_channel
1	Ava Patel	West	organic
2	Ben Carter	East	paid_search
3	Chloe Kim	South	referral
4	Daniel Reed	North	organic
5	Elena Cruz	East	affiliate
6	Farah Ali	NULL	referral

Expected Output

region	avg_delivery_hours
West	13.25
East	5.25
North	4.00
South	0.75

Schema

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
shopper_id	INT	References the shopper who placed the order
placed_at	TIMESTAMP	Timestamp when the order was placed
delivered_at	TIMESTAMP	Timestamp when the order was delivered
order_status	VARCHAR(20)	Current order status

shoppers

Column	Type	Description
shopper_idPK	INT	Primary key for each shopper
shopper_name	VARCHAR(100)	Full name of the shopper
region	VARCHAR(50)	Region assigned to the shopper
signup_channel	VARCHAR(50)	Marketing channel used during signup

Sample Data

Example 1

Input

{"orders":[[101,1,"2024-05-03 09:00:00","2024-05-03 11:30:00","delivered"],[102,2,"2024-05-10 14:00:00","2024-05-10 18:00:00","delivered"],[103,3,"2024-05-12 08:15:00","2024-05-12 09:00:00","delivered"],[104,1,"2024-04-28 19:00:00","2024-05-01 08:00:00","delivered"],[105,4,"2024-05-20 12:00:00",null,"in_transit"],[106,5,"2024-05-22 10:00:00","2024-05-22 16:30:00","delivered"],[107,4,"2024-05-25 09:00:00","2024-05-25 13:00:00","delivered"],[108,2,"2024-04-15 10:00:00","2024-04-15 12:00:00","deliv

Output[]

Problem

Task

QuickCart tracks delivery performance across shopper regions. Write a SQL query to find the average delivery time in hours per region for the last month using the orders and shoppers tables.

Requirements

Join orders to shoppers using shopper_id
Only include orders delivered in the previous calendar month based on delivered_at
Exclude rows where delivered_at or placed_at is NULL
Return region and avg_delivery_hours, rounded to 2 decimal places
Sort results by avg_delivery_hours descending, then region ascending

Table Definitions

`orders`

column	type	description
order_id	INT	Unique order identifier
shopper_id	INT	Shopper who placed the order
placed_at	TIMESTAMP	Time the order was placed
delivered_at	TIMESTAMP	Time the order was delivered
order_status	VARCHAR(20)	Current order status

`shoppers`

column	type	description
shopper_id	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper name
region	VARCHAR(50)	Shopper region
signup_channel	VARCHAR(50)	Acquisition channel

Sample Data

`orders`

order_id	shopper_id	placed_at	delivered_at	order_status
101	1	2024-05-03 09:00:00	2024-05-03 11:30:00	delivered
102	2	2024-05-10 14:00:00	2024-05-10 18:00:00	delivered
103	3	2024-05-12 08:15:00	2024-05-12 09:00:00	delivered
104	1	2024-04-28 19:00:00	2024-05-01 08:00:00	delivered
105	4	2024-05-20 12:00:00	NULL	in_transit
106	5	2024-05-22 10:00:00	2024-05-22 16:30:00	delivered

`shoppers`

shopper_id	shopper_name	region	signup_channel
1	Ava Patel	West	organic
2	Ben Carter	East	paid_search
3	Chloe Kim	South	referral
4	Daniel Reed	North	organic
5	Elena Cruz	East	affiliate
6	Farah Ali	NULL	referral

Expected Output

region	avg_delivery_hours
West	13.25
East	5.25
North	4.00
South	0.75

Schema

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
shopper_id	INT	References the shopper who placed the order
placed_at	TIMESTAMP	Timestamp when the order was placed
delivered_at	TIMESTAMP	Timestamp when the order was delivered
order_status	VARCHAR(20)	Current order status

shoppers

Column	Type	Description
shopper_idPK	INT	Primary key for each shopper
shopper_name	VARCHAR(100)	Full name of the shopper
region	VARCHAR(50)	Region assigned to the shopper
signup_channel	VARCHAR(50)	Marketing channel used during signup

Sample Data

Example 1

Input

{"orders":[[101,1,"2024-05-03 09:00:00","2024-05-03 11:30:00","delivered"],[102,2,"2024-05-10 14:00:00","2024-05-10 18:00:00","delivered"],[103,3,"2024-05-12 08:15:00","2024-05-12 09:00:00","delivered"],[104,1,"2024-04-28 19:00:00","2024-05-01 08:00:00","delivered"],[105,4,"2024-05-20 12:00:00",null,"in_transit"],[106,5,"2024-05-22 10:00:00","2024-05-22 16:30:00","delivered"],[107,4,"2024-05-25 09:00:00","2024-05-25 13:00:00","delivered"],[108,2,"2024-04-15 10:00:00","2024-04-15 12:00:00","deliv

Output[]

Practice SQL

PostgreSQL

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

Up next

WAverage Order Value by RegionEasy Find Peak Delivery HourEasy

Average ETA and Outlier TripsEasy

Next question

Average Delivery Time by Region

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsDate Functions

Also asked at

Problem

Task

QuickCart tracks delivery performance across shopper regions. Write a SQL query to find the average delivery time in hours per region for the last month using the orders and shoppers tables.

Requirements

Join orders to shoppers using shopper_id
Only include orders delivered in the previous calendar month based on delivered_at
Exclude rows where delivered_at or placed_at is NULL
Return region and avg_delivery_hours, rounded to 2 decimal places
Sort results by avg_delivery_hours descending, then region ascending

Table Definitions

`orders`

column	type	description
order_id	INT	Unique order identifier
shopper_id	INT	Shopper who placed the order
placed_at	TIMESTAMP	Time the order was placed
delivered_at	TIMESTAMP	Time the order was delivered
order_status	VARCHAR(20)	Current order status

`shoppers`

column	type	description
shopper_id	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper name
region	VARCHAR(50)	Shopper region
signup_channel	VARCHAR(50)	Acquisition channel

Sample Data

`orders`

order_id	shopper_id	placed_at	delivered_at	order_status
101	1	2024-05-03 09:00:00	2024-05-03 11:30:00	delivered
102	2	2024-05-10 14:00:00	2024-05-10 18:00:00	delivered
103	3	2024-05-12 08:15:00	2024-05-12 09:00:00	delivered
104	1	2024-04-28 19:00:00	2024-05-01 08:00:00	delivered
105	4	2024-05-20 12:00:00	NULL	in_transit
106	5	2024-05-22 10:00:00	2024-05-22 16:30:00	delivered

`shoppers`

shopper_id	shopper_name	region	signup_channel
1	Ava Patel	West	organic
2	Ben Carter	East	paid_search
3	Chloe Kim	South	referral
4	Daniel Reed	North	organic
5	Elena Cruz	East	affiliate
6	Farah Ali	NULL	referral

Expected Output

region	avg_delivery_hours
West	13.25
East	5.25
North	4.00
South	0.75

Schema

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
shopper_id	INT	References the shopper who placed the order
placed_at	TIMESTAMP	Timestamp when the order was placed
delivered_at	TIMESTAMP	Timestamp when the order was delivered
order_status	VARCHAR(20)	Current order status

shoppers

Column	Type	Description
shopper_idPK	INT	Primary key for each shopper
shopper_name	VARCHAR(100)	Full name of the shopper
region	VARCHAR(50)	Region assigned to the shopper
signup_channel	VARCHAR(50)	Marketing channel used during signup

Sample Data

Example 1

Input

{"orders":[[101,1,"2024-05-03 09:00:00","2024-05-03 11:30:00","delivered"],[102,2,"2024-05-10 14:00:00","2024-05-10 18:00:00","delivered"],[103,3,"2024-05-12 08:15:00","2024-05-12 09:00:00","delivered"],[104,1,"2024-04-28 19:00:00","2024-05-01 08:00:00","delivered"],[105,4,"2024-05-20 12:00:00",null,"in_transit"],[106,5,"2024-05-22 10:00:00","2024-05-22 16:30:00","delivered"],[107,4,"2024-05-25 09:00:00","2024-05-25 13:00:00","delivered"],[108,2,"2024-04-15 10:00:00","2024-04-15 12:00:00","deliv

Output[]

Problem

Task

QuickCart tracks delivery performance across shopper regions. Write a SQL query to find the average delivery time in hours per region for the last month using the orders and shoppers tables.

Requirements

Join orders to shoppers using shopper_id
Only include orders delivered in the previous calendar month based on delivered_at
Exclude rows where delivered_at or placed_at is NULL
Return region and avg_delivery_hours, rounded to 2 decimal places
Sort results by avg_delivery_hours descending, then region ascending

Table Definitions

`orders`

column	type	description
order_id	INT	Unique order identifier
shopper_id	INT	Shopper who placed the order
placed_at	TIMESTAMP	Time the order was placed
delivered_at	TIMESTAMP	Time the order was delivered
order_status	VARCHAR(20)	Current order status

`shoppers`

column	type	description
shopper_id	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper name
region	VARCHAR(50)	Shopper region
signup_channel	VARCHAR(50)	Acquisition channel

Sample Data

`orders`

order_id	shopper_id	placed_at	delivered_at	order_status
101	1	2024-05-03 09:00:00	2024-05-03 11:30:00	delivered
102	2	2024-05-10 14:00:00	2024-05-10 18:00:00	delivered
103	3	2024-05-12 08:15:00	2024-05-12 09:00:00	delivered
104	1	2024-04-28 19:00:00	2024-05-01 08:00:00	delivered
105	4	2024-05-20 12:00:00	NULL	in_transit
106	5	2024-05-22 10:00:00	2024-05-22 16:30:00	delivered

`shoppers`

shopper_id	shopper_name	region	signup_channel
1	Ava Patel	West	organic
2	Ben Carter	East	paid_search
3	Chloe Kim	South	referral
4	Daniel Reed	North	organic
5	Elena Cruz	East	affiliate
6	Farah Ali	NULL	referral

Expected Output

region	avg_delivery_hours
West	13.25
East	5.25
North	4.00
South	0.75

Schema

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
shopper_id	INT	References the shopper who placed the order
placed_at	TIMESTAMP	Timestamp when the order was placed
delivered_at	TIMESTAMP	Timestamp when the order was delivered
order_status	VARCHAR(20)	Current order status

shoppers

Column	Type	Description
shopper_idPK	INT	Primary key for each shopper
shopper_name	VARCHAR(100)	Full name of the shopper
region	VARCHAR(50)	Region assigned to the shopper
signup_channel	VARCHAR(50)	Marketing channel used during signup

Sample Data

Example 1

Input

{"orders":[[101,1,"2024-05-03 09:00:00","2024-05-03 11:30:00","delivered"],[102,2,"2024-05-10 14:00:00","2024-05-10 18:00:00","delivered"],[103,3,"2024-05-12 08:15:00","2024-05-12 09:00:00","delivered"],[104,1,"2024-04-28 19:00:00","2024-05-01 08:00:00","delivered"],[105,4,"2024-05-20 12:00:00",null,"in_transit"],[106,5,"2024-05-22 10:00:00","2024-05-22 16:30:00","delivered"],[107,4,"2024-05-25 09:00:00","2024-05-25 13:00:00","delivered"],[108,2,"2024-04-15 10:00:00","2024-04-15 12:00:00","deliv

Output[]

Practice SQL

PostgreSQL

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

Up next

WAverage Order Value by RegionEasy Find Peak Delivery HourEasy

Average ETA and Outlier TripsEasy

Next question