AR cohort persistence quartiles — claim-to-payment lag distribution

Build hospital RCM SQL on your resume

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).

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.

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.

Authentic RCM Schema

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.

Denial + AR Workflow Narrative

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.

Real RCM KPIs

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.

Resume-Grade Capstones

Denial rate spiked to 11% last week.

Active payers by plan type

Medicare patients by demographic

Charges per CPT code

Claims submitted per payer

Total payments per provider

Top 10 highest-revenue CPTs

Clean claim rate per payer

Monthly claim volume trend

Denial rate per CPT — high-volume CPTs only

AR aging buckets

Days-in-AR by payer — open claims aging

First-pass yield quartiles — NTILE on payer-level FPY

Top denial reason per department — RANK PARTITION

Underpayment detection — contracted vs paid

Claim-to-remit lag — LAG window across event streams

Provider × CPT productivity matrix

Appeal overturn cohort — denied claims that paid after appeal

Payer concentration HHI per service line

Edit attribution — denials by front-end fix category

RCM scorecard — every payer, every KPI

Provider effectiveness — charges vs collections delta, ranked within department

Payment-timing proxy across payer mix

Data quality audit — orphan claims + NULL denials in one report

Patient revenue segmentation capstone — multi-metric ranks + segment label

patient_id

patient_name

date_of_birth

gender

primary_payer_id

secondary_payer_id

dim_patient

payer_id

payer_name

plan_type

expected_pct_of_billed

is_in_network

dim_payer

provider_id

provider_name

specialty

department

is_credentialed

dim_provider

cpt_code

description

service_line

charge_amount

dim_cpt

charge_id

service_date

billed_amount

posted_amount

fact_charges

claim_id

submission_date

claim_status

submitted_amount

fact_claims

remit_id

posted_date

allowed_amount

paid_amount

adjustment_amount

denial_code

denial_reason

fact_remits

AR cohort persistence quartiles — claim-to-payment lag distribution

The Brief

You'll practice