SQL for Healthcare Analytics: Patient Wait Times, Readmission Rates & HIPAA-Compliant Aggregations

Healthcare Analytics Without Exposing PHI

Analyzing patient data while protecting privacy is a critical skill. These SQL patterns aggregate data safely and compute key metrics without exposing identifiers.

1. Average Patient Wait Time by Hour

Compute the average wait time across all patients per hour of the day — useful for staffing optimization.

SELECT
    EXTRACT(HOUR FROM check_in_time) AS hour_of_day,
    AVG(EXTRACT(EPOCH FROM (check_out_time - check_in_time)) / 60) AS avg_wait_minutes
FROM patient_visits
GROUP BY hour_of_day
ORDER BY hour_of_day;

2. 30-Day Readmission Rate per Condition

Find the percentage of patients readmitted within 30 days for each diagnosis, excluding the initial visit.

WITH initial_visits AS (
    SELECT
        patient_id,
        diagnosis_code,
        admission_date AS first_admission
    FROM patient_admissions
    WHERE admission_rank = 1
),
readmissions AS (
    SELECT
        r.patient_id,
        r.diagnosis_code,
        r.admission_date AS readmit_date
    FROM patient_admissions r
    INNER JOIN initial_visits i
        ON r.patient_id = i.patient_id
       AND r.admission_date BETWEEN i.first_admission + INTERVAL '1 day'
                                    AND i.first_admission + INTERVAL '30 days'
)
SELECT
    diagnosis_code,
    COUNT(DISTINCT patient_id) AS readmitted_count,
    COUNT(DISTINCT i.patient_id) AS total_patients,
    ROUND(COUNT(DISTINCT r.patient_id)::numeric / COUNT(DISTINCT i.patient_id) * 100, 2) AS readmission_rate_pct
FROM readmissions r
JOIN initial_visits i ON r.patient_id = i.patient_id
GROUP BY diagnosis_code
ORDER BY readmission_rate_pct DESC;

3. Daily Resource Utilization Efficiency

Compare occupied beds vs. total capacity by ward to identify underutilized or over capacity units.

SELECT
    ward_name,
    DATE(admission_date) AS date,
    SUM(CASE WHEN status = 'occupied' THEN 1 ELSE 0 END) AS occupied_beds,
    COUNT(*) AS total_beds,
    ROUND(100.0 * SUM(CASE WHEN status = 'occupied' THEN 1 ELSE 0 END) / COUNT(*), 2) AS occupancy_pct
FROM bed_assignments
GROUP BY ward_name, date
ORDER BY occupancy_pct DESC;

Key Takeaways for Production

• Never expose patient_id or direct identifiers in analytical queries — always aggregate and anonymize.
• CTEs make complex multi-step calculations readable and maintainable — break readmission logic into initial_visits and readmissions.
• EXTRACT(EPOCH FROM ...) converts intervals to seconds for precise time calculations.
• Use materialized views for read-heavy metrics like readmission rates so OLTP tables aren't locked down by analytical queries.