Become a Healthcare Data Analyst with SQL

This path teaches SQL on the shape of real healthcare data: patients, encounters, diagnoses, procedures, claims and prescriptions. You'll build patient cohorts from diagnosis codes, calculate length of stay and 30-day readmissions, compare providers fairly, measure medication adherence, and learn the privacy habits that are non-negotiable when you work with health information. Example tables are simplified and fictional; real systems add many more columns, but the patterns are the same.

1. 1

   The Healthcare Data Model

   30 min

   Most healthcare datasets revolve around a few core tables: patients, encounters (each visit or admission), diagnoses and procedures attached to encounters, claims for what was billed and paid, and prescriptions. One patient has many encounters, and one encounter has many diagnoses, so knowing the grain of each table prevents double counting.

   SELECT e.encounter_id, p.patient_id, e.encounter_type,
          e.admit_date, e.discharge_date, d.diagnosis_code, d.is_primary
   FROM encounters e
   JOIN patients p   ON p.patient_id = e.patient_id
   JOIN diagnoses d  ON d.encounter_id = e.encounter_id
   WHERE e.admit_date >= '2026-01-01';

   Joining encounters to diagnoses multiplies rows, one per diagnosis. Count DISTINCT encounter_id, or filter to the primary diagnosis, before summing anything per encounter.
   Lesson: ER Modeling & Relationships Lesson: JOINs Blog: When a JOIN multiplies totals
2. 2

   Working with Diagnosis & Procedure Codes

   30 min

   Conditions are recorded as codes, most commonly ICD-10 for diagnoses. Codes are hierarchical: every code starting with E11 is a form of type 2 diabetes. Match code families with LIKE or, better, a maintained code-group lookup table, so the definition of "diabetic patient" lives in one place.

   SELECT DISTINCT e.patient_id
   FROM diagnoses d
   JOIN encounters e ON e.encounter_id = d.encounter_id
   WHERE d.diagnosis_code LIKE 'E11%'      -- type 2 diabetes family
     AND e.admit_date >= '2025-10-01';

   Store codes without dots in a consistent format (or always with them). E11.9 and E119 won't match each other, and inconsistent formatting quietly shrinks cohorts.
   Lesson: LIKE & Wildcards Blog: String manipulation functions Blog: SQL data cleaning cheat sheet
3. 3

   Building Patient Cohorts

   35 min

   A cohort is a precisely defined group of patients: "adults aged 40-65 with a type 2 diabetes diagnosis and at least two outpatient visits in the past year". Build it step by step with CTEs, one criterion each, so the definition is auditable and the count at each step can be reported.

   WITH diabetic AS (
       SELECT DISTINCT e.patient_id
       FROM diagnoses d JOIN encounters e ON e.encounter_id = d.encounter_id
       WHERE d.diagnosis_code LIKE 'E11%'
   ),
   adults AS (
       SELECT patient_id FROM patients
       WHERE birth_date BETWEEN '1961-01-01' AND '1986-12-31'
   ),
   frequent AS (
       SELECT patient_id FROM encounters
       WHERE encounter_type = 'outpatient' AND admit_date >= '2025-10-01'
       GROUP BY patient_id HAVING COUNT(*) >= 2
   )
   SELECT COUNT(*) AS cohort_size
   FROM diabetic
   JOIN adults   USING (patient_id)
   JOIN frequent USING (patient_id);

   Report the patient count after each inclusion step (a "cohort funnel"). Clinicians and reviewers will ask why the final number is what it is.
   Lesson: Common Table Expressions Lesson: HAVING Clause Blog: Cohort analysis in SQL
4. 4

   Length of Stay

   25 min

   Length of stay (LOS) is the number of days between admission and discharge for inpatient encounters. Average LOS by department or diagnosis is a core operational metric. Exclude encounters still in progress (no discharge date) and decide explicitly how same-day discharges count.

   SELECT department,
          COUNT(*) AS admissions,
          ROUND(AVG(discharge_date - admit_date), 1) AS avg_los_days
   FROM encounters
   WHERE encounter_type = 'inpatient'
     AND discharge_date IS NOT NULL
   GROUP BY department
   ORDER BY avg_los_days DESC;

   Report the median alongside the average. A handful of very long stays can drag the average far above what a typical patient experiences.
   Blog: Median and percentiles Lesson: Date & Time Functions Blog: NULL handling in SQL
