Interview Guides

Monthly Customer Spend and Rank | Dataford Interview Questions - Dataford - Ace your Interview

Monthly Customer Spend and Rank

Medium

SQL & Data Manipulation

Asked at 19 companies19JoinsWindow FunctionsCTEs

Also asked at

Problem

Task

NovaCart wants a monthly customer performance report built from raw transactional tables. Write a PostgreSQL query from scratch to return customer-level metrics for January 2024.

Requirements

Return one row per customer who placed at least one completed order in January 2024.
Show each customer's total completed orders, total revenue, average order value, and a spending tier using CASE WHEN:
- High for revenue >= 500
- Medium for revenue between 200 and 499.99
- Low for revenue < 200
Rank customers by total revenue within their region using ROW_NUMBER().
Only include customers with at least 2 completed orders in January 2024.
Sort by region, then revenue descending, then customer name.

Table Definitions

`customers`

column	type	description
customer_id	INT	Primary key
customer_name	VARCHAR(100)	Customer name
region	VARCHAR(50)	Customer region
signup_date	DATE	Account creation date

`orders`

column	type	description
order_id	INT	Primary key
customer_id	INT	References customers
order_date	DATE	Order date
status	VARCHAR(20)	Order status
shipping_fee	NUMERIC(10,2)	Shipping charge

`order_items`

column	type	description
order_item_id	INT	Primary key
order_id	INT	References orders
product_name	VARCHAR(100)	Product name
quantity	INT	Quantity purchased
unit_price	NUMERIC(10,2)	Price per unit

Sample Data

`customers`

customer_id	customer_name	region	signup_date
3	Carla	East	2023-11-20
1	Alice	North	2023-12-15
8	Hank	West	2024-01-10
5	Eva	South	2023-08-01

`orders`

order_id	customer_id	order_date	status	shipping_fee
104	2	2024-01-20	completed	0.00
101	1	2024-01-05	completed	10.00
110	7	2024-01-21	completed	4.00
106	3	2024-01-25	completed	12.00

`order_items`

order_item_id	order_id	product_name	quantity	unit_price
1008	105	Keyboard	1	80.00
1001	101	Laptop Sleeve	2	25.00
1014	110	Mouse	1	25.00
1005	103	Dock	1	150.00

Expected Output

customer_id	customer_name	region	completed_orders	total_revenue	avg_order_value	spending_tier	regional_rank
3	Carla	East	2	667.00	333.50	High	1
1	Alice	North	2	330.00	165.00	Medium	1
7	Gina	North	2	90.00	45.00	Low	2

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
region	VARCHAR(50)	Customer sales region
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status
shipping_fee	NUMERIC(10,2)	Shipping fee charged for the order

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
order_id	INT	Order associated with the item
product_name	VARCHAR(100)	Product name
quantity	INT	Units purchased
unit_price	NUMERIC(10,2)	Price per unit

Sample Data

Example 1

Input

