Top Parts Per Category | Dataford Interview Questions

Write a SQL query using window functions to find the top three selling automotive parts in each store category for the last quarter at Advance Auto Parts.

Use the provided store, part, and sales data. Treat the last quarter as the complete calendar quarter immediately before the current quarter. Return only parts with sales during that period.

Output

One row per qualifying part, with store_category, part_id, part_name, total_units_sold, and sales_rank.
Include the top three parts in each category, ranked by total units sold descending.
Break ties by part_id ascending, and order the final results by store_category, sales_rank, and part_id.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
store_category	VARCHAR(50)	Business category assigned to the store

parts

Column	Type	Description
part_idPK	INT	Unique automotive part identifier
part_name	VARCHAR(150)	Automotive part description

sales

Column	Type	Description
sale_idPK	INT	Unique sales record identifier
store_id	INT	Store associated with the sale
part_id	INT	Part associated with the sale
sale_date	DATE	Date of the sale
quantity	INT	Number of units sold

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