New to this domain?

A Hospital Revenue Cycle Management data primer

The vocabulary, the workflow, and the misconceptions — so the schema stops looking like alphabet soup before you write your first query.

The schema at a glance

Swipe horizontally to see fact tables →

dim_patient

PKpatient_id

patient_name

date_of_birth

gender

FKprimary_payer_id

FKsecondary_payer_id

dim_payer

PKpayer_id

payer_name

plan_type

expected_pct_of_billed

is_in_network

dim_provider

PKprovider_id

provider_name

specialty

department

is_credentialed

dim_cpt

PKcpt_code

description

service_line

charge_amount

rvu

fact_charges

PKcharge_id

FKpatient_id

FKprovider_id

FKcpt_code

service_date

billed_amount

posted_amount

fact_claims

PKclaim_id

FKcharge_id

FKpayer_id

submission_date

claim_status

submitted_amount

fact_remits

PKremit_id

FKclaim_id

posted_date

allowed_amount

paid_amount

adjustment_amount

denial_code

denial_reason

Dimension FactPK Primary KeyFK Foreign Key

Where the data comes from

The hospital revenue cycle is a three-step paper trail that follows a patient encounter. It starts when a clinician documents a service — "I saw this patient, performed this procedure, diagnosed this condition." Documentation gets coded into CPT and ICD-10 codes by the billing department, and a charge is created in the EHR’s billing module. The hospital’s billing team rolls up charges into a claim and submits it to the patient’s insurance payer (Medicare, Aetna, BCBS, etc.). The payer adjudicates the claim — accepting it, denying it, or paying part of it — and sends back an electronic remit (ERA / 835) detailing the allowed amount, paid amount, patient responsibility, and any denial reason codes. Days, weeks, or months later, the patient pays their portion and the cycle closes. The data captures every step: charges in fact_charges, claims in fact_claims, remits in fact_remits. Row counts shrink at each stage (12k → 10,969 → 9,693) because not every charge bills, and not every billed claim has been paid yet. The whole job is reasoning about that funnel.

Core entities

Charge
A charge is the first row that exists when a hospital does work for a patient. It captures what was done (CPT code), who did it (rendering provider), to whom (patient), and what the hospital intends to bill. A charge is the clinical truth of the encounter — created from clinician documentation. It does not yet say what the hospital will be paid; that’s negotiated downstream.
fact_charges — service_date, cpt_code, provider_id, patient_id, billed_amount
Claim
A claim is one or more charges packaged together and sent to a payer (insurance company) for reimbursement. It carries a submission date, a status (submitted / paid / denied / appealed), and the dollar amount the hospital is asking for. A claim is the financial ASK — what the hospital wants to be paid. The payer’s response lives on the remit.
fact_claims — claim_id, charge_id, payer_id, submission_date, claim_status, submitted_amount
Remit (ERA / 835)
A remit is the payer’s answer. For each claim, the remit says how much was allowed (the payer’s contracted price), how much was actually paid, how much the patient owes, how much was written off, and — if denied — why. Remits are why hospitals know whether they are getting paid the contracted rate or being underpaid; the answer always lives here, never on the claim itself.
fact_remits — claim_id, posted_date, allowed_amount, paid_amount, patient_responsibility, write_off, denial_code, denial_reason
Denial
A denial is when the payer refuses to pay all or part of a claim. Denials carry a CARC code (Claim Adjustment Reason Code — the standardized "why") and a denial reason in plain text. Denials can be soft (fixable, like a missing modifier) or hard (uncovered service). The hospital’s revenue depends on appealing the soft ones; the data lets you measure denial rate, denial reason mix, and overturn rate after appeal.
fact_remits.denial_code + fact_remits.denial_reason; appeal status on fact_claims.claim_status = 'appealed'

Vocabulary

