Interview Guides

Top Products by Sales Revenue | Dataford Interview Questions - Dataford - Ace your Interview

Top Products by Sales Revenue

Medium

SQL & Data Manipulation

Asked at 75 companies75AggregationsGroup ByRanking

Also asked at

Problem

Task

NovaCart wants to identify its highest-revenue products from completed sales. Write a SQL query to return the top 10 products by total sales revenue.

Requirements

Use only completed orders when calculating revenue.
Compute product revenue as quantity * unit_price from order line items.
Return each product's product_id, product_name, and total revenue.
Sort by total revenue descending, then by product_id ascending to break ties.
Return only the top 10 rows.

Table Definitions

`products`

column	type	description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product 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	Order date
status	VARCHAR(20)	Order status

`order_items`

column	type	description
order_item_id	INT	Primary key for each order line
order_id	INT	References `orders.order_id`
product_id	INT	References `products.product_id`
quantity	INT	Units sold
unit_price	DECIMAL(10,2)	Price per unit at purchase time

Sample Data

Representative rows are included below in the structured dataset.

Expected Output

product_id	product_name	total_revenue
3	Noise-Canceling Headphones	500.00
1	Wireless Mouse	300.00
4	Standing Desk	300.00
9	Office Chair	300.00
2	Mechanical Keyboard	240.00
7	4K Monitor	220.00
12	Webcam	180.00
8	USB-C Dock	160.00
6	Laptop Stand	105.00
11	Ethernet Adapter	90.00

Schema

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Display name of the product
category	VARCHAR(50)	Product category

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_id	INT	Identifier for the customer
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
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 price captured at purchase time

Sample Data

Example 1

Input

