Emergency Department Patient Flow Optimization & Wait Time Prediction System

WITH ranked AS (
  SELECT
    encounter_id,
    patient_id,
    arrival_ts,
    ingest_ts,
    ROW_NUMBER() OVER (
      PARTITION BY encounter_id
      ORDER BY ingest_ts DESC NULLS LAST, arrival_ts DESC
    ) AS rn
  FROM raw_ed.encounters
)
SELECT encounter_id, patient_id, arrival_ts
FROM ranked
WHERE rn = 1;

SELECT
  e.encounter_id,
  BOOL_OR(f.event_ts < e.triage_ts) AS has_pre_triage_event,
  BOOL_OR(
    f.event_type = 'PROVIDER_FIRST_CONTACT'
    AND f.event_ts < e.triage_ts
  ) AS impossible_provider_before_triage
FROM curated.encounters e
JOIN curated.flow_events f ON f.encounter_id = e.encounter_id
GROUP BY e.encounter_id
HAVING BOOL_OR(f.event_ts < e.triage_ts);

WITH w AS (
  SELECT
    encounter_id,
    TIMESTAMPDIFF(MINUTE, triage_ts, first_provider_ts) AS wait_min
  FROM curated.encounters
  WHERE first_provider_ts IS NOT NULL
),
stats AS (
  SELECT
    wait_min,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY wait_min) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY wait_min) AS q3
  FROM w
)
SELECT
  w.encounter_id,
  w.wait_min,
  CASE
    WHEN w.wait_min > 720 THEN 1
    WHEN w.wait_min > (stats.q3 + 1.5 * (stats.q3 - stats.q1)) THEN 1
    ELSE 0
  END AS wait_outlier_flag
FROM w CROSS JOIN stats;

import pandas as pd

enc = pd.read_csv("encounters.csv", parse_dates=["arrival_ts", "triage_ts", "first_provider_ts"])
flow = pd.read_csv("flow_events.csv", parse_dates=["event_ts"])
pat = pd.read_csv("patients.csv")

# Invalid ages removed for analytics cohort
pat = pat[(pat["age"] >= 0) & (pat["age"] <= 115)]

esi_map = {"esi-2": "2", "2": "2", "II": "2", "esi 3": "3", "THREE": "3"}
enc["esi_level"] = enc["esi_raw"].str.strip().str.lower().map(esi_map).fillna("UNKNOWN")

# Missing triage: borrow first in-ED event after arrival (operational proxy)
flow = flow.sort_values(["encounter_id", "event_ts"])
first_ed = flow.loc[flow["event_type"].eq("IN_ED")].groupby("encounter_id")["event_ts"].min()
enc["triage_ts"] = enc["triage_ts"].fillna(enc["encounter_id"].map(first_ed))

enc = enc.drop_duplicates(subset=["encounter_id"], keep="last")

g = flow.groupby("encounter_id")["event_ts"]
viol = g.apply(lambda s: s.is_monotonic_increasing)
bad = viol[~viol].index

flow["wait_min_from_triage"] = (
    flow.merge(enc[["encounter_id", "triage_ts"]], on="encounter_id", how="left")
    .assign(delta=lambda d: (d["event_ts"] - d["triage_ts"]).dt.total_seconds() / 60.0)
)["delta"]

# Winsorize for modeling while keeping raw for audit
cap = flow["wait_min_from_triage"].quantile(0.995)
flow["wait_min_winsor"] = flow["wait_min_from_triage"].clip(upper=cap)