SQL for Supplier Defects | Dataford Interview Questions

Write a SQL query to find the top 5 suppliers by volume who have a defect rate higher than 2% for Mini-Circuits procurement data.

Use suppliers and procurement_receipts. Define volume as total units received and defect rate as total defective units divided by total units received.

Output

Return one row per qualifying supplier with supplier_name, total_volume, defective_units, and defect_rate_percent.
Include only suppliers whose defect rate exceeds 2%.
Order by total volume descending, then supplier name ascending for ties, and return at most five rows.

Schema

suppliers

Column	Type	Description
supplier_idPK	INT	Unique supplier identifier
supplier_name	VARCHAR(150)	Supplier name
supplier_region	VARCHAR(60)	Supplier operating region

procurement_receipts

Column	Type	Description
receipt_idPK	INT	Unique procurement receipt identifier
supplier_id	INT	Supplier associated with the receipt
receipt_date	DATE	Date the material was received
units_received	INT	Number of units received
defective_units	INT	Number of defective units identified

Your question is SQL for Supplier Defects. 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