Provider Analytics
Query hospital operations data, investigate quality metrics, and build CMS-ready reports for the board.
30 missions|5 tables
- “How many departments do we have?”
- “Average length of stay by department”
- “Month-over-month encounter trend”
- Real Hospital Scenarios — Answer questions from the CMO, VP of Operations, and CEO about readmissions, wait times, and patient outcomes.
- EHR Data Quality Issues — Duplicate patients, inconsistent department names, mixed date formats, and bad values that silently break your analysis.
- Provider-Specific Skills — Readmission rates, length of stay, ED throughput, utilization, CMS methodology, and age bucketing from DOB.
Clinical Operations Analytics
Practice the SQL patterns hospital hiring managers actually recognize: ICD-10 ↔ CCSR terminology joins, principal vs. secondary diagnosis logic, POA gotchas, HCC risk tiering, and the LEFT-JOIN-vs-INNER-JOIN trap that silently breaks production healthcare dashboards. Curriculum modeled on the patterns named in CMS-HCC, AHRQ CCSR, and the Tuva Project.
25 missions|7 tables
- “Pull the chronic disease list”
- “Top 3 codes per body system”
- “Department CCSR concentration (HHI)”
- Real Terminology Joins — Practice ICD-10 → CCSR → HCC the way Cogito and Caboodle analysts do — the foundational pattern absent from every other SQL learning platform.
- Principal vs. Secondary Logic — Cohort patients by principal diagnosis, aggregate comorbidities on those encounters — the HCC-recapture pattern run quarterly at every ACO and Medicare Advantage plan.
- Production-Grade Gotchas — POA NULL handling, ICD-9 legacy detection, and the LEFT-JOIN-vs-INNER-JOIN trap that’s the #1 silent bug in healthcare reporting.
Clinical Trials & Research Analytics
Practice the SQL patterns clinical research analysts and trial sponsors actually run: enrollment cohort analysis, arm balance checks, MedDRA-coded adverse event signal detection, visit adherence drift, and Kaplan-Meier-style retention curves. Real-world terminology — NCT-format trial IDs, CTCAE v5 severity grades 1-5, ATC drug classes, and MedDRA Preferred Terms — on a fully isolated 7-table dataset modeled on real phase 2 / phase 3 protocols.
25 missions|7 tables
- “Enrollments per site”
- “First adverse event per enrollment”
- “Site HHI — concentration of enrollments per therapeutic area”
- Authentic Trial Terminology — NCT-format trial IDs, MedDRA Preferred Terms for adverse events, CTCAE v5 severity grades, ATC drug classes. The vocabulary every clinical research analyst, biostatistician, and trial sponsor uses daily.
- Protocol & Cohort Analysis — Arm balance checks, screen-fail rates, eligibility strata, and per-arm safety comparisons — the SQL patterns that drive every interim analysis and DSMB report.
- Safety Signal Detection — Severity-by-arm cross-tabs, drug-vs-placebo AE rate deltas, and SAE cohort intersection — the analytics that flag a signal before the next protocol amendment.
Payer Claims & Appeals Analytics
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.
25 missions|6 tables
- “Claims volume by service month”
- “Provider denial-rate outliers (specialty-adjusted)”
- “Provider appeal-success ranking within specialty (RANK PARTITION)”
- 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.
Pharma Commercial Analytics
Practice the SQL patterns pharma commercial analysts run at Pfizer / Lilly / Novartis / Veeva: top prescribers by NRx, decile-based HCP segmentation, NRx vs TRx breakdowns, call-to-Rx attribution lag, territory market share HHI, switcher cohorts, persistence curves, and brand-launch performance scorecards. Per-Rx-claim grain on 24 months of data spanning a single-brand launch + in-class competitors. The patterns life-sciences commercial teams interview on.
25 missions|7 tables
- “HCPs in cardiology specialty”
- “Decile-based prescriber tiers — recompute from Rx volume”
- “Territory market share HHI — concentration of Rx volume”
- Authentic Pharma Commercial Schema — Per-Rx-claim grain (NRx vs TRx flag, days_supply, copay), HCP/NPI dimension with decile + target flag + specialty, rep activity with sample drops, payer plans by formulary tier, and normalized territory FK that mirrors how real IQVIA / Veeva / Komodo datasets are shaped.
- Launch + Competitor Narrative — Single launch brand (Cardiozin, a new statin) entered market month 17 of a 24-month window; 2-3 in-class competitors hold incumbent share; 5-6 unrelated products provide portfolio breadth. Every mission can hook into 'how is our launch tracking vs. competition?' — the same question every brand team asks every Monday.
- NRx, TRx & Persistence — Per-fill is_new_rx flag enables the canonical NRx (new prescriptions) vs TRx (total prescriptions including refills) split that drives every commercial dashboard. Days-supply distributions, refill-rate cohorts, and time-to-discontinuation curves — the patterns brand teams use to detect adherence and switching.
Hospital Revenue Cycle Management
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.
25 missions|7 tables
- “Medicare patients by demographic”
- “Days-in-AR by payer — open claims aging”
- “Payer concentration HHI per service line”
- 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.