Top 3 Products by Month | Dataford Interview Questions

Domo's Data Analyst team wants a monthly view of the products generating the most revenue. Write a PostgreSQL query that calculates product revenue from order line items and returns the top three products for each calendar month.

Requirements

Include only orders with a status of completed.
Calculate revenue as quantity * unit_price, grouped by month and product.
Rank products independently within each month and return the top three, breaking revenue ties by the smaller product_id.
Return the month, product name, revenue, and rank, ordered by month and rank.

Schema

orders

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

order_items

Column	Type	Description
order_item_idPK	INT	Line item 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

products

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

Your question is Top 3 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