Drill 3 of 12 in Healthcare questions
0

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…