Second-Highest Purchase Per Category | Dataford Interview Questions

Write a SQL query using window functions to find the second-highest purchase amount per guest category over a rolling 30-day period at Target.

Use the purchases table. Treat the second-highest value as the second-highest distinct purchase amount. Exclude incomplete category or amount values and dates whose 30-day period has no second-highest amount.

Output

One row per guest category and purchase date with a valid second-highest amount
Columns: guest_category, as_of_date, second_highest_purchase_amount
Order by guest_category, then as_of_date ascending

Schema

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
guest_id	VARCHAR(20)	Target guest identifier
guest_category	VARCHAR(30)	Guest category assigned to the purchase
purchase_date	DATE	Calendar date of the purchase
purchase_amount	DECIMAL(10,2)	Purchase amount in US dollars

Your question is Second-Highest Purchase Per Category. Start with the requirements and the one table 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

Write a SQL query using window functions to find the second-highest purchase amount per guest category over a rolling 30-day period at Target.

Output

One row per guest category and purchase date with a valid second-highest amount
Columns: guest_category, as_of_date, second_highest_purchase_amount
Order by guest_category, then as_of_date ascending

Schema

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
guest_id	VARCHAR(20)	Target guest identifier
guest_category	VARCHAR(30)	Guest category assigned to the purchase
purchase_date	DATE	Calendar date of the purchase
purchase_amount	DECIMAL(10,2)	Purchase amount in US dollars

Your question is Second-Highest Purchase Per Category. Start with the requirements and the one table 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.