CTE for Top Products by Revenue | Dataford Interview Questions

Write a query using a CTE to identify the top three products by revenue per category.

Use the provided product and order item data. Revenue is the sum of quantity multiplied by unit price. Break revenue ties by the smaller product ID.

Output

One row per qualifying product, with category, product_id, product_name, revenue, and product_rank.
Include only the top three products in each category, ordered by category and rank.

products

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
product_id	INT	Product purchased in the order item
quantity	INT	Number of units purchased
unit_price	NUMERIC(10,2)	Price per unit at purchase time

Your question is CTE for Top Products by Revenue. Start with the requirements and the two 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.

Write a query using a CTE to identify the top three products by revenue per category.

Use the provided product and order item data. Revenue is the sum of quantity multiplied by unit price. Break revenue ties by the smaller product ID.

One row per qualifying product, with category, product_id, product_name, revenue, and product_rank.
Include only the top three products in each category, ordered by category and rank.

products

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
product_id	INT	Product purchased in the order item
quantity	INT	Number of units purchased
unit_price	NUMERIC(10,2)	Price per unit at purchase time

Your question is CTE for Top Products by Revenue. Start with the requirements and the two 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.