Patient Outcome Analysis — Discharge Disposition

Discharge disposition (where a patient goes after leaving hospital) is the primary outcome metric for quality reporting. This lesson analyses disposition distribution, LOS by outcome, and ICU admission rates by diagnosis.

1. Discharge Disposition Distribution

SELECT
    COALESCE(discharge_disposition, 'Still Admitted') AS disposition,
    COUNT(*)                                           AS count,
    ROUND((100.0 * COUNT(*) / SUM(COUNT(*)) OVER ())::NUMERIC, 1) AS pct_of_all
FROM hc_admissions
GROUP BY discharge_disposition
ORDER BY count DESC;

What This Returns

disposition    | count | pct_of_all
---------------+-------+-----------
Home           |   126 |       18.0
Rehab          |   126 |       18.0
SNF            |   126 |       18.0
Expired        |   126 |       18.0
AMA            |   126 |       18.0
Still Admitted |    70 |       10.0

2. Length of Stay by Discharge Outcome

Outcomes with longer LOS indicate more complex or deteriorating cases:

SELECT
    discharge_disposition,
    COUNT(*)                                               AS discharges,
    ROUND((AVG(
        EXTRACT(EPOCH FROM (discharged_at - admitted_at)) / 86400
    ))::NUMERIC, 1)                                                  AS avg_los_days,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (discharged_at - admitted_at)) / 86400
    )::NUMERIC, 1)                                         AS median_los_days
FROM hc_admissions
WHERE discharged_at IS NOT NULL
GROUP BY discharge_disposition
ORDER BY avg_los_days DESC;

3. ICU Admission Rates by Diagnosis

Which primary diagnoses most often result in ICU placement?

SELECT
    d.icd_code,
    d.description,
    COUNT(DISTINCT a.id)                                    AS total_admissions,
    COUNT(DISTINCT a.id) FILTER (WHERE w.type = 'Intensive Care') AS icu_admissions,
    ROUND((100.0 *
        COUNT(DISTINCT a.id) FILTER (WHERE w.type = 'Intensive Care')
        / NULLIF(COUNT(DISTINCT a.id), 0))::NUMERIC, 1)              AS icu_rate_pct
FROM hc_diagnoses d
JOIN hc_admissions a ON a.id = d.admission_id
JOIN hc_wards w      ON w.id = a.ward_id
WHERE d.is_primary = true
GROUP BY d.icd_code, d.description
HAVING COUNT(DISTINCT a.id) >= 5
ORDER BY icu_rate_pct DESC;

4. Mortality Rate by Ward

Expired disposition = in-hospital death:

SELECT
    w.name,
    COUNT(a.id) FILTER (WHERE a.discharged_at IS NOT NULL)                AS total_discharges,
    COUNT(a.id) FILTER (WHERE a.discharge_disposition = 'Expired')        AS deaths,
    ROUND((100.0 *
        COUNT(a.id) FILTER (WHERE a.discharge_disposition = 'Expired')
        / NULLIF(COUNT(a.id) FILTER (WHERE a.discharged_at IS NOT NULL), 0))::NUMERIC, 1)                                                                    AS mortality_rate_pct
FROM hc_admissions a
JOIN hc_wards w ON w.id = a.ward_id
GROUP BY w.name
ORDER BY mortality_rate_pct DESC;

5. Against Medical Advice (AMA) Discharges — Risk Profile

AMA discharges are associated with higher readmission risk. Profile these patients:

SELECT
    p.insurance_type,
    COUNT(a.id)                          AS ama_discharges,
    ROUND((AVG(
        EXTRACT(EPOCH FROM (a.discharged_at - a.admitted_at)) / 86400
    ))::NUMERIC, 1)                                AS avg_los_before_ama,
    ROUND((AVG(
        DATE_PART('year', AGE(p.dob))
    ))::NUMERIC, 0)                                AS avg_patient_age
FROM hc_admissions a
JOIN hc_patients p ON p.id = a.patient_id
WHERE a.discharge_disposition = 'AMA'
GROUP BY p.insurance_type
ORDER BY ama_discharges DESC;

Key Takeaway

SUM(COUNT(*)) OVER () computes a grand total across all rows in the result set — this is how you calculate percentages within a single query without a subquery. Combining FILTER (WHERE condition) with window functions (OVER ()) enables multi-metric pivoting in a single scan, which is dramatically more efficient than multiple joins to the same table.

← Previous Next →