Healthcare analytics has the same SQL syntax as every other analyst domain and almost none of the same habits. The data shape is different (encounters, not events; members, not customers), the definitions are regulated (CMS specs, HEDIS rules), and the mistakes are costly in ways a marketing analyst never experiences (HIPAA audits, CMS quality-report rework, misidentified patients). This post is the working shape of the role — the five query patterns a healthcare analyst writes every week.

Examples use a simplified schema: `dim_patients`, `dim_providers`, `dim_departments`, `fact_encounters` (admit/discharge records), `fact_quality_metrics` (observations on encounters). This is close to the caseSQL provider path schema; if you’ve done those missions, the shape will be familiar.

Three things separate healthcare analytics from consumer / product analytics:

HEDIS (Healthcare Effectiveness Data and Information Set) is the quality-measurement standard payers report to NCQA. Every measure has the same shape: a _denominator_ (the population eligible for the measure) and a _numerator_ (the subset that received the appropriate care). The rate is numerator / denominator.

-- HEDIS-style: Breast cancer screening rate for women 50-74 in 2025
WITH denominator AS (
  -- Women aged 50-74 with continuous enrollment during the measurement year
  SELECT patient_id
  FROM dim_patients
  WHERE gender = 'F'
    AND EXTRACT(YEAR FROM AGE(CURRENT_DATE, dob)) BETWEEN 50 AND 74
    AND enrollment_start <= '2024-01-01'
    AND enrollment_end   >= '2025-12-31'
),
numerator AS (
  -- The subset of the denominator who had a mammogram in 2024 or 2025
  SELECT DISTINCT d.patient_id
  FROM denominator d
  JOIN fact_encounters e USING (patient_id)
  WHERE e.cpt_code IN ('77057', '77063', '77067')
    AND e.service_date BETWEEN '2024-01-01' AND '2025-12-31'
)
SELECT
  (SELECT COUNT(*) FROM denominator) AS denom,
  (SELECT COUNT(*) FROM numerator)   AS numer,
  (SELECT COUNT(*) FROM numerator) * 100.0 /
    NULLIF((SELECT COUNT(*) FROM denominator), 0) AS screening_rate_pct;

Don’t invent the HEDIS logic from scratch. NCQA publishes the exact value-sets (CPT, ICD, HCPCS codes) and the inclusion/exclusion rules for every measure. Your query operationalizes their spec; your job is to make it match their rules exactly, not to decide what "breast cancer screening" means.

The definition has a spec

30-day readmissions — the definition actually matters

Readmission rate is the most-reported and most-frequently-miscomputed healthcare metric. "Patient discharged, came back within 30 days" sounds simple; the real CMS definition has:

-- All-cause 30-day readmission rate, self-join on the encounters table
WITH index_admissions AS (
  SELECT
    encounter_id AS index_encounter_id,
    patient_id,
    discharge_date
  FROM fact_encounters
  WHERE encounter_type = 'inpatient'
    AND discharged_alive = true
    AND discharge_date BETWEEN '2025-01-01' AND '2025-11-30'
),
readmits AS (
  SELECT
    i.index_encounter_id,
    i.patient_id,
    MIN(r.admit_date) AS readmit_date
  FROM index_admissions i
  JOIN fact_encounters r
    ON r.patient_id = i.patient_id
   AND r.encounter_type = 'inpatient'
   AND r.admit_date  >  i.discharge_date
   AND r.admit_date <= i.discharge_date + INTERVAL '30 days'
   AND r.encounter_id <> i.index_encounter_id
  GROUP BY i.index_encounter_id, i.patient_id
)
SELECT
  COUNT(*) FILTER (WHERE r.index_encounter_id IS NOT NULL) * 100.0 /
    COUNT(*) AS readmit_rate_pct,
  COUNT(*) AS index_admissions
FROM index_admissions i
LEFT JOIN readmits r USING (index_encounter_id);

Claims vs clinical: the reconciliation problem

Healthcare organizations run on two parallel data systems: claims (what the payer was billed) and clinical (what the EHR recorded). They describe the same encounters and disagree constantly. Reconciling them is a standing analyst task.

The pattern: FULL OUTER JOIN the two sources on patient_id + encounter date window, then categorize every row as "in both," "claims only," or "clinical only." The non-matching rows ARE the analysis — they tell you where the two systems drift.

PHI: the handling rules that keep you employed

A short list that every healthcare analyst internalizes in week one. These aren’t guidelines; breaking them has gotten analysts walked out by security.

Healthcare SQL rewards analysts who internalize the domain constraints before the syntax. A perfectly-written query against a wrong HEDIS definition is worse than a slower query against the right one — because the first one ships a number that legal, compliance, and CMS will all have to live with.

A practical guide to the SQL that healthcare analysts actually write on the job — HEDIS measure logic, readmission rates, claims-vs-clinical reconciliation, and PHI handling.

Healthcare

SQL for Healthcare Analysts: The Queries That Matter