{"orders":[["1001","501","2024-03-05","completed"],["1002","502","2024-03-06","cancelled"],["1003","503","2024-03-07","completed"],["1004","504","2024-03-08","pending"],["1005","505","2024-03-09","completed"],["1006","506","2024-03-10","returned"],["1007","507","2024-03-11","completed"],["1008",null,"2024-03-12","completed"],["1009","509","2024-03-13",null],["1010","510","2024-03-14","completed"],["1011","511","2024-03-05","backordered"],["1012","506","2024-03-15","on_hold"],["1013","503","2024-

Output

[["9","Office Chair","1885.00"],["3","Noise-Canceling Headphones","1856.00"],["1","Wireless Mouse","790.00"],["8","USB-C Dock","763.00"],["2","Mechanical Keyboard","240.00"],["12","Webcam","180.00"],["7","4K Monitor","169.00"],["6","Laptop Stand","105.00"],["10","Desk Lamp","0.00"]]

Problem

Task

NovaCart wants to identify its highest-revenue products from completed sales. Write a SQL query to return the top 10 products by total sales revenue.

Requirements

Use only completed orders when calculating revenue.
Compute product revenue as quantity * unit_price from order line items.
Return each product's product_id, product_name, and total revenue.
Sort by total revenue descending, then by product_id ascending to break ties.
Return only the top 10 rows.

Table Definitions

`products`

column	type	description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product 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	Order date
status	VARCHAR(20)	Order status

`order_items`

column	type	description
order_item_id	INT	Primary key for each order line
order_id	INT	References `orders.order_id`
product_id	INT	References `products.product_id`
quantity	INT	Units sold
unit_price	DECIMAL(10,2)	Price per unit at purchase time

Sample Data

Representative rows are included below in the structured dataset.

Expected Output

product_id	product_name	total_revenue
3	Noise-Canceling Headphones	500.00
1	Wireless Mouse	300.00
4	Standing Desk	300.00
9	Office Chair	300.00
2	Mechanical Keyboard	240.00
7	4K Monitor	220.00
12	Webcam	180.00
8	USB-C Dock	160.00
6	Laptop Stand	105.00
11	Ethernet Adapter	90.00

Schema

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Display name of the product
category	VARCHAR(50)	Product category

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_id	INT	Identifier for the customer
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
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 price captured at purchase time

Sample Data

Example 1

Input

{"orders":[["1001","501","2024-03-05","completed"],["1002","502","2024-03-06","cancelled"],["1003","503","2024-03-07","completed"],["1004","504","2024-03-08","pending"],["1005","505","2024-03-09","completed"],["1006","506","2024-03-10","returned"],["1007","507","2024-03-11","completed"],["1008",null,"2024-03-12","completed"],["1009","509","2024-03-13",null],["1010","510","2024-03-14","completed"],["1011","511","2024-03-05","backordered"],["1012","506","2024-03-15","on_hold"],["1013","503","2024-

Output

[["9","Office Chair","1885.00"],["3","Noise-Canceling Headphones","1856.00"],["1","Wireless Mouse","790.00"],["8","USB-C Dock","763.00"],["2","Mechanical Keyboard","240.00"],["12","Webcam","180.00"],["7","4K Monitor","169.00"],["6","Laptop Stand","105.00"],["10","Desk Lamp","0.00"]]

Practice SQL

PostgreSQL

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

Up next

Top Products by Sales Revenue

Medium

SQL & Data Manipulation

Asked at 75 companies75AggregationsGroup ByRanking

Also asked at

Problem

Task

NovaCart wants to identify its highest-revenue products from completed sales. Write a SQL query to return the top 10 products by total sales revenue.

Requirements

Use only completed orders when calculating revenue.
Compute product revenue as quantity * unit_price from order line items.
Return each product's product_id, product_name, and total revenue.
Sort by total revenue descending, then by product_id ascending to break ties.
Return only the top 10 rows.

Table Definitions

`products`

column	type	description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product 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	Order date
status	VARCHAR(20)	Order status

`order_items`

column	type	description
order_item_id	INT	Primary key for each order line
order_id	INT	References `orders.order_id`
product_id	INT	References `products.product_id`
quantity	INT	Units sold
unit_price	DECIMAL(10,2)	Price per unit at purchase time

Sample Data

Representative rows are included below in the structured dataset.

Expected Output

product_id	product_name	total_revenue
3	Noise-Canceling Headphones	500.00
1	Wireless Mouse	300.00
4	Standing Desk	300.00
9	Office Chair	300.00
2	Mechanical Keyboard	240.00
7	4K Monitor	220.00
12	Webcam	180.00
8	USB-C Dock	160.00
6	Laptop Stand	105.00
11	Ethernet Adapter	90.00

Schema

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Display name of the product
category	VARCHAR(50)	Product category

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_id	INT	Identifier for the customer
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
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 price captured at purchase time

Sample Data

Example 1

Input

{"orders":[["1001","501","2024-03-05","completed"],["1002","502","2024-03-06","cancelled"],["1003","503","2024-03-07","completed"],["1004","504","2024-03-08","pending"],["1005","505","2024-03-09","completed"],["1006","506","2024-03-10","returned"],["1007","507","2024-03-11","completed"],["1008",null,"2024-03-12","completed"],["1009","509","2024-03-13",null],["1010","510","2024-03-14","completed"],["1011","511","2024-03-05","backordered"],["1012","506","2024-03-15","on_hold"],["1013","503","2024-

Output

[["9","Office Chair","1885.00"],["3","Noise-Canceling Headphones","1856.00"],["1","Wireless Mouse","790.00"],["8","USB-C Dock","763.00"],["2","Mechanical Keyboard","240.00"],["12","Webcam","180.00"],["7","4K Monitor","169.00"],["6","Laptop Stand","105.00"],["10","Desk Lamp","0.00"]]

Problem

Task

NovaCart wants to identify its highest-revenue products from completed sales. Write a SQL query to return the top 10 products by total sales revenue.

Requirements

Use only completed orders when calculating revenue.
Compute product revenue as quantity * unit_price from order line items.
Return each product's product_id, product_name, and total revenue.
Sort by total revenue descending, then by product_id ascending to break ties.
Return only the top 10 rows.

Table Definitions

`products`

column	type	description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product 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	Order date
status	VARCHAR(20)	Order status

`order_items`

column	type	description
order_item_id	INT	Primary key for each order line
order_id	INT	References `orders.order_id`
product_id	INT	References `products.product_id`
quantity	INT	Units sold
unit_price	DECIMAL(10,2)	Price per unit at purchase time

Sample Data

Representative rows are included below in the structured dataset.

Expected Output

product_id	product_name	total_revenue
3	Noise-Canceling Headphones	500.00
1	Wireless Mouse	300.00
4	Standing Desk	300.00
9	Office Chair	300.00
2	Mechanical Keyboard	240.00
7	4K Monitor	220.00
12	Webcam	180.00
8	USB-C Dock	160.00
6	Laptop Stand	105.00
11	Ethernet Adapter	90.00

Schema

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Display name of the product
category	VARCHAR(50)	Product category

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_id	INT	Identifier for the customer
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
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 price captured at purchase time

Sample Data

Example 1

Input

{"orders":[["1001","501","2024-03-05","completed"],["1002","502","2024-03-06","cancelled"],["1003","503","2024-03-07","completed"],["1004","504","2024-03-08","pending"],["1005","505","2024-03-09","completed"],["1006","506","2024-03-10","returned"],["1007","507","2024-03-11","completed"],["1008",null,"2024-03-12","completed"],["1009","509","2024-03-13",null],["1010","510","2024-03-14","completed"],["1011","511","2024-03-05","backordered"],["1012","506","2024-03-15","on_hold"],["1013","503","2024-

Output

[["9","Office Chair","1885.00"],["3","Noise-Canceling Headphones","1856.00"],["1","Wireless Mouse","790.00"],["8","USB-C Dock","763.00"],["2","Mechanical Keyboard","240.00"],["12","Webcam","180.00"],["7","4K Monitor","169.00"],["6","Laptop Stand","105.00"],["10","Desk Lamp","0.00"]]

Practice SQL

PostgreSQL

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

Up next