Interview Question Bank · WHERE vs HAVING · minimum volume
3. Claim denial rate by insurer
EasyHealthcareQuestion #135
Concept: WHERE vs HAVING · minimum volumeshow
WHERE filters rows before grouping, and HAVING filters groups after. A rate should only use rows whose outcome is known, so undecided rows go in WHERE. A minimum-volume rule (at least N cases) is about the group, so it goes in HAVING, and it should count the same rows the rate uses.
Example (different data):
SELECT referee, COUNT(*) AS reviewed, ROUND(100.0 * COUNT(*) FILTER (WHERE overturned) / COUNT(*), 1) AS overturn_pct FROM match_calls WHERE review_status = 'final' GROUP BY referee HAVING COUNT(*) >= 10;
Your task
Larkspur Health's revenue-cycle team is renegotiating with insurers and wants each one's denial rate for Q1 2025. Pending claims haven't been decided, and an insurer needs at least 5 decided claims for its rate to be reported.
Using decided claims only (status 'paid' or 'denied'), return insurer, decided_claims, denied_claims and denial_rate_pct (denied as a % of decided, rounded to 1 decimal) for insurers with at least 5 decided claims. Sort by denial_rate_pct descending, then insurer.
Starting Postgres in your browser…
Checking your access…