Top Parts Per Category | Dataford Interview Questions

Advance Auto Parts wants to identify the strongest-selling automotive parts across store categories. Write a SQL query that calculates total units sold for each part within each store category during the previous complete calendar quarter.

Output

Return one row per part included in the top three for its store category.
Include store_category, part_id, part_name, total_units_sold, and sales_rank.
Rank parts by total units sold in descending order within each category. Break ties by ascending part_id.
Exclude sales outside the previous complete calendar quarter and sales that cannot be matched to a store or part. Preserve NULL store categories as their own category.

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