Interview Guides

Average Order Value by Region | Dataford Interview Questions - Dataford - Ace your Interview

Average Order Value by Region

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsGroup By

Also asked at

Problem

Task

Whatnot's operations team wants a quick view of buyer monetization by geography. Write a SQL query to calculate the average order value per user by region.

Requirements

Join orders to users using user_id.
Group results by region.
Return each region and its average order value as avg_order_value.
Exclude orders that are not linked to a valid user region.
Order the final result by avg_order_value descending, then region ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
username	VARCHAR(50)	Whatnot username
region	VARCHAR(50)	User region
signup_date	DATE	Date the user joined

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_total	DECIMAL(10,2)	Total order amount
order_date	DATE	Date of purchase

Sample Data

`users`

user_id	username	region	signup_date
1	seller_sam	West	2024-01-10
2	vintage_via	East	2024-01-12
3	card_king	South	2024-02-01
4	toy_town	West	2024-02-05

`orders`

order_id	user_id	order_total	order_date
101	2	80.00	2024-03-01
102	1	120.00	2024-03-03
103	4	60.00	2024-03-04
104	2	40.00	2024-03-05

Expected Output

region	avg_order_value
North	200.00
West	90.00
East	60.00
South	20.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(50)	Whatnot username
region	VARCHAR(50)	User region
signup_date	DATE	Date the user joined Whatnot

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_total	DECIMAL(10,2)	Total order amount
order_date	DATE	Date of purchase

Sample Data

Example 1

Input

