Rank Vendors With Window Functions | Dataford Interview Questions

Write a SQL query that uses window functions to rank vendors by cost savings within each category.

Use the provided vendor and quote data. Cost savings equal benchmark cost minus quoted cost, summed for each vendor and category. Exclude unmatched vendors and quote rows missing either cost.

Output

One row per vendor and category, with vendor_id, vendor_name, category, total_cost_savings, and savings_rank.
Ties share the same rank.
Order by category, savings_rank, then vendor_id.

Schema

vendors

Column	Type	Description
vendor_idPK	INT	Unique vendor identifier
vendor_name	VARCHAR(100)	Vendor business name

vendor_quotes

Column	Type	Description
quote_idPK	INT	Unique quote identifier
vendor_id	INT	Vendor submitting the quote
category	VARCHAR(50)	Procurement category
benchmark_cost	NUMERIC(12,2)	Expected or benchmark cost
quoted_cost	NUMERIC(12,2)	Cost quoted by the vendor

Your question is Rank Vendors With Window Functions. Start with the requirements and the two 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