Hospital Revenue Cycle Management Path

Denial rate spiked to 11% last week.
The CFO wants the AR cleanup plan by Monday.

Practice the SQL patterns hospital RCM analysts run at HCA / CommonSpirit / Epic shops / Athena: denial rate by CPT, days-in-AR by payer, first-pass yield, contractual underpayment detection, claim-to-remit lag, AR aging buckets, and provider productivity vs collections matrices. Per-charge / per-claim / per-remit grain on 24 months of provider-side billing data with planted denial cohorts and underpayment traps. The patterns billing analysts, denials specialists, and CFOs actually run.

See All Missions

25 missions (10 free · 15 Pro incl. 5 Master) · 7 tables · charge → claim → remit star schema

How It Works

1

Read the briefing

A Slack message from your manager

2

Explore the schema

5 tables in a star schema

3

Write your query

Full SQL editor with autocomplete

4

Get expert feedback

Graduated hints, not just pass/fail

Why This Path

Authentic RCM Schema

Per-charge / per-claim / per-remit grain mirroring how Epic Resolute, Athenahealth, and Cerner billing systems shape data. Patient and payer dims with primary/secondary insurance attribution, provider dim with department + credentialing flag, CPT charge-master with RVU, claim status transitions (submitted → paid / denied / appealed / closed), and remit-level allowed/paid/adjustment splits with denial codes.

Denial + AR Workflow Narrative

Planted denial cohorts (timely-filing, prior-auth, medical-necessity, coordination-of-benefits) drive the denials-management missions. Contractual underpayment plants test the M14 expected-vs-paid detection. AR aging follows realistic 0-30 / 31-60 / 61-90 / 90+ bucket distributions across payer mix.

Real RCM KPIs

Clean Claim Rate (first-pass yield), Days in AR, denial rate by CPT and payer, contractual yield, write-off rate, and the provider productivity views that surface the difference between high-volume billers and high-collections billers.

Resume-Grade Capstones

Multi-CTE RCM scorecards across every payer + every KPI, provider effectiveness paired pivots ranked within department, payer concentration HHI per service line, edit-attribution analyses (which front-end fixes prevent denials), and patient revenue segmentation capstones with multi-metric ranks. The SQL hospital RCM teams interview on.

The Missions

Each mission is a real request from someone at the company. Difficulty increases as you go.

Easy5 missions
1
Active payers by plan typeFrom: Billing Analyst
Medicare patients by demographicFrom: Billing Analyst
Charges per CPT codeFrom: Billing Analyst
Claims submitted per payerFrom: Billing Analyst
Total payments per providerFrom: Billing Analyst
Medium5 missions
Top 10 highest-revenue CPTsFrom: Denials Specialist
Clean claim rate per payerFrom: Denials Specialist
Monthly claim volume trendFrom: Denials Specialist
Denial rate per CPT — high-volume CPTs onlyFrom: Denials Specialist
AR aging bucketsFrom: AR Manager
Hard5 missions
Days-in-AR by payer — open claims agingProFrom: AR Manager
First-pass yield quartiles — NTILE on payer-level FPYProFrom: AR Manager
Top denial reason per department — RANK PARTITIONProFrom: CDI/Coding Lead
Underpayment detection — contracted vs paidProFrom: Payer Contracting Manager
Claim-to-remit lag — LAG window across event streamsProFrom: Payer Contracting Manager
Expert5 missions
Provider × CPT productivity matrixProFrom: RCM Director
Appeal overturn cohort — denied claims that paid after appealProFrom: RCM Director
Payer concentration HHI per service lineProFrom: RCM Director
Edit attribution — denials by front-end fix categoryProFrom: RCM Director
AR cohort persistence quartiles — claim-to-payment lag distributionProFrom: RCM Director
Master5 missions
RCM scorecard — every payer, every KPIProFrom: CFO
Provider effectiveness — charges vs collections delta, ranked within departmentProFrom: CFO
Payment-timing proxy across payer mixProFrom: CFO
Data quality audit — orphan claims + NULL denials in one reportProFrom: CFO
Patient revenue segmentation capstone — multi-metric ranks + segment labelProFrom: CFO

The Database

Hospital RCM dataset modeled on Epic Resolute / Athenahealth / Cerner billing-system shapes. 24-month window covering ~12K charges → ~10K claims → ~9.5K remits across 200 patients, 30 providers in 5 departments (Cardiology, Orthopedics, Internal Medicine, Surgery, ED), 50 CPT codes spanning E&M + procedures, and 10 payers with mixed plan types and contract terms. Planted denial cohorts (timely-filing, prior-auth, medical-necessity, COB), planted underpayments (paid < contracted), and intentional data-quality gaps (orphan claim_ids, NULL remit fields) drive the audit and reconciliation missions. Schema fully isolated from payer-claims (which models the same workflow from the insurer's POV).

dim_patient (200)dim_payer (10)dim_provider (30)dim_cpt (50)fact_charges (~12,000)fact_claims (~10,000)fact_remits (~9,500)

●dimension tables  ● fact tables

Build hospital RCM SQL on your resume

The patterns billing analysts, denials specialists, and CFOs actually run — on a real-shape charge / claim / remit dataset.

Looking for something different?

|