Average Delivery Time by Region | Dataford Interview Questions

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

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

Your question is Average Delivery Time by Region. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem