Using HAVING for Provider Spend | Dataford Interview Questions

Santa Clara Family Health wants to identify providers with more than $50,000 in billed claims during the latest completed fiscal quarter. Write a SQL query that returns each qualifying provider and their total billed amount.

Output

One row per provider with claims in the latest completed fiscal quarter.
Return provider_id, provider_name, and total_billed.
Exclude providers whose total is $50,000 or less, and ignore claims with a NULL billed amount when calculating totals.
Sort by total_billed descending, then provider_id ascending.

Schema

providers

Column	Type	Description
provider_idPK	INT	Unique provider identifier
provider_name	VARCHAR(100)	Provider or facility name

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
provider_id	INT	Provider associated with the claim
service_date	DATE	Date services were provided
billed_amount	NUMERIC(12,2)	Amount billed on the claim

fiscal_quarters

Column	Type	Description
quarter_idPK	INT	Unique fiscal quarter identifier
quarter_name	VARCHAR(20)	Fiscal quarter label
quarter_start	DATE	First date of the fiscal quarter
start_date	DATE	Date boundary used for claim matching
end_date	DATE	Last date of the fiscal quarter
quarter_end	DATE	Date used to determine whether the quarter is complete

Your question is Using HAVING for Provider Spend. 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