Medical coder to healthcare data analyst: denial analysis in SQL

Before: the coder's denial worklist

A typical revenue-cycle coder works from a list like this, usually in a shared spreadsheet or the practice-management system:

Every row is a task. The goal is to clear the list. Success is measured in claims worked per day and accuracy on audit.

This list contains most of what an analyst needs. It is simply read one row at a time.

After: the analyst's view of the same data

The analyst asks different questions of the same rows: which payer denies most, for which reason, and at which step the error was introduced. Here is that analysis on synthetic data.

CREATE TABLE claims (
  claim_id TEXT PRIMARY KEY,
  payer TEXT,
  coder TEXT,
  submitted_on TEXT,
  billed REAL
);
CREATE TABLE denials (
  claim_id TEXT,
  reason TEXT,
  origin TEXT
);
INSERT INTO claims VALUES
 ('CL-1040','Payer A','Coder 1','2026-09-01',4200),
 ('CL-1041','Payer A','Coder 2','2026-09-01',3100),
 ('CL-1042','Payer A','Coder 2','2026-09-02',5600),
 ('CL-1043','Payer B','Coder 1','2026-09-02',2800),
 ('CL-1044','Payer B','Coder 3','2026-09-03',7300),
 ('CL-1045','Payer B','Coder 3','2026-09-03',1900),
 ('CL-1046','Payer C','Coder 1','2026-09-04',6400),
 ('CL-1047','Payer B','Coder 1','2026-09-04',3500),
 ('CL-1048','Payer C','Coder 2','2026-09-05',2200),
 ('CL-1049','Payer A','Coder 3','2026-09-05',4800),
 ('CL-1050','Payer C','Coder 3','2026-09-06',3900),
 ('CL-1051','Payer A','Coder 2','2026-09-06',5100);
INSERT INTO denials VALUES
 ('CL-1042','Missing modifier','Coding'),
 ('CL-1044','Authorization absent','Front desk'),
 ('CL-1047','Authorization absent','Front desk'),
 ('CL-1051','Diagnosis does not support procedure','Coding'),
 ('CL-1045','Authorization absent','Front desk');

-- Denial rate and value at risk by payer
SELECT c.payer,
       COUNT(*) AS claims,
       COUNT(d.claim_id) AS denied,
       ROUND(100.0 * COUNT(d.claim_id) / COUNT(*), 1) AS denial_rate_pct,
       SUM(CASE WHEN d.claim_id IS NOT NULL THEN c.billed ELSE 0 END) AS denied_value
FROM claims c
LEFT JOIN denials d ON d.claim_id = c.claim_id
GROUP BY c.payer
ORDER BY denial_rate_pct DESC;

-- Where the denials start
SELECT origin, reason, COUNT(*) AS n
FROM denials
GROUP BY origin, reason
ORDER BY n DESC;

The first query returns:

payer    claims  denied  denial_rate_pct  denied_value
Payer B  4       3       75.0             12700.0
Payer A  5       2       40.0             10700.0
Payer C  3       0       0.0              0

The second shows that three of the five denials are "Authorization absent", all originating at the front desk, and all with Payer B.

What changed between the two artefacts

The most important row is "owner of the fix". In the synthetic data, the biggest problem is not coding at all. Payer B denials come from missing prior authorization before the visit. A coder can resubmit those claims forever; an analyst's recommendation is a pre-visit authorization check for Payer B, and a way to measure whether it worked.

Cautions a coder is well placed to raise

Your coding background lets you spot flaws a generalist analyst might miss:

• Small samples mislead. Twelve claims prove nothing; a real analysis needs months of data, and a rate for a payer with few claims needs a confidence caveat.
• Denial reasons are not always accurate. Payers sometimes return a generic reason. You know which ones to distrust.
• One claim can carry several denial lines. Count claims and denial lines separately, or rates inflate.
• Coder comparisons need care. If one coder handles the hardest specialty, a higher denial rate may reflect the work, not the person. Never publish a per-coder ranking without that context.

Put these in your project write-up. They show domain judgement, which is what makes a healthcare analyst useful.

The skills you need to add

• SQL: joins, LEFT JOIN for "claims with no denial", grouping, date filters.
• Excel and Power BI: a monthly denial dashboard by payer, reason and origin.
• Basic statistics: rates, trends and when a change is noise.
• Writing recommendations: one page, one fix, one measure of success.

Within the Data Analyst programme, a claims dataset like the one above would make a natural capstone subject.

Revenue cycle analyst as the first title

Healthcare data analyst, RCM analyst, denial management analyst and revenue cycle reporting analyst build directly on coding. Many revenue-cycle teams already have these roles next to the coding floor, which can make an internal move the most practical first step. Check whether a posting expects SQL or only Excel reporting; aim for the former.

How to explain it

For three years I coded outpatient claims and worked the denial queue. I noticed I was fixing the same authorization denials for one payer every week, and they weren't coding errors at all. So I learned SQL and built a denial analysis on synthetic claims: denial rate by payer, reason and where the error started. It showed the largest group came from the front desk, not coding, and I proposed a pre-visit check with a way to measure it. I want to be the person who finds those patterns, rather than the one clearing them by hand.

A coding CV tends to list payers and specialties but not analysis; paste yours into the free skill check and see whether SQL or reporting shows up at all. If modelling interests you more than reporting, clinical research coordinator to data scientist shows a route with more statistics.