Admissions Analysis — Volume, Length of Stay, and Readmissions
Admissions Analysis — Volume, Length of Stay, and Readmissions
Understanding admission patterns is the foundation of hospital operations. This lesson covers four essential queries: daily volume trends, length-of-stay distributions, 30-day readmission detection, and frequent-flyer identification.
1. Daily Admission Volume Trends
Track how many patients are admitted each day. Use DATE_TRUNC to group timestamps into day buckets.
SELECT
DATE_TRUNC('day', admitted_at)::DATE AS admission_date,
COUNT(*) AS admissions,
COUNT(*) FILTER (WHERE admission_type = 'Emergency') AS emergency,
COUNT(*) FILTER (WHERE admission_type = 'Elective') AS elective,
COUNT(*) FILTER (WHERE admission_type = 'Transfer') AS transfer
FROM hc_admissions
WHERE admitted_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;For a 7-day rolling average to smooth noise:
SELECT
admission_date,
admissions,
ROUND(AVG(admissions) OVER (
ORDER BY admission_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 1) AS rolling_7d_avg
FROM (
SELECT
DATE_TRUNC('day', admitted_at)::DATE AS admission_date,
COUNT(*) AS admissions
FROM hc_admissions
GROUP BY 1
) daily
ORDER BY admission_date;What This Returns
admission_date | admissions | rolling_7d_avg
---------------+------------+---------------
2023-01-01 | 3 | 3.0
2023-01-02 | 4 | 3.5
2023-01-03 | 3 | 3.3
...2. Average and Median Length of Stay by Ward
Length of stay (LOS) is a key efficiency metric. Use PERCENTILE_CONT for median — it is more robust than AVG because a single 60-day outlier can skew the average significantly.
SELECT
w.name AS ward,
COUNT(a.id) AS total_admissions,
ROUND(AVG(
EXTRACT(EPOCH FROM (a.discharged_at - a.admitted_at)) / 86400
), 1) AS avg_los_days,
ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM (a.discharged_at - a.admitted_at)) / 86400
)::NUMERIC, 1) AS median_los_days,
ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM (a.discharged_at - a.admitted_at)) / 86400
)::NUMERIC, 1) AS p90_los_days,
COUNT(*) FILTER (WHERE a.discharged_at IS NULL) AS currently_admitted
FROM hc_admissions a
JOIN hc_wards w ON w.id = a.ward_id
GROUP BY w.name
ORDER BY avg_los_days DESC;What This Returns
ward | total_admissions | avg_los_days | median_los_days | p90_los_days | currently_admitted
-----------+------------------+--------------+-----------------+--------------+-------------------
ICU | 116 | 7.8 | 7.5 | 13.4 | 12
Surgery | 116 | 7.6 | 7.5 | 13.0 | 12
General-A | 116 | 7.4 | 7.0 | 13.0 | 12
...3. Detecting 30-Day Readmissions
A readmission within 30 days of discharge is a quality-of-care indicator and often penalised by payers. Detect these with a self-join:
SELECT
a1.id AS original_admission_id,
a1.patient_id,
a1.discharged_at::DATE AS discharge_date,
a2.id AS readmission_id,
a2.admitted_at::DATE AS readmit_date,
(a2.admitted_at - a1.discharged_at) AS gap,
a2.admission_type
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
ORDER BY a1.patient_id, a1.discharged_at;Summarise the 30-day readmission rate by ward:
WITH readmits AS (
SELECT DISTINCT a1.id AS original_id, a1.ward_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
FROM hc_admissions a
JOIN hc_wards w ON w.id = a.ward_id
LEFT JOIN readmits r ON r.original_id = a.id
WHERE a.discharged_at IS NOT NULL
GROUP BY w.name
ORDER BY readmit_rate_pct DESC;What This Returns
ward | discharges | readmissions | readmit_rate_pct
-----------+------------+--------------+------------------
ICU | 104 | 42 | 40.4
Pediatric | 101 | 41 | 40.6
...4. Frequent Admitters — Patients with 3+ Admissions in 12 Months
High-frequency admitters often signal care gaps or complex chronic conditions:
SELECT
p.id,
p.name,
p.mrn,
p.insurance_type,
COUNT(a.id) AS admissions_12mo,
MIN(a.admitted_at)::DATE AS first_admission,
MAX(a.admitted_at)::DATE AS last_admission,
STRING_AGG(DISTINCT d.description, ', ' ORDER BY d.description)
AS primary_diagnoses
FROM hc_patients p
JOIN hc_admissions a
ON a.patient_id = p.id
AND a.admitted_at >= CURRENT_DATE - INTERVAL '12 months'
LEFT JOIN hc_diagnoses d
ON d.admission_id = a.id
AND d.is_primary = true
GROUP BY p.id, p.name, p.mrn, p.insurance_type
HAVING COUNT(a.id) >= 3
ORDER BY admissions_12mo DESC;What This Returns
id | name | mrn | insurance_type | admissions_12mo
----+------------+-----------+----------------+----------------
12 | Patient 12 | MRN00012 | Medicare | 6
24 | Patient 24 | MRN00024 | Medicaid | 5
...Key Takeaway
Use DATE_TRUNC + COUNT for volume trends, PERCENTILE_CONT for robust LOS statistics, and a self-join on patient_id with a date-range condition to detect readmissions. The self-join pattern is the standard technique for any "event within N days of another event" problem and appears in churn, fraud, and retention analysis as well.