Allowed amount: The dollar amount the payer recognizes as eligible for payment under the hospital’s contract. Not what the hospital gets paid — what the payer agrees the service is worth.
AR (accounts receivable): Open dollars the hospital is waiting to collect on submitted-but-not-yet-paid claims. The 90+ day bucket is where revenue dies.
CARC code: Claim Adjustment Reason Code. The standardized two- or three-character code on a remit explaining why a claim was adjusted or denied. CMS publishes the master list.
Clean claim: A claim that pays on the first submission with no rework or denial. Clean claim rate is the operational mirror of denial rate.
CPT code: Current Procedural Terminology. The 5-digit code identifying what service was performed. Different CPTs have different reimbursement rates, modifiers, and documentation requirements.
Days in A/R: Average number of days a hospital waits between submitting a claim and getting paid. CFOs read this number every Monday; high values mean cash is stuck.
Denial rate: Percentage of claims that came back denied. Different from first-pass yield because a claim can be pending without being denied.
First-pass yield (FPY): Percentage of claims that paid on the first submission with no rework. Operational quality metric for the billing team.
Modifier: A two-character code attached to a CPT that changes its meaning or payment (e.g. "26" professional component only, "59" distinct procedural service). Missing modifiers are a top denial driver.
NPI: National Provider Identifier. The unique 10-digit ID for any provider or facility billing US insurance. Joins between systems often pivot on NPI.
Patient responsibility: The dollar amount the patient owes after insurance: copay, deductible, coinsurance. A separate column on the remit; not the same as paid_amount.
Plan type: The category of insurance (HMO, PPO, Medicare Advantage, Medicaid). Different plan types have different denial and payment patterns. Lives on dim_payer.plan_type.
Underpayment: When the payer pays less than the contracted rate for a service. Detected by joining paid_amount against dim_payer.expected_pct_of_billed. Often hidden in the data.
Write-off: Dollars the hospital has decided not to chase, either because of contract terms (contractual adjustment) or because collection failed (bad debt). Different from denial.

Common misconceptions

Trap: Allowed amount is what the hospital gets paid.
Allowed amount is what the payer recognizes as eligible — the contracted price. Paid amount is what the payer actually sends after subtracting patient responsibility. They are almost never the same number.
Trap: A claim and a charge are the same thing.
A charge is one service. A claim packages one or more charges and submits them to a payer. One claim can carry many charges; one patient encounter usually generates several charges that may end up on one or several claims depending on payer rules.
Trap: If a claim isn’t paid, it must be denied.
A claim with no remit yet is in A/R (accounts receivable) — pending. Denial is an explicit payer response on a remit. Treating "no remit" as "denied" miscategorizes the entire pending pipeline as a quality problem instead of a cash-flow problem.
Trap: Modifiers are clerical metadata.
Modifiers can swing payment by tens of thousands of dollars. The same CPT with a "59" (distinct procedural service) modifier pays differently than the same CPT without it. Aggregating revenue at the CPT level alone hides the variance the billing team is paid to manage.
Trap: Days in A/R is just a count.
Days in A/R is the hospital’s cash conversion clock. Every day a claim sits in submitted status is a day the hospital can’t pay nurses with that money. CFOs gate Monday morning on this number. Modeling it correctly (open claims only, excluding paid and written-off) is a load-bearing skill.

Ready to start when…

If you can describe the charge → claim → remit chain in your own words and explain why allowed amount, paid amount, and patient responsibility are three different columns, mission 1 will feel approachable. The first 10 missions only ask you to query the dimensions and produce simple aggregates — no window functions until mission 11. You’re not behind; this is the on-ramp.

Open the Hospital Revenue Cycle Managementcurriculum →

What a hospital RCM analyst actually does

A hospital RCM analyst turns charge / claim / remit data into denial reports, AR aging dashboards, and underpayment recovery lists for the CFO and the billing director. You don’t see patients but you see their billing data daily — every encounter generates rows you reason about. A wrong denial-rate number can mean misallocated FTEs in the appeals team; a missed underpayment pattern can mean millions left on the table at contract renewal. The work is part SQL, part reading payer contracts, part sitting next to billers to learn why a denial code that looks like noise is actually a workflow problem.

The role varies by employer

