Coding shift detection

Get hospital-recognized SQL on your resume

Hospital operations star schema (shared with Provider Analytics path) plus authentic ICD-10 terminology and per-encounter diagnosis tables. ~5,800+ rows including ~5 legacy ICD-9 codes that survived the 2015 cutover (LEFT JOIN audit target), POA documentation gaps on ~9% of principal diagnoses, and a hidden coding shift on encounter 42 where the admit dx differs from the principal.

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.

Practice ICD-10 → CCSR → HCC the way Cogito and Caboodle analysts do — the foundational pattern absent from every other SQL learning platform.

Real Terminology Joins

Cohort patients by principal diagnosis, aggregate comorbidities on those encounters — the HCC-recapture pattern run quarterly at every ACO and Medicare Advantage plan.

Principal vs. Secondary Logic

POA NULL handling, ICD-9 legacy detection, and the LEFT-JOIN-vs-INNER-JOIN trap that’s the #1 silent bug in healthcare reporting.

Production-Grade Gotchas

Multi-CTE patient-level HCC chronic-burden score with risk tiering. “Built a multi-CTE chronic-disease burden score with HCC risk tiering” lands cleanly with every health-system, ACO, payer, and HealthTech hiring manager.

Resume-Grade Capstone

The chart says one thing. The codes say another.

Browse the cardiology codebook

Pull the chronic disease list

The HCC-eligible code list

Encounter diagnosis volume by type

The POA tally

Translate every diagnosis on the chart

Top 10 most-documented codes

Body system distribution

Top 10 chronic conditions

Orphan codes — codebook gaps

Top 3 codes per body system

POA documentation rate by code

Encounters carrying multiple HCCs

Encounter complexity score — top 10

Comorbidity co-occurrence matrix

Diabetic kidney disease cohort

Department CCSR concentration (HHI)

Acute MI bundle

Diagnosis chronicity ladder

Translate the codes — top principal diagnoses

The comorbidity profile — what travels with heart failure

CCSR service-line rollup

Data quality audit — POA gaps and legacy codes

HCC chronic burden — patient risk tiering capstone

patient_id

first_name

last_name

date_of_birth

gender

insurance_type

zip_code

primary_language

is_deceased

dim_patients

department_id

department_name

department_type

floor

bed_count

manager_name

dim_departments

provider_id

specialty

hire_date

is_active

dim_providers

metric_id

encounter_id

metric_type

metric_value

measurement_date

reported_by

fact_quality_metrics

admit_date

discharge_date

diagnosis_code

diagnosis_description

encounter_type

discharge_disposition

fact_encounters

icd_code

icd_description

ccsr_category

ccsr_description

body_system

is_chronic

hcc_flag

dim_icd

diagnosis_id

dx_type

poa_indicator

dx_rank

fact_diagnoses

Coding shift detection

The Brief

You'll practice