Readmission Risk Scoring — Multi-Factor Patient Stratification

Predicting which patients are most likely to be readmitted within 30 days is one of the most impactful uses of hospital SQL analytics. This lesson builds a composite risk score from five clinical signals.

Risk Score Components

1. Build the Risk Score CTE

WITH patient_age AS (
    SELECT
        id AS patient_id,
        GREATEST(0, (DATE_PART('year', AGE(dob)) - 50) / 10)::INT AS age_score
    FROM hc_patients
),
prior_admissions AS (
    SELECT
        patient_id,
        GREATEST(0, COUNT(*) - 1) * 2  AS admission_score
    FROM hc_admissions
    WHERE admitted_at >= CURRENT_DATE - INTERVAL '24 months'
    GROUP BY patient_id
),
diagnosis_score AS (
    SELECT
        a.patient_id,
        COUNT(d.id) AS diag_score
    FROM hc_diagnoses d
    JOIN hc_admissions a ON a.id = d.admission_id
    GROUP BY a.patient_id
),
medication_score AS (
    SELECT
        a.patient_id,
        COUNT(m.id) AS med_score
    FROM hc_medications m
    JOIN hc_admissions a ON a.id = m.admission_id
    GROUP BY a.patient_id
),
lab_score AS (
    SELECT
        patient_id,
        COUNT(*) FILTER (WHERE is_abnormal) * 2 AS lab_abnormal_score
    FROM hc_lab_results
    GROUP BY patient_id
),
composite AS (
    SELECT
        p.id AS patient_id,
        p.name,
        p.mrn,
        p.insurance_type,
        COALESCE(pa.age_score, 0)         AS age_score,
        COALESCE(pr.admission_score, 0)   AS admission_score,
        COALESCE(ds.diag_score, 0)        AS diag_score,
        COALESCE(ms.med_score, 0)         AS med_score,
        COALESCE(ls.lab_abnormal_score, 0) AS lab_score,
        COALESCE(pa.age_score, 0)
          + COALESCE(pr.admission_score, 0)
          + COALESCE(ds.diag_score, 0)
          + COALESCE(ms.med_score, 0)
          + COALESCE(ls.lab_abnormal_score, 0) AS total_risk_score
    FROM hc_patients p
    LEFT JOIN patient_age         pa ON pa.patient_id = p.id
    LEFT JOIN prior_admissions    pr ON pr.patient_id = p.id
    LEFT JOIN diagnosis_score     ds ON ds.patient_id = p.id
    LEFT JOIN medication_score    ms ON ms.patient_id = p.id
    LEFT JOIN lab_score           ls ON ls.patient_id = p.id
)
SELECT
    patient_id,
    name,
    mrn,
    insurance_type,
    age_score,
    admission_score,
    diag_score,
    med_score,
    lab_score,
    total_risk_score,
    NTILE(4) OVER (ORDER BY total_risk_score DESC) AS risk_quartile,
    RANK()   OVER (ORDER BY total_risk_score DESC) AS risk_rank
FROM composite
ORDER BY total_risk_score DESC
LIMIT 30;

What This Returns

patient_id | name       | mrn      | total_risk_score | risk_quartile | risk_rank
-----------+------------+----------+------------------+---------------+----------
       261 | Patient 261| MRN00261 |               42 |             1 |         1
       391 | Patient 391| MRN00391 |               42 |             1 |         1
        11 | Patient 11 | MRN00011 |               41 |             1 |         3
...

2. Risk Tier Distribution

WITH scores AS (
    -- (same CTE chain as above, omitted for brevity)
    SELECT
        p.id AS patient_id,
        COALESCE((DATE_PART('year', AGE(p.dob)) - 50) / 10 * 1, 0)::INT AS total_risk_score
    FROM hc_patients p
),
tiers AS (
    SELECT
        patient_id,
        CASE
            WHEN total_risk_score >= 30 THEN 'High Risk (30+)'
            WHEN total_risk_score >= 15 THEN 'Medium Risk (15-29)'
            WHEN total_risk_score >=  5 THEN 'Low Risk (5-14)'
            ELSE 'Minimal Risk (<5)'
        END AS risk_tier
    FROM scores
)
SELECT risk_tier, COUNT(*) AS patients,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM tiers
GROUP BY risk_tier
ORDER BY MIN(
    CASE risk_tier
        WHEN 'High Risk (30+)'     THEN 1
        WHEN 'Medium Risk (15-29)' THEN 2
        WHEN 'Low Risk (5-14)'     THEN 3
        ELSE 4
    END
);

3. Department-Level Readmission Rates

WITH readmits AS (
    SELECT DISTINCT a1.ward_id, a1.id AS original_id
    FROM hc_admissions a1
    JOIN hc_admissions a2
        ON  a2.patient_id  = a1.patient_id
        AND a2.id         != a1.id
        AND a2.admitted_at  > a1.discharged_at
        AND a2.admitted_at <= a1.discharged_at + INTERVAL '30 days'
    WHERE a1.discharged_at IS NOT NULL
)
SELECT
    w.name,
    COUNT(a.id)              AS discharges,
    COUNT(r.original_id)     AS readmissions,
    ROUND(100.0 * COUNT(r.original_id) / NULLIF(COUNT(a.id), 0), 1) AS readmit_rate_pct,
    RANK() OVER (ORDER BY
        100.0 * COUNT(r.original_id) / NULLIF(COUNT(a.id), 0) DESC
    )                        AS ward_rank
FROM hc_admissions a
JOIN hc_wards w ON w.id = a.ward_id
LEFT JOIN readmits r ON r.original_id = a.id AND r.ward_id = a.ward_id
WHERE a.discharged_at IS NOT NULL
GROUP BY w.name
ORDER BY readmit_rate_pct DESC;

Key Takeaway

Multi-CTE risk scoring is a powerful and readable pattern. Each CTE computes one signal; the final composite CTE aggregates them. Using COALESCE(value, 0) handles patients with no admissions/labs without dropping them from the result. NTILE(4) divides patients into risk quartiles automatically regardless of the score distribution — no manual threshold-setting required. This architecture translates directly to clinical decision support systems.