Top Product Revenue Last 30 Days | Dataford Interview Questions

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.

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

Your question is Top Product Revenue Last 30 Days. Start with the requirements and the three 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