{"users":[[1,"seller_sam","West","2024-01-10"],[2,"vintage_via","East","2024-01-12"],[3,"card_king","South","2024-02-01"],[4,"toy_town","West","2024-02-05"],[5,"lux_lane","North","2024-02-11"],[6,"stream_star",null,"2024-02-15"],[7,"deal_drew","East","2024-03-01"],[8,"mint_maya","North","2024-03-03"],[9,"retro_ray","Central","2024-03-10"],["1","rare_rina","Midwest","2024-01-28"],["2","trade_tess","Texas","2024-02-07"],["3","trade_tess","Northeast","2024-01-22"],["4","showcase_seo","South","2024-

Output[["Texas","436.00"],["North","200.00"],["Central","162.00"],["West","90.00"],["East","60.00"],["South","20.00"]]

Problem

Task

Whatnot's operations team wants a quick view of buyer monetization by geography. Write a SQL query to calculate the average order value per user by region.

Requirements

Join orders to users using user_id.
Group results by region.
Return each region and its average order value as avg_order_value.
Exclude orders that are not linked to a valid user region.
Order the final result by avg_order_value descending, then region ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
username	VARCHAR(50)	Whatnot username
region	VARCHAR(50)	User region
signup_date	DATE	Date the user joined

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_total	DECIMAL(10,2)	Total order amount
order_date	DATE	Date of purchase

Sample Data

`users`

user_id	username	region	signup_date
1	seller_sam	West	2024-01-10
2	vintage_via	East	2024-01-12
3	card_king	South	2024-02-01
4	toy_town	West	2024-02-05

`orders`

order_id	user_id	order_total	order_date
101	2	80.00	2024-03-01
102	1	120.00	2024-03-03
103	4	60.00	2024-03-04
104	2	40.00	2024-03-05

Expected Output

region	avg_order_value
North	200.00
West	90.00
East	60.00
South	20.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(50)	Whatnot username
region	VARCHAR(50)	User region
signup_date	DATE	Date the user joined Whatnot

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_total	DECIMAL(10,2)	Total order amount
order_date	DATE	Date of purchase

Sample Data

Example 1

Input

{"users":[[1,"seller_sam","West","2024-01-10"],[2,"vintage_via","East","2024-01-12"],[3,"card_king","South","2024-02-01"],[4,"toy_town","West","2024-02-05"],[5,"lux_lane","North","2024-02-11"],[6,"stream_star",null,"2024-02-15"],[7,"deal_drew","East","2024-03-01"],[8,"mint_maya","North","2024-03-03"],[9,"retro_ray","Central","2024-03-10"],["1","rare_rina","Midwest","2024-01-28"],["2","trade_tess","Texas","2024-02-07"],["3","trade_tess","Northeast","2024-01-22"],["4","showcase_seo","South","2024-

Output[["Texas","436.00"],["North","200.00"],["Central","162.00"],["West","90.00"],["East","60.00"],["South","20.00"]]

Practice SQL

PostgreSQL

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

Up next

IAverage Delivery Time by RegionEasy ATop Revenue Region by QuarterMedium

Top Transaction Users by CountryMedium

Next question

Average Order Value by Region

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsGroup By

Also asked at

Problem

Task

Whatnot's operations team wants a quick view of buyer monetization by geography. Write a SQL query to calculate the average order value per user by region.

Requirements

Join orders to users using user_id.
Group results by region.
Return each region and its average order value as avg_order_value.
Exclude orders that are not linked to a valid user region.
Order the final result by avg_order_value descending, then region ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
username	VARCHAR(50)	Whatnot username
region	VARCHAR(50)	User region
signup_date	DATE	Date the user joined

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_total	DECIMAL(10,2)	Total order amount
order_date	DATE	Date of purchase

Sample Data

`users`

user_id	username	region	signup_date
1	seller_sam	West	2024-01-10
2	vintage_via	East	2024-01-12
3	card_king	South	2024-02-01
4	toy_town	West	2024-02-05

`orders`

order_id	user_id	order_total	order_date
101	2	80.00	2024-03-01
102	1	120.00	2024-03-03
103	4	60.00	2024-03-04
104	2	40.00	2024-03-05

Expected Output

region	avg_order_value
North	200.00
West	90.00
East	60.00
South	20.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(50)	Whatnot username
region	VARCHAR(50)	User region
signup_date	DATE	Date the user joined Whatnot

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_total	DECIMAL(10,2)	Total order amount
order_date	DATE	Date of purchase

Sample Data

Example 1

Input

{"users":[[1,"seller_sam","West","2024-01-10"],[2,"vintage_via","East","2024-01-12"],[3,"card_king","South","2024-02-01"],[4,"toy_town","West","2024-02-05"],[5,"lux_lane","North","2024-02-11"],[6,"stream_star",null,"2024-02-15"],[7,"deal_drew","East","2024-03-01"],[8,"mint_maya","North","2024-03-03"],[9,"retro_ray","Central","2024-03-10"],["1","rare_rina","Midwest","2024-01-28"],["2","trade_tess","Texas","2024-02-07"],["3","trade_tess","Northeast","2024-01-22"],["4","showcase_seo","South","2024-

Output[["Texas","436.00"],["North","200.00"],["Central","162.00"],["West","90.00"],["East","60.00"],["South","20.00"]]

Problem

Task

Whatnot's operations team wants a quick view of buyer monetization by geography. Write a SQL query to calculate the average order value per user by region.

Requirements

Join orders to users using user_id.
Group results by region.
Return each region and its average order value as avg_order_value.
Exclude orders that are not linked to a valid user region.
Order the final result by avg_order_value descending, then region ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
username	VARCHAR(50)	Whatnot username
region	VARCHAR(50)	User region
signup_date	DATE	Date the user joined

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_total	DECIMAL(10,2)	Total order amount
order_date	DATE	Date of purchase

Sample Data

`users`

user_id	username	region	signup_date
1	seller_sam	West	2024-01-10
2	vintage_via	East	2024-01-12
3	card_king	South	2024-02-01
4	toy_town	West	2024-02-05

`orders`

order_id	user_id	order_total	order_date
101	2	80.00	2024-03-01
102	1	120.00	2024-03-03
103	4	60.00	2024-03-04
104	2	40.00	2024-03-05

Expected Output

region	avg_order_value
North	200.00
West	90.00
East	60.00
South	20.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(50)	Whatnot username
region	VARCHAR(50)	User region
signup_date	DATE	Date the user joined Whatnot

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_total	DECIMAL(10,2)	Total order amount
order_date	DATE	Date of purchase

Sample Data

Example 1

Input

{"users":[[1,"seller_sam","West","2024-01-10"],[2,"vintage_via","East","2024-01-12"],[3,"card_king","South","2024-02-01"],[4,"toy_town","West","2024-02-05"],[5,"lux_lane","North","2024-02-11"],[6,"stream_star",null,"2024-02-15"],[7,"deal_drew","East","2024-03-01"],[8,"mint_maya","North","2024-03-03"],[9,"retro_ray","Central","2024-03-10"],["1","rare_rina","Midwest","2024-01-28"],["2","trade_tess","Texas","2024-02-07"],["3","trade_tess","Northeast","2024-01-22"],["4","showcase_seo","South","2024-

Output[["Texas","436.00"],["North","200.00"],["Central","162.00"],["West","90.00"],["East","60.00"],["South","20.00"]]

Practice SQL

PostgreSQL

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

Up next

IAverage Delivery Time by RegionEasy ATop Revenue Region by QuarterMedium

Top Transaction Users by CountryMedium

Next question