Top Products in a Rolling Window | Dataford Interview Questions

Write a query to identify top-performing products over a rolling 30-day window.

Use the provided product, order, and reporting-date tables. Calculate product revenue for each reporting date using the inclusive 30-day period ending on that date. Include products ranked among the top three for each reporting date, including ties.

Output

One row per qualifying product and reporting date.
Columns: reporting_date, product_id, product_name, rolling_revenue, and performance_rank.
Order by reporting_date, performance_rank, and product_id.

Schema

products

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

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
product_id	INT	Product purchased in the order
order_date	DATE	Date the order was placed
quantity	INT	Number of units ordered
unit_price	NUMERIC(10,2)	Price per unit

reporting_dates

Column	Type	Description
reporting_datePK	DATE	End date of a rolling reporting window

Your question is Top Products in a Rolling Window. 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