Payer Claims & Appeals Analytics Path

The denial rate ticked up.
Claims ops needs a CARC breakdown by close.

Practice the SQL patterns payer claims and appeals analysts actually run: denial rate by CPT, CARC/RARC code frequency analysis, appeal-overturn pivots by level, out-of-network leakage cohorts, and provider-scorecard composites against a real-shape claims ledger. X12 835 standard codes, plan metal tiers, and the adjudication patterns every health-plan analytics team writes daily.

See All Missions

25 missions (10 free · 15 Pro incl. 5 Master) · 6 tables · CARC/RARC denial codes, X12 835 standard

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 Payer Terminology

X12 835 CARC and RARC denial codes, CPT procedure codes, place-of-service, plan metal tiers (Bronze/Silver/Gold/Platinum), and HMO/PPO/EPO plan types. The vocabulary every claims analyst, appeals coordinator, and payer revenue-integrity team uses daily.

Denial & Appeal Mechanics

Denial rate by CPT, CARC frequency × category cross-tabs, overturn% by appeal level, and turnaround-time percentiles — the SQL patterns that drive every monthly denial-management review.

Network Leakage & Adequacy

Out-of-network provider cohorts via anti-join, network adequacy gaps by specialty × region, and provider denial-rate outliers (specialty-adjusted) — the analytics that surface contracting and access risks.

Resume-Grade Capstones

Multi-CTE claim-lifecycle funnels, recoverable-revenue projections at historical overturn rates, and percentile-banded provider scorecards. The SQL payer analytics 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 members by plan_typeFrom: Payer Operations Manager
Claims volume by service monthFrom: Finance Analyst
Top 5 specialties by claim countFrom: Network Management Lead
Out-of-network provider listFrom: Network Contracting Coordinator
Denied claims this quarterFrom: Denials Manager
Medium5 missions
Denial rate by CPT (top 10)From: Revenue Cycle Director
Avg paid_amt by plan_type × fundingFrom: Finance Analyst
CARC code frequency × categoryFrom: Denials Manager
Member denial-rate cohort by regionFrom: Population Health Analyst
First-pass appeal volume by levelFrom: Appeals Manager
Hard5 missions
Provider denial-rate outliers (specialty-adjusted)ProFrom: Network Manager
Top denial concentration via HHI on CARC codesProFrom: Revenue Integrity Lead
Rolling 90-day denial trendProFrom: Claims Operations Lead
CPT bundling (CO-97) leakage by providerProFrom: Revenue Integrity Lead
Appeals TAT percentiles by level (NTILE)ProFrom: Appeals Manager
Expert5 missions
Overturn% by level × denial_category (MAX(CASE) pivot)ProFrom: Revenue Integrity Lead
OON leakage cohort (anti-join)ProFrom: Network Manager
Provider appeal-success ranking within specialty (RANK PARTITION)ProFrom: Network Manager
Sliding-window repeat-denial detection (LAG within member-CPT)ProFrom: Appeals Coordinator
Plan-type adverse selection: high-utilizer concentration (NTILE × cumulative %)ProFrom: Actuarial Analyst
Master5 missions
End-to-end claim lifecycle funnelProFrom: VP Claims Operations
Recoverable revenue at historical overturn rateProFrom: Revenue Cycle Director
Provider scorecard composite, percentile-bandedProFrom: Network Manager
Member retention proxy: enrollment-cohort decayProFrom: Population Health Analyst
Network adequacy gap: specialty × regionProFrom: Network Manager

The Database

Payer claims dataset modeled on real commercial / Medicare-Advantage health-plan adjudication. Members enrolled across HMO/PPO/EPO plans by metal tier, providers in/out of network with NPI and specialty, and a fact_claims ledger with CPT codes, place-of-service, billed/allowed/paid amounts, and adjudication status. Denials carry X12 835 CARC and RARC codes (CO-97 bundling, CO-50 medical necessity, PR-1 deductible, etc.) and a denial_category roll-up. Appeals track level (1, 2, External), filed/decision dates, outcome, and appellant_type. All tables are net-new; no overlap with the provider, clinical-ops, or clinical-trials paths.

dim_members (~1,000)dim_providers_payer (~80)dim_plans (8)fact_claims (~12,000)fact_denials (~2,400)fact_appeals (~600)

●dimension tables  ● fact tables

Build payer SQL on your resume

The patterns health-plan analytics teams interview on — on a real-shape claims dataset.

Looking for something different?

|