{"orders":[[104,2,"2024-01-20","completed","0.00"],[101,1,"2024-01-05","completed","10.00"],[110,7,"2024-01-21","completed","4.00"],[106,3,"2024-01-25","completed","12.00"],[108,5,"2024-01-18","cancelled","5.00"],[103,1,"2024-01-28","completed",null],[111,8,"2024-02-01","completed","6.00"],[102,1,"2024-01-15","pending","5.00"],[109,6,"2024-01-30","completed","0.00"],[105,3,"2024-01-08","completed","7.00"],[107,4,"2023-12-31","completed","3.00"],[112,999,"2024-01-12","completed","2.00"],[113,7,"2

Output

[["3","Carla","East","2","667.00","333.50","High","1"],["1","Alice","North","2","370.00","185.00","Medium","1"],["7","Gina","North","2","98.00","49.00","Low","2"]]

Problem

Task

NovaCart wants a monthly customer performance report built from raw transactional tables. Write a PostgreSQL query from scratch to return customer-level metrics for January 2024.

Requirements

Return one row per customer who placed at least one completed order in January 2024.
Show each customer's total completed orders, total revenue, average order value, and a spending tier using CASE WHEN:
- High for revenue >= 500
- Medium for revenue between 200 and 499.99
- Low for revenue < 200
Rank customers by total revenue within their region using ROW_NUMBER().
Only include customers with at least 2 completed orders in January 2024.
Sort by region, then revenue descending, then customer name.

Table Definitions

`customers`

column	type	description
customer_id	INT	Primary key
customer_name	VARCHAR(100)	Customer name
region	VARCHAR(50)	Customer region
signup_date	DATE	Account creation date

`orders`

column	type	description
order_id	INT	Primary key
customer_id	INT	References customers
order_date	DATE	Order date
status	VARCHAR(20)	Order status
shipping_fee	NUMERIC(10,2)	Shipping charge

`order_items`

column	type	description
order_item_id	INT	Primary key
order_id	INT	References orders
product_name	VARCHAR(100)	Product name
quantity	INT	Quantity purchased
unit_price	NUMERIC(10,2)	Price per unit

Sample Data

`customers`

customer_id	customer_name	region	signup_date
3	Carla	East	2023-11-20
1	Alice	North	2023-12-15
8	Hank	West	2024-01-10
5	Eva	South	2023-08-01

`orders`

order_id	customer_id	order_date	status	shipping_fee
104	2	2024-01-20	completed	0.00
101	1	2024-01-05	completed	10.00
110	7	2024-01-21	completed	4.00
106	3	2024-01-25	completed	12.00

`order_items`

order_item_id	order_id	product_name	quantity	unit_price
1008	105	Keyboard	1	80.00
1001	101	Laptop Sleeve	2	25.00
1014	110	Mouse	1	25.00
1005	103	Dock	1	150.00

Expected Output

customer_id	customer_name	region	completed_orders	total_revenue	avg_order_value	spending_tier	regional_rank
3	Carla	East	2	667.00	333.50	High	1
1	Alice	North	2	330.00	165.00	Medium	1
7	Gina	North	2	90.00	45.00	Low	2

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
region	VARCHAR(50)	Customer sales region
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status
shipping_fee	NUMERIC(10,2)	Shipping fee charged for the order

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
order_id	INT	Order associated with the item
product_name	VARCHAR(100)	Product name
quantity	INT	Units purchased
unit_price	NUMERIC(10,2)	Price per unit

Sample Data

Example 1

Input

{"orders":[[104,2,"2024-01-20","completed","0.00"],[101,1,"2024-01-05","completed","10.00"],[110,7,"2024-01-21","completed","4.00"],[106,3,"2024-01-25","completed","12.00"],[108,5,"2024-01-18","cancelled","5.00"],[103,1,"2024-01-28","completed",null],[111,8,"2024-02-01","completed","6.00"],[102,1,"2024-01-15","pending","5.00"],[109,6,"2024-01-30","completed","0.00"],[105,3,"2024-01-08","completed","7.00"],[107,4,"2023-12-31","completed","3.00"],[112,999,"2024-01-12","completed","2.00"],[113,7,"2

Output

[["3","Carla","East","2","667.00","333.50","High","1"],["1","Alice","North","2","370.00","185.00","Medium","1"],["7","Gina","North","2","98.00","49.00","Low","2"]]

Practice SQL

PostgreSQL

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

Up next

Monthly Sales Summary for StoreEasy Monthly Revenue Trends by CategoryMedium AMonthly Sales Summary by CategoryEasy

Next question

Monthly Customer Spend and Rank

Medium

SQL & Data Manipulation

Asked at 19 companies19JoinsWindow FunctionsCTEs

Also asked at

Problem

Task

NovaCart wants a monthly customer performance report built from raw transactional tables. Write a PostgreSQL query from scratch to return customer-level metrics for January 2024.

Requirements

Return one row per customer who placed at least one completed order in January 2024.
Show each customer's total completed orders, total revenue, average order value, and a spending tier using CASE WHEN:
- High for revenue >= 500
- Medium for revenue between 200 and 499.99
- Low for revenue < 200
Rank customers by total revenue within their region using ROW_NUMBER().
Only include customers with at least 2 completed orders in January 2024.
Sort by region, then revenue descending, then customer name.

Table Definitions

`customers`

column	type	description
customer_id	INT	Primary key
customer_name	VARCHAR(100)	Customer name
region	VARCHAR(50)	Customer region
signup_date	DATE	Account creation date

`orders`

column	type	description
order_id	INT	Primary key
customer_id	INT	References customers
order_date	DATE	Order date
status	VARCHAR(20)	Order status
shipping_fee	NUMERIC(10,2)	Shipping charge

`order_items`

column	type	description
order_item_id	INT	Primary key
order_id	INT	References orders
product_name	VARCHAR(100)	Product name
quantity	INT	Quantity purchased
unit_price	NUMERIC(10,2)	Price per unit

Sample Data

`customers`

customer_id	customer_name	region	signup_date
3	Carla	East	2023-11-20
1	Alice	North	2023-12-15
8	Hank	West	2024-01-10
5	Eva	South	2023-08-01

`orders`

order_id	customer_id	order_date	status	shipping_fee
104	2	2024-01-20	completed	0.00
101	1	2024-01-05	completed	10.00
110	7	2024-01-21	completed	4.00
106	3	2024-01-25	completed	12.00

`order_items`

order_item_id	order_id	product_name	quantity	unit_price
1008	105	Keyboard	1	80.00
1001	101	Laptop Sleeve	2	25.00
1014	110	Mouse	1	25.00
1005	103	Dock	1	150.00

Expected Output

customer_id	customer_name	region	completed_orders	total_revenue	avg_order_value	spending_tier	regional_rank
3	Carla	East	2	667.00	333.50	High	1
1	Alice	North	2	330.00	165.00	Medium	1
7	Gina	North	2	90.00	45.00	Low	2

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
region	VARCHAR(50)	Customer sales region
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status
shipping_fee	NUMERIC(10,2)	Shipping fee charged for the order

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
order_id	INT	Order associated with the item
product_name	VARCHAR(100)	Product name
quantity	INT	Units purchased
unit_price	NUMERIC(10,2)	Price per unit

Sample Data

Example 1

Input

{"orders":[[104,2,"2024-01-20","completed","0.00"],[101,1,"2024-01-05","completed","10.00"],[110,7,"2024-01-21","completed","4.00"],[106,3,"2024-01-25","completed","12.00"],[108,5,"2024-01-18","cancelled","5.00"],[103,1,"2024-01-28","completed",null],[111,8,"2024-02-01","completed","6.00"],[102,1,"2024-01-15","pending","5.00"],[109,6,"2024-01-30","completed","0.00"],[105,3,"2024-01-08","completed","7.00"],[107,4,"2023-12-31","completed","3.00"],[112,999,"2024-01-12","completed","2.00"],[113,7,"2

Output

[["3","Carla","East","2","667.00","333.50","High","1"],["1","Alice","North","2","370.00","185.00","Medium","1"],["7","Gina","North","2","98.00","49.00","Low","2"]]

Problem

Task

NovaCart wants a monthly customer performance report built from raw transactional tables. Write a PostgreSQL query from scratch to return customer-level metrics for January 2024.

Requirements

Return one row per customer who placed at least one completed order in January 2024.
Show each customer's total completed orders, total revenue, average order value, and a spending tier using CASE WHEN:
- High for revenue >= 500
- Medium for revenue between 200 and 499.99
- Low for revenue < 200
Rank customers by total revenue within their region using ROW_NUMBER().
Only include customers with at least 2 completed orders in January 2024.
Sort by region, then revenue descending, then customer name.

Table Definitions

`customers`

column	type	description
customer_id	INT	Primary key
customer_name	VARCHAR(100)	Customer name
region	VARCHAR(50)	Customer region
signup_date	DATE	Account creation date

`orders`

column	type	description
order_id	INT	Primary key
customer_id	INT	References customers
order_date	DATE	Order date
status	VARCHAR(20)	Order status
shipping_fee	NUMERIC(10,2)	Shipping charge

`order_items`

column	type	description
order_item_id	INT	Primary key
order_id	INT	References orders
product_name	VARCHAR(100)	Product name
quantity	INT	Quantity purchased
unit_price	NUMERIC(10,2)	Price per unit

Sample Data

`customers`

customer_id	customer_name	region	signup_date
3	Carla	East	2023-11-20
1	Alice	North	2023-12-15
8	Hank	West	2024-01-10
5	Eva	South	2023-08-01

`orders`

order_id	customer_id	order_date	status	shipping_fee
104	2	2024-01-20	completed	0.00
101	1	2024-01-05	completed	10.00
110	7	2024-01-21	completed	4.00
106	3	2024-01-25	completed	12.00

`order_items`

order_item_id	order_id	product_name	quantity	unit_price
1008	105	Keyboard	1	80.00
1001	101	Laptop Sleeve	2	25.00
1014	110	Mouse	1	25.00
1005	103	Dock	1	150.00

Expected Output

customer_id	customer_name	region	completed_orders	total_revenue	avg_order_value	spending_tier	regional_rank
3	Carla	East	2	667.00	333.50	High	1
1	Alice	North	2	330.00	165.00	Medium	1
7	Gina	North	2	90.00	45.00	Low	2

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
region	VARCHAR(50)	Customer sales region
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status
shipping_fee	NUMERIC(10,2)	Shipping fee charged for the order

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
order_id	INT	Order associated with the item
product_name	VARCHAR(100)	Product name
quantity	INT	Units purchased
unit_price	NUMERIC(10,2)	Price per unit

Sample Data

Example 1

Input

{"orders":[[104,2,"2024-01-20","completed","0.00"],[101,1,"2024-01-05","completed","10.00"],[110,7,"2024-01-21","completed","4.00"],[106,3,"2024-01-25","completed","12.00"],[108,5,"2024-01-18","cancelled","5.00"],[103,1,"2024-01-28","completed",null],[111,8,"2024-02-01","completed","6.00"],[102,1,"2024-01-15","pending","5.00"],[109,6,"2024-01-30","completed","0.00"],[105,3,"2024-01-08","completed","7.00"],[107,4,"2023-12-31","completed","3.00"],[112,999,"2024-01-12","completed","2.00"],[113,7,"2

Output

[["3","Carla","East","2","667.00","333.50","High","1"],["1","Alice","North","2","370.00","185.00","Medium","1"],["7","Gina","North","2","98.00","49.00","Low","2"]]

Practice SQL

PostgreSQL

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

Up next

Monthly Sales Summary for StoreEasy Monthly Revenue Trends by CategoryMedium AMonthly Sales Summary by CategoryEasy

Next question