Average Medical Claim Cost by Supplier | Dataford Interview Questions

Write a query to report the average medical claim cost by supplier.

Use the suppliers and medical_claims tables. Include suppliers with no claims, returning a NULL average for them. Round averages to two decimal places.

Output

One row per supplier with columns supplier_name and average_claim_cost
Include all suppliers and ignore NULL claim costs in the average
Sort by average cost descending, place NULL averages last, then sort by supplier name ascending

Schema

suppliers

Column	Type	Description
supplier_idPK	INT	Unique supplier identifier
supplier_name	VARCHAR(150)	Supplier name

medical_claims

Column	Type	Description
claim_idPK	INT	Unique medical claim identifier
supplier_id	INT	Supplier associated with the claim
claim_cost	DECIMAL(12,2)	Cost of the medical claim

Your question is Average Medical Claim Cost by Supplier. 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