Top 3 Products by Category | Dataford Interview Questions

Rakuten Marketplace needs a category-level view of its highest-revenue products. Write a PostgreSQL query using a CTE and a window function to identify the top three products in each non-null category based on completed orders.

Requirements

Calculate revenue as quantity * unit_price for completed order items only.
Aggregate revenue by product and category, then rank products within each category using a window function.
Return products with ranks 1 through 3, including categories with fewer than three products.
Order the final results by category and revenue rank. Break revenue ties by the smaller product_id.

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(150)	Rakuten Marketplace product name
category	VARCHAR(80)	Product category

order_items

Column	Type	Description
order_item_idPK	INT	Unique order-item identifier
product_id	INT	Referenced product identifier
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Price per purchased unit
order_status	VARCHAR(20)	Order processing status

Your question is Top 3 Products by Category. 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.

Problem