The one rule that matters most: privacy first

Never use a current or former employer's real patient data in a public portfolio. Healthcare data is protected by strict regulations (HIPAA in the US, and equivalents elsewhere), and improperly de-identified or unauthorized data in a public GitHub repo is a serious legal and ethical problem — not just a bad look. Every dataset suggested below is either fully synthetic or properly de-identified and released specifically for research and educational use.

Which datasets are actually safe to use

DatasetWhat it isAccess
SyntheaFully synthetic patient generator — no real people at allFree, no application needed
MIMIC-III / MIMIC-IVReal, de-identified ICU data from a US hospital, widely used in researchRequires a data-use agreement and short training course via PhysioNet
CMS Public Use FilesAggregated, already-anonymized US Medicare claims summariesFree, public
eICU Collaborative Research DatabaseMulti-center de-identified ICU dataRequires credentialed access via PhysioNet

Synthea is the easiest starting point for a portfolio — since it's generated, not real, there's zero re-identification risk, and it comes with a realistic relational schema out of the box.

A minimal healthcare schema

schema.sql
CREATE TABLE patients (
  patient_id   INT PRIMARY KEY,
  birth_date   DATE,
  gender       VARCHAR(10)
);

CREATE TABLE admissions (
  admission_id     INT PRIMARY KEY,
  patient_id       INT REFERENCES patients(patient_id),
  admission_date   DATE NOT NULL,
  discharge_date   DATE,
  primary_diagnosis VARCHAR(100),
  total_cost       DECIMAL(10,2)
);

Query 1 — 30-day readmission rate

A self-join finds admission pairs where the second admission starts within 30 days of the first one's discharge:

readmission-rate.sql
WITH readmit_pairs AS (
  SELECT DISTINCT a1.patient_id
  FROM admissions a1
  JOIN admissions a2
    ON a1.patient_id = a2.patient_id
   AND a2.admission_date > a1.discharge_date
   AND a2.admission_date <= a1.discharge_date + INTERVAL '30 days'
)
SELECT
  COUNT(DISTINCT r.patient_id) AS readmitted_patients,
  COUNT(DISTINCT a.patient_id) AS total_discharged_patients,
  ROUND(COUNT(DISTINCT r.patient_id) * 100.0 / COUNT(DISTINCT a.patient_id), 1) AS readmission_rate_pct
FROM admissions a
LEFT JOIN readmit_pairs r ON r.patient_id = a.patient_id;

Query 2 — Average length of stay by diagnosis

length-of-stay.sql
SELECT
  primary_diagnosis,
  COUNT(*) AS num_admissions,
  ROUND(AVG(discharge_date - admission_date), 1) AS avg_length_of_stay_days
FROM admissions
WHERE discharge_date IS NOT NULL
GROUP BY primary_diagnosis
HAVING COUNT(*) >= 10   -- suppress noisy averages from tiny groups
ORDER BY avg_length_of_stay_days DESC;

The HAVING COUNT(*) >= 10 filter matters here specifically — an average computed from 2 or 3 admissions is not a reliable number, and presenting it without that caveat is misleading.

Query 3 — Most common diagnosis by age group

diagnosis-by-age.sql
WITH patient_age AS (
  SELECT
    a.admission_id,
    a.primary_diagnosis,
    CASE
      WHEN DATE_PART('year', a.admission_date - p.birth_date) < 18 THEN '0-17'
      WHEN DATE_PART('year', a.admission_date - p.birth_date) < 40 THEN '18-39'
      WHEN DATE_PART('year', a.admission_date - p.birth_date) < 65 THEN '40-64'
      ELSE '65+'
    END AS age_group
  FROM admissions a
  JOIN patients p ON p.patient_id = a.patient_id
),
ranked AS (
  SELECT
    age_group, primary_diagnosis,
    COUNT(*) AS diagnosis_count,
    RANK() OVER (PARTITION BY age_group ORDER BY COUNT(*) DESC) AS rnk
  FROM patient_age
  GROUP BY age_group, primary_diagnosis
)
SELECT age_group, primary_diagnosis, diagnosis_count
FROM ranked
WHERE rnk = 1;

Query 4 — Cost per diagnosis category

cost-per-diagnosis.sql
SELECT
  primary_diagnosis,
  COUNT(*) AS num_admissions,
  ROUND(AVG(total_cost), 2) AS avg_cost,
  ROUND(SUM(total_cost), 2) AS total_cost
FROM admissions
GROUP BY primary_diagnosis
ORDER BY total_cost DESC
LIMIT 10;

Common mistakes

  • Using real, unauthorized patient data. By far the most serious mistake possible in this project category — always start from Synthea or a properly credentialed, de-identified source.
  • Reporting rates from tiny sample sizes without a caveat. A "50% readmission rate" based on 2 patients is not a meaningful statistic.
  • Confusing prevalence with rate. "Most common diagnosis" (a count) and "highest readmission rate for a diagnosis" (a ratio) answer different questions and shouldn't be presented interchangeably.
  • Ignoring open admissions. Rows with a NULL discharge date (still admitted) will corrupt a length-of-stay average if not filtered out.
  • Publishing findings that could re-identify individuals even from a supposedly de-identified dataset, particularly with very small subgroup breakdowns.

Key takeaways

  • Dataset choice is the most important decision in a healthcare project — use Synthea or properly credentialed, de-identified sources only.
  • Readmission rate is a self-join on discharge-to-next-admission date, within a defined window (commonly 30 days).
  • Always show the underlying count alongside any computed rate from a small subgroup.
  • Filter out still-open admissions before computing length-of-stay averages.
  • Never use real, unauthorized patient data — synthetic and properly licensed data demonstrate the same SQL skill with zero privacy risk.