Large IDN (integrated delivery network)
HCA, Ascension, CommonSpirit, Trinity. Standardized RCM across 15+ facilities, contract-management software (e.g. nThrive, FinThrive, Optum), heavy denial-prevention focus. Best comp ceiling and the most specialization — separate teams for denials, underpayments, AR follow-up, and contract modeling.
Community / regional hospital
Smaller billing department, broader analyst remit. One person may own denial reporting, AR aging, and the monthly revenue close. Less gated entry; a strong door if you come from a billing or coding background and want to move into analytics.
Physician group / MSO
Ambulatory-flavored RCM — wRVU productivity, payer mix, coding-edit denials, smaller dollar amounts but higher claim volume. Less inpatient complexity. Athena, eClinicalWorks, and NextGen show up more than Epic Resolute here.

Skills that actually get hired

SQL specifics

Conditional aggregation — SUM(CASE WHEN claim_status = 'denied' THEN 1 END) is the bread-and-butter denial-rate pattern.
Date arithmetic — days in A/R, posting lag, denial-to-appeal turnaround all reduce to date diffs.
Window functions (LAG, ROW_NUMBER, running sums) starting at mission 11 — used for first-pass yield, denial-then-appeal sequencing, and rolling AR snapshots.
LEFT JOIN with NULL filtering — the canonical "claims with no remit yet" pattern for open AR.

Domain knowledge

Charge → claim → remit chain and why row counts shrink at each stage.
CARC codes and the soft-vs-hard denial distinction; which denial reasons are actually appealable.
Contract terms: allowed amount vs paid amount, expected % of billed, capitated vs fee-for-service.
Plan-type variance — Medicare, Medicaid, commercial, and Medicare Advantage have different denial and payment patterns.

Tooling

Epic Resolute (HB / PB) at Epic shops; Cerner / Oracle Health and Athena at others. The schema differs but the chain doesn’t.
Tableau or Power BI for downstream dashboards — CFO weekly review, denial reason Pareto, AR aging by payer.
Excel still matters — contract modeling and one-off underpayment recovery lists live there.

The interview loop

1
Recruiter screen (30 min)
Fit check. Be ready to explain why RCM specifically (vs clinical or payer analytics). If you have biller or coder background, lead with it — it’s genuinely valued here.
2
Take-home or live SQL screen (60 min)
Almost always a denial-rate or days-in-AR query against a charge / claim / remit toy schema. They want conditional aggregation, correct open-AR filtering (claims with no remit yet), and a sensible denominator. Edge cases: appealed claims, partial payments, write-offs.
3
Hiring manager / billing director (45–60 min)
Domain depth. Expect "walk me through how you’d find underpayments" or "denial rate ticked up 3 points last month — how would you investigate?" They want to see you separate volume from rate, and reason about denial reason mix not just denial total.
4
Peer / domain panel
Senior analyst plus a biller or coder. They’re testing whether you can talk to the people who generate the data without pretending to know billing better than they do. Asking good questions matters more than knowing every CARC code.

Questions you’ll actually be asked

“How would you compute days in A/R by payer for open claims only?”: Filter to claims with no remit yet (LEFT JOIN fact_remits, IS NULL) or claim_status in ('submitted', 'appealed'). Compute the date diff from submission_date to today. Group by payer. The trap is letting paid or written-off claims into the denominator — days in A/R is a stock measure on open balances.
“Walk me through a denial rate calculation. What’s the right denominator?”: Numerator: claims with a denial_code on their remit, or claim_status = 'denied'. Denominator: claims that have been adjudicated (have a remit). Pending claims belong in AR analysis, not denial analysis. A denominator of "all submitted claims" mixes pending with adjudicated and inflates or deflates the rate depending on the AR backlog.
“How do you find underpayments?”: Join paid_amount on fact_remits to dim_payer.expected_pct_of_billed * fact_claims.submitted_amount (the contracted expected). The variance below expected is the underpayment. Sort by absolute dollars first — the recovery team works the largest gaps. The trap is computing percentage variance on small claims and surfacing $2 underpayments above $20K ones.
“What’s the difference between a charge and a claim, and why does it matter for analytics?”: A charge is one service line; a claim packages one or more charges to a payer. Counting unique claims when the question wants service volume undercounts; counting charges when the question wants billing actions overcounts. Always read the question for the grain — "how many denied procedures" is charge-level; "how many denied submissions" is claim-level.
“How would modifier mix affect a CPT-level revenue analysis?”: The same CPT with different modifiers reimburses differently — 26 (professional), TC (technical), 59 (distinct procedural service) all change the price. Aggregating revenue by CPT alone hides the variance modifiers explain. Group by CPT + modifier, or at least flag which CPTs have a meaningful modifier distribution before reporting averages.

