Top Products by Category | Dataford Interview Questions

Chewy's merchandising team needs a quarterly view of its best-selling products. Assume the reporting quarter is April 1, 2025 through June 30, 2025, inclusive.

Write a PostgreSQL query to identify the top three products by units sold within each product category.

Requirements

Include only completed orders placed during the reporting quarter.
Aggregate quantity by product and category, treating NULL quantities as zero.
Rank products independently within each category and return only ranks 1 through 3.
Sort the output by category and sales rank.

Schema

products

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

orders

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

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	Units included on the order line

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