5. 5

   30-Day Readmission Rate

   40 min

   A readmission is an inpatient admission within 30 days of a previous discharge for the same patient, one of the most-watched quality measures in healthcare. LEAD() finds each patient's next admission in a single pass, without a self-join.

   WITH stays AS (
       SELECT patient_id, encounter_id, admit_date, discharge_date,
              LEAD(admit_date) OVER (PARTITION BY patient_id ORDER BY admit_date) AS next_admit
       FROM encounters
       WHERE encounter_type = 'inpatient' AND discharge_date IS NOT NULL
   )
   SELECT COUNT(*) AS discharges,
          SUM(CASE WHEN next_admit - discharge_date <= 30 THEN 1 ELSE 0 END) AS readmissions_30d,
          ROUND(100.0 * SUM(CASE WHEN next_admit - discharge_date <= 30 THEN 1 ELSE 0 END) / COUNT(*), 1) AS readmission_rate_pct
   FROM stays;

   Official readmission measures have detailed exclusion rules (planned readmissions, transfers, deaths). Use this pattern to learn the mechanics, and follow the formal specification when reporting externally.
   Blog: LEAD and LAG examples Blog: Gaps and islands Lesson: Window Functions
6. 6

   Provider Performance & Fair Comparisons

   30 min

   Comparing providers or hospitals by raw averages is misleading: one that treats sicker patients will look worse even if its care is better. Compare like with like by grouping within diagnosis or risk groups, show volumes next to rates, and flag small numbers that make rates unstable.

   SELECT provider_id, primary_dx_group,
          COUNT(*)                         AS cases,
          ROUND(AVG(total_paid), 0)        AS avg_cost,
          CASE WHEN COUNT(*) < 20 THEN 'low volume' ELSE 'ok' END AS reliability
   FROM claims_summary
   GROUP BY provider_id, primary_dx_group
   ORDER BY primary_dx_group, avg_cost DESC;

   A rate based on 4 cases can swing from 0% to 50% with one patient. Always show the denominator.
   Blog: Percent of total Blog: Top N per category Lesson: GROUP BY
7. 7

   Claims & Medication Adherence

   35 min

   Claims tables show what was billed, allowed and paid, often with adjustments and reversals as extra rows. Medication adherence is commonly measured as the share of days in a period a patient had medication available, built from prescription fill dates and days' supply.

   SELECT patient_id,
          SUM(days_supply)                                    AS days_covered,
          ROUND(100.0 * LEAST(SUM(days_supply), 180) / 180, 1) AS pct_days_covered
   FROM pharmacy_fills
   WHERE drug_class = 'statin'
     AND fill_date BETWEEN '2026-01-01' AND '2026-06-29'
   GROUP BY patient_id;

   Net out reversed and adjusted claims before summing payments. Summing every claim line usually overstates cost, sometimes badly.
   Blog: Running totals Blog: Correct rows, wrong numbers Blog: 7 types of duplicate data
8. 8

   Privacy-Aware SQL

   25 min

   Health data is among the most sensitive data there is, and regulations such as HIPAA in the US and India's DPDP Act govern how it is handled. Analysts follow the minimum necessary principle: select only the columns you need, prefer de-identified or aggregated data, never export identifiers you don't need, and suppress small counts that could identify individuals.

   SELECT diagnosis_group, age_band,
          CASE WHEN COUNT(*) < 11 THEN NULL ELSE COUNT(*) END AS patients  -- suppress small cells
   FROM deidentified_encounters
   GROUP BY diagnosis_group, age_band;

   Use age bands and partial postcodes instead of birth dates and full addresses in shared reports. Combinations of "harmless" fields can re-identify a patient.
   Lesson: Views Blog: SQL security best practices Blog: Healthcare data analysis project