What it pays

Level	Range	Notes
RCM Analyst (0–3 yr)	$60k–$85k	Community hospitals and physician groups at the low end; large IDNs and metro markets at the top. Billing or coding background pushes you toward the top of the band.
Senior RCM Analyst (3–6 yr)	$80k–$115k	Owns at least one CFO-visible KPI (denial rate, days in A/R, or underpayment recovery). Epic Resolute or Cerner experience pushes the top of the range.
RCM Manager (6–10 yr)	$110k–$155k	Running a team of 3–8 analysts at a large IDN or AMC. Bonus 10–20%. Comp ranges sourced from Levels.fyi, BLS OES, and Glassdoor; treat as ranges not point estimates.

Certifications — honest take

HFMA CRCR (Certified Revenue Cycle Representative)
Nice to have
Cheap, fast, broad coverage of the RCM workflow. Good signal for someone moving in from outside healthcare. Not a differentiator at senior levels.
AAPC CPC (Certified Professional Coder)
Nice to have
Useful for coding-adjacent or denial-prevention roles where you need to read CPT / ICD-10 with confidence. Overkill if your work is downstream reporting only.
Epic Resolute (HB or PB) / Cerner Revenue Cycle certification
Gold standard
Gold if employer-sponsored — Epic certs are gated and the sponsorship itself is the signal. Skip if you’d be paying out of pocket; the cert without job experience doesn’t open the doors people think it does.

How long it takes

Months 0–3: learn the schema (charges, claims, remits, the four dimensions) and ship a simple denial-rate dashboard. Months 3–9: own at least one CFO-visible KPI — days in A/R, first-pass yield, or denial reason Pareto — and build the appeals-impact view that goes with it. Months 9–18: lead an underpayment recovery cycle end-to-end (find the gap, prove the contract violation, hand a recovery list to the appeals team) and you’re the senior. Career-switchers from billing or coding compress this; pure analyst backgrounds without healthcare exposure stretch it by 6 months because the domain takes longer to internalize than the SQL.

Common mistakes to avoid

Treating "no remit yet" as a denial — it’s pending AR, a cash-flow problem, not a quality problem.
Reporting allowed amount as revenue — paid amount is what the hospital actually receives; allowed amount minus patient responsibility is closer to it.
Aggregating revenue by CPT without modifiers — the modifier mix is exactly the variance the billing team manages.
Computing days in A/R on all claims instead of open-only — paid and written-off claims dilute the metric and hide the cash-stuck story.
Treating Epic Resolute experience as the only marketable ticket — Cerner, Athena, and smaller-vendor experience is just as employable, and signaling otherwise narrows your search to a tenth of the actual market.

The trajectory

Stage	Years	Comp
RCM Analyst	0–3 yr	$60k–$85k
Senior RCM Analyst	3–6 yr	$80k–$115k
RCM Manager	6–10 yr	$110k–$155k
Director of Revenue Cycle Analytics	10+ yr	$150k–$210k

How the caseSQL curriculum maps to this

The caseSQL Hospital RCM path can’t teach you a specific vendor’s schema — Epic Resolute, Cerner, and Athena all label the same concepts differently. What it does teach is the reasoning: charge / claim / remit chain, conditional aggregation for denial rate, open-AR filtering for days in A/R, contract-rate joins for underpayment detection, and CARC-code mix for denial-reason Pareto. Those are the transferable skills that translate to any billing system. If mission 1 still feels intimidating after reading this, scroll back to the primer — the vocabulary is the whole on-ramp.