Interview Guides

Top Product Revenue Last 30 Days | Dataford Interview Questions - Dataford - Ace your Interview

Top Product Revenue Last 30 Days

Medium

SQL & Data Manipulation

Asked at 51 companies51JoinsAggregationsDate Functions

Also asked at

Problem

Task

ShopSphere wants to identify its highest-revenue products from recent sales activity. Write a SQL query to find the top 5 products by revenue in the last 30 days, excluding any returned items.

Assume the query is run relative to the latest date in the dataset, and revenue should be calculated as quantity * unit_price.

Requirements

Use only order items from the last 30 days based on orders.order_date.
Exclude items where order_items.is_returned = true.
Join the necessary tables to return product names.
Aggregate revenue by product.
Return the top 5 products ordered by revenue descending.

Table Definitions

`products`

column	type	description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

`orders`

column	type	description
order_id	INT	Primary key for each order
customer_id	INT	Customer placing the order
order_date	DATE	Date the order was placed

`order_items`

column	type	description
order_item_id	INT	Primary key for each line item
order_id	INT	References `orders.order_id`
product_id	INT	References `products.product_id`
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time
is_returned	BOOLEAN	Whether the item was returned

Sample Data

`products`

product_id	product_name	category
101	Wireless Mouse	Electronics
102	Mechanical Keyboard	Electronics
103	USB-C Hub	Accessories

`orders`

order_id	customer_id	order_date
1001	501	2024-06-28
1002	502	2024-07-02
1003	503	2024-07-10

`order_items`

order_item_id	order_id	product_id	quantity	unit_price	is_returned
1	1001	101	2	25.00	false
2	1002	102	1	90.00	false
3	1003	101	1	25.00	true

Expected Output

product_id	product_name	total_revenue
106	4K Monitor	600.00
102	Mechanical Keyboard	360.00
108	Laptop Stand	240.00
101	Wireless Mouse	150.00
107	Noise Cancelling Headphones	150.00

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

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

order_items

Column	Type	Description
order_item_idPK	INT	Unique line item identifier
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Unit sale price at the time of purchase
is_returned	BOOLEAN	Whether the line item was returned

Sample Data

Example 1

Input

