SQL Top Products Query | Dataford Interview Questions

Business Context

Cloud Software Group's product analytics team wants to identify the strongest-selling products within each product category. Revenue should reflect completed orders only.

Task

Write a PostgreSQL query that returns the top three products by revenue for every product category.

Requirements

Join products, order items, and orders to calculate revenue as quantity * unit_price.
Include only orders with status = 'completed'.
Aggregate revenue by product and category, then rank products independently within each category.
Return products ranked 1 through 3, ordered by category, revenue descending, and product ID as a deterministic tie-breaker.

Schema

products

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

order_items

Column	Type	Description
order_item_idPK	INT	Unique order-line identifier
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	NUMERIC(10,2)	Price per unit at purchase time

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status

Your question is SQL Top Products Query. 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