Top-Selling Products by Month | Dataford Interview Questions

Fashion Nova wants a monthly view of its highest-revenue products. Write a PostgreSQL query that returns the top two products by revenue for every calendar month represented in the order data.

Requirements

Include only orders whose status is completed.
Calculate product revenue as quantity * unit_price, grouped by calendar month and product.
Use a window function to rank products within each month and return ranks 1 and 2, including ties.
Sort the final output by month ascending, revenue descending, and product ID ascending.

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
sku	VARCHAR(30)	Fashion Nova stock keeping unit
product_name	VARCHAR(100)	Product display name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
ordered_at	TIMESTAMP	Timestamp when the order was placed
status	VARCHAR(20)	Order lifecycle status

order_items

Column	Type	Description
line_idPK	INT	Unique order-line identifier
order_id	INT	Referenced order
product_id	INT	Referenced product
quantity	INT	Number of units in the line item
unit_price	NUMERIC(10,2)	Selling price per unit

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