{"orders":[[1008,508,"2024-06-20"],[1003,503,"2024-07-10"],[1001,501,"2024-06-28"],[1006,506,"2024-07-23"],[1010,510,"2024-07-25"],[1002,502,"2024-07-02"],[1005,505,"2024-07-21"],[1004,504,"2024-07-18"],[1009,509,"2024-07-24"],[1007,null,"2024-07-26"],["1","99","2024-07-22"],["2","13","2024-06-30"],["3","58","2024-07-04"],["4","97","2024-07-13"],["5","6","2024-06-29"],["6","77","2024-06-19"],["7","14","2024-07-02"],["8","59","2024-07-04"],["9","41","2024-06-26"],["10","1","2024-07-18"],["11","32

Output

[["106","4K Monitor","600.00"],["102","Mechanical Keyboard","360.00"],["107","Noise Cancelling Headphones","150.00"],["103","USB-C Hub","135.00"],["105","Desk Lamp","120.00"]]

Problem

Task

ShopSphere wants to identify its highest-revenue products from recent sales activity. Write a SQL query to find the top 5 products by revenue in the last 30 days, excluding any returned items.

Assume the query is run relative to the latest date in the dataset, and revenue should be calculated as quantity * unit_price.

Requirements

Use only order items from the last 30 days based on orders.order_date.
Exclude items where order_items.is_returned = true.
Join the necessary tables to return product names.
Aggregate revenue by product.
Return the top 5 products ordered by revenue descending.

Table Definitions

`products`

column	type	description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

`orders`

column	type	description
order_id	INT	Primary key for each order
customer_id	INT	Customer placing the order
order_date	DATE	Date the order was placed

`order_items`

column	type	description
order_item_id	INT	Primary key for each line item
order_id	INT	References `orders.order_id`
product_id	INT	References `products.product_id`
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time
is_returned	BOOLEAN	Whether the item was returned

Sample Data

`products`

product_id	product_name	category
101	Wireless Mouse	Electronics
102	Mechanical Keyboard	Electronics
103	USB-C Hub	Accessories

`orders`

order_id	customer_id	order_date
1001	501	2024-06-28
1002	502	2024-07-02
1003	503	2024-07-10

`order_items`

order_item_id	order_id	product_id	quantity	unit_price	is_returned
1	1001	101	2	25.00	false
2	1002	102	1	90.00	false
3	1003	101	1	25.00	true

Expected Output

product_id	product_name	total_revenue
106	4K Monitor	600.00
102	Mechanical Keyboard	360.00
108	Laptop Stand	240.00
101	Wireless Mouse	150.00
107	Noise Cancelling Headphones	150.00

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

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

order_items

Column	Type	Description
order_item_idPK	INT	Unique line item identifier
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Unit sale price at the time of purchase
is_returned	BOOLEAN	Whether the line item was returned

Sample Data

Example 1

Input

{"orders":[[1008,508,"2024-06-20"],[1003,503,"2024-07-10"],[1001,501,"2024-06-28"],[1006,506,"2024-07-23"],[1010,510,"2024-07-25"],[1002,502,"2024-07-02"],[1005,505,"2024-07-21"],[1004,504,"2024-07-18"],[1009,509,"2024-07-24"],[1007,null,"2024-07-26"],["1","99","2024-07-22"],["2","13","2024-06-30"],["3","58","2024-07-04"],["4","97","2024-07-13"],["5","6","2024-06-29"],["6","77","2024-06-19"],["7","14","2024-07-02"],["8","59","2024-07-04"],["9","41","2024-06-26"],["10","1","2024-07-18"],["11","32

Output

[["106","4K Monitor","600.00"],["102","Mechanical Keyboard","360.00"],["107","Noise Cancelling Headphones","150.00"],["103","USB-C Hub","135.00"],["105","Desk Lamp","120.00"]]

Practice SQL

PostgreSQL

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

Up next

Top Product Revenue Last 30 Days

Medium

SQL & Data Manipulation

Asked at 51 companies51JoinsAggregationsDate Functions

Also asked at

Problem

Task

ShopSphere wants to identify its highest-revenue products from recent sales activity. Write a SQL query to find the top 5 products by revenue in the last 30 days, excluding any returned items.

Assume the query is run relative to the latest date in the dataset, and revenue should be calculated as quantity * unit_price.

Requirements

Use only order items from the last 30 days based on orders.order_date.
Exclude items where order_items.is_returned = true.
Join the necessary tables to return product names.
Aggregate revenue by product.
Return the top 5 products ordered by revenue descending.

Table Definitions

`products`

column	type	description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

`orders`

column	type	description
order_id	INT	Primary key for each order
customer_id	INT	Customer placing the order
order_date	DATE	Date the order was placed

`order_items`

column	type	description
order_item_id	INT	Primary key for each line item
order_id	INT	References `orders.order_id`
product_id	INT	References `products.product_id`
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time
is_returned	BOOLEAN	Whether the item was returned

Sample Data

`products`

product_id	product_name	category
101	Wireless Mouse	Electronics
102	Mechanical Keyboard	Electronics
103	USB-C Hub	Accessories

`orders`

order_id	customer_id	order_date
1001	501	2024-06-28
1002	502	2024-07-02
1003	503	2024-07-10

`order_items`

order_item_id	order_id	product_id	quantity	unit_price	is_returned
1	1001	101	2	25.00	false
2	1002	102	1	90.00	false
3	1003	101	1	25.00	true

Expected Output

product_id	product_name	total_revenue
106	4K Monitor	600.00
102	Mechanical Keyboard	360.00
108	Laptop Stand	240.00
101	Wireless Mouse	150.00
107	Noise Cancelling Headphones	150.00

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

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

order_items

Column	Type	Description
order_item_idPK	INT	Unique line item identifier
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Unit sale price at the time of purchase
is_returned	BOOLEAN	Whether the line item was returned

Sample Data

Example 1

Input

{"orders":[[1008,508,"2024-06-20"],[1003,503,"2024-07-10"],[1001,501,"2024-06-28"],[1006,506,"2024-07-23"],[1010,510,"2024-07-25"],[1002,502,"2024-07-02"],[1005,505,"2024-07-21"],[1004,504,"2024-07-18"],[1009,509,"2024-07-24"],[1007,null,"2024-07-26"],["1","99","2024-07-22"],["2","13","2024-06-30"],["3","58","2024-07-04"],["4","97","2024-07-13"],["5","6","2024-06-29"],["6","77","2024-06-19"],["7","14","2024-07-02"],["8","59","2024-07-04"],["9","41","2024-06-26"],["10","1","2024-07-18"],["11","32

Output

[["106","4K Monitor","600.00"],["102","Mechanical Keyboard","360.00"],["107","Noise Cancelling Headphones","150.00"],["103","USB-C Hub","135.00"],["105","Desk Lamp","120.00"]]

Problem

Task

ShopSphere wants to identify its highest-revenue products from recent sales activity. Write a SQL query to find the top 5 products by revenue in the last 30 days, excluding any returned items.

Assume the query is run relative to the latest date in the dataset, and revenue should be calculated as quantity * unit_price.

Requirements

Use only order items from the last 30 days based on orders.order_date.
Exclude items where order_items.is_returned = true.
Join the necessary tables to return product names.
Aggregate revenue by product.
Return the top 5 products ordered by revenue descending.

Table Definitions

`products`

column	type	description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

`orders`

column	type	description
order_id	INT	Primary key for each order
customer_id	INT	Customer placing the order
order_date	DATE	Date the order was placed

`order_items`

column	type	description
order_item_id	INT	Primary key for each line item
order_id	INT	References `orders.order_id`
product_id	INT	References `products.product_id`
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time
is_returned	BOOLEAN	Whether the item was returned

Sample Data

`products`

product_id	product_name	category
101	Wireless Mouse	Electronics
102	Mechanical Keyboard	Electronics
103	USB-C Hub	Accessories

`orders`

order_id	customer_id	order_date
1001	501	2024-06-28
1002	502	2024-07-02
1003	503	2024-07-10

`order_items`

order_item_id	order_id	product_id	quantity	unit_price	is_returned
1	1001	101	2	25.00	false
2	1002	102	1	90.00	false
3	1003	101	1	25.00	true

Expected Output

product_id	product_name	total_revenue
106	4K Monitor	600.00
102	Mechanical Keyboard	360.00
108	Laptop Stand	240.00
101	Wireless Mouse	150.00
107	Noise Cancelling Headphones	150.00

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

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

order_items

Column	Type	Description
order_item_idPK	INT	Unique line item identifier
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Unit sale price at the time of purchase
is_returned	BOOLEAN	Whether the line item was returned

Sample Data

Example 1

Input

{"orders":[[1008,508,"2024-06-20"],[1003,503,"2024-07-10"],[1001,501,"2024-06-28"],[1006,506,"2024-07-23"],[1010,510,"2024-07-25"],[1002,502,"2024-07-02"],[1005,505,"2024-07-21"],[1004,504,"2024-07-18"],[1009,509,"2024-07-24"],[1007,null,"2024-07-26"],["1","99","2024-07-22"],["2","13","2024-06-30"],["3","58","2024-07-04"],["4","97","2024-07-13"],["5","6","2024-06-29"],["6","77","2024-06-19"],["7","14","2024-07-02"],["8","59","2024-07-04"],["9","41","2024-06-26"],["10","1","2024-07-18"],["11","32

Output

[["106","4K Monitor","600.00"],["102","Mechanical Keyboard","360.00"],["107","Noise Cancelling Headphones","150.00"],["103","USB-C Hub","135.00"],["105","Desk Lamp","120.00"]]

Practice SQL

PostgreSQL

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

Up next