The denial rate is denied claims over adjudicated claims: those the payer has decided, paid or denied. Pending claims have no decision yet, so they stay out of both counts.
SELECT ROUND(COUNT(*) FILTER (WHERE status = 'denied') * 100.0
/ COUNT(*) FILTER (WHERE status IN ('paid', 'denied')), 1) AS denial_rate_pct
FROM hd_claims;
FILTER (WHERE ...) counts only the rows that match, so one query holds both counts. The pitfalls the exercises check:
- Dividing by every claim, pending included, which makes the rate look lower than it is.
- Integer division:
COUNT(*) * 100 / COUNT(*) drops the decimals. Multiply by 100.0.
- A different measure: denied dollars over billed dollars is not the denial rate, which counts claims. Report both if they help, each under its own name.
- Averaging the payers' rates to get a system rate: a small payer would weigh as much as Medicare.
Denied claims over adjudicated claims.