Member retention proxy: enrollment-cohort decay

Build payer SQL on your resume

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.

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.

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.

Authentic Payer Terminology

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.

Denial & Appeal Mechanics

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.

Network Leakage & Adequacy

Multi-CTE claim-lifecycle funnels, recoverable-revenue projections at historical overturn rates, and percentile-banded provider scorecards. The SQL payer analytics teams interview on.

Resume-Grade Capstones

The denial rate ticked up.

Active members by plan_type

Claims volume by service month

Top 5 specialties by claim count

Out-of-network provider list

Denied claims this quarter

Denial rate by CPT (top 10)

Avg paid_amt by plan_type × funding

CARC code frequency × category

Member denial-rate cohort by region

First-pass appeal volume by level

Provider denial-rate outliers (specialty-adjusted)

Top denial concentration via HHI on CARC codes

Rolling 90-day denial trend

CPT bundling (CO-97) leakage by provider

Appeals TAT percentiles by level (NTILE)

Overturn% by level × denial_category (MAX(CASE) pivot)

OON leakage cohort (anti-join)

Provider appeal-success ranking within specialty (RANK PARTITION)

Sliding-window repeat-denial detection (LAG within member-CPT)

Plan-type adverse selection: high-utilizer concentration (NTILE × cumulative %)

End-to-end claim lifecycle funnel

Recoverable revenue at historical overturn rate

Provider scorecard composite, percentile-banded

Network adequacy gap: specialty × region

member_id

plan_id

age_band

region

enrollment_start

enrollment_end

dim_members

plan_name

plan_type

funding

metal_tier

dim_plans

provider_id

specialty

network_status

state

dim_providers_payer

denial_id

claim_id

denied_date

carc_code

rarc_code

denial_category

fact_denials

service_date

cpt_code

place_of_service

billed_amt

allowed_amt

paid_amt

status

fact_claims

appeal_id

level

filed_date

decision_date

outcome

appellant_type

fact_appeals

Member retention proxy: enrollment-cohort decay

The Brief

You'll practice