-- ------------------------------------------------------------------
-- Title: Suspicion of Infection
-- Identifies suspected infection events by pairing systemic antibiotic
-- administration with culture collection within asymmetric time windows:
-- culture within 72h before OR 24h after antibiotic start.
-- ------------------------------------------------------------------
-- Reference:
-- Seymour CW et al. "Assessment of Clinical Criteria for Sepsis."
-- JAMA. 2016;315(8):762-774.
-- Adapted from mimic-code suspicion_of_infection.sql
-- Optimized for DuckDB: split OR joins into UNION ALL for IEJoin.
-- DEVIATION from mimic-code: ROW_NUMBER() orderings include stable tie-breakers
-- so exact timestamp/name ties select deterministically across execution plans.
WITH ab_tbl AS (
SELECT
abx.subject_id,
abx.hadm_id,
abx.stay_id,
abx.antibiotic,
abx.starttime AS antibiotic_time,
DATE_TRUNC('DAY', abx.starttime) AS antibiotic_date,
abx.stoptime AS antibiotic_stoptime,
ROW_NUMBER() OVER (
PARTITION BY subject_id
ORDER BY
starttime NULLS FIRST,
stoptime NULLS FIRST,
antibiotic NULLS FIRST,
hadm_id NULLS FIRST,
stay_id NULLS FIRST
) AS ab_id
FROM mimiciv_derived.antibiotic AS abx
), me AS (
SELECT
micro_specimen_id,
MAX(subject_id) AS subject_id,
MAX(hadm_id) AS hadm_id,
TRY_CAST(MAX(chartdate) AS DATE) AS chartdate,
MAX(charttime) AS charttime,
MAX(spec_type_desc) AS spec_type_desc,
MAX(
CASE
WHEN NOT org_name IS NULL AND org_itemid <> 90856 AND org_name <> ''
THEN 1
ELSE 0
END
) AS positiveculture
FROM mimiciv_hosp.microbiologyevents
GROUP BY
micro_specimen_id
),
-- Split me into two subsets so each join path uses only simple inequalities
-- (no OR), enabling DuckDB's IEJoin range-join optimization.
me_with_time AS (
SELECT * FROM me WHERE charttime IS NOT NULL
),
me_date_only AS (
SELECT * FROM me WHERE charttime IS NULL
),
me_then_ab AS (
SELECT
subject_id, hadm_id, stay_id, ab_id, micro_specimen_id,
last72_charttime, last72_positiveculture, last72_specimen,
ROW_NUMBER() OVER (
PARTITION BY subject_id, ab_id
ORDER BY chartdate NULLS FIRST, charttime NULLS FIRST, micro_specimen_id NULLS FIRST
) AS micro_seq
FROM (
-- Cultures with charttime: antibiotic within 72h after culture
SELECT
ab_tbl.subject_id, ab_tbl.hadm_id, ab_tbl.stay_id, ab_tbl.ab_id,
me72.micro_specimen_id,
me72.charttime AS last72_charttime,
me72.positiveculture AS last72_positiveculture,
me72.spec_type_desc AS last72_specimen,
me72.chartdate,
me72.charttime
FROM ab_tbl
INNER JOIN me_with_time AS me72
ON ab_tbl.subject_id = me72.subject_id
AND ab_tbl.antibiotic_time > me72.charttime
AND ab_tbl.antibiotic_time <= me72.charttime + INTERVAL '72' HOUR
UNION ALL
-- Cultures with only chartdate: antibiotic within 3 days after culture
SELECT
ab_tbl.subject_id, ab_tbl.hadm_id, ab_tbl.stay_id, ab_tbl.ab_id,
me72.micro_specimen_id,
CAST(me72.chartdate AS TIMESTAMP) AS last72_charttime,
me72.positiveculture AS last72_positiveculture,
me72.spec_type_desc AS last72_specimen,
me72.chartdate,
me72.charttime
FROM ab_tbl
INNER JOIN me_date_only AS me72
ON ab_tbl.subject_id = me72.subject_id
AND ab_tbl.antibiotic_date >= me72.chartdate
AND ab_tbl.antibiotic_date <= me72.chartdate + INTERVAL 3 DAY
)
),
ab_then_me AS (
SELECT
subject_id, hadm_id, stay_id, ab_id, micro_specimen_id,
next24_charttime, next24_positiveculture, next24_specimen,
ROW_NUMBER() OVER (
PARTITION BY subject_id, ab_id
ORDER BY chartdate NULLS FIRST, charttime NULLS FIRST, micro_specimen_id NULLS FIRST
) AS micro_seq
FROM (
-- Cultures with charttime: antibiotic within 24h before culture
SELECT
ab_tbl.subject_id, ab_tbl.hadm_id, ab_tbl.stay_id, ab_tbl.ab_id,
me24.micro_specimen_id,
me24.charttime AS next24_charttime,
me24.positiveculture AS next24_positiveculture,
me24.spec_type_desc AS next24_specimen,
me24.chartdate,
me24.charttime
FROM ab_tbl
INNER JOIN me_with_time AS me24
ON ab_tbl.subject_id = me24.subject_id
AND ab_tbl.antibiotic_time >= me24.charttime - INTERVAL '24' HOUR
AND ab_tbl.antibiotic_time < me24.charttime
UNION ALL
-- Cultures with only chartdate: antibiotic within 1 day before culture
SELECT
ab_tbl.subject_id, ab_tbl.hadm_id, ab_tbl.stay_id, ab_tbl.ab_id,
me24.micro_specimen_id,
CAST(me24.chartdate AS TIMESTAMP) AS next24_charttime,
me24.positiveculture AS next24_positiveculture,
me24.spec_type_desc AS next24_specimen,
me24.chartdate,
me24.charttime
FROM ab_tbl
INNER JOIN me_date_only AS me24
ON ab_tbl.subject_id = me24.subject_id
AND ab_tbl.antibiotic_date >= me24.chartdate - INTERVAL 1 DAY
AND ab_tbl.antibiotic_date <= me24.chartdate
)
)
SELECT
ab_tbl.subject_id,
ab_tbl.stay_id,
ab_tbl.hadm_id,
ab_tbl.ab_id,
ab_tbl.antibiotic,
ab_tbl.antibiotic_time,
CASE WHEN last72_specimen IS NULL AND next24_specimen IS NULL THEN 0 ELSE 1 END AS suspected_infection,
CASE
WHEN last72_specimen IS NULL AND next24_specimen IS NULL
THEN NULL
ELSE COALESCE(last72_charttime, antibiotic_time)
END AS suspected_infection_time,
COALESCE(last72_charttime, next24_charttime) AS culture_time,
COALESCE(last72_specimen, next24_specimen) AS specimen,
COALESCE(last72_positiveculture, next24_positiveculture) AS positive_culture
FROM ab_tbl
LEFT JOIN ab_then_me AS ab2me
ON ab_tbl.subject_id = ab2me.subject_id
AND ab_tbl.ab_id = ab2me.ab_id
AND ab2me.micro_seq = 1
LEFT JOIN me_then_ab AS me2ab
ON ab_tbl.subject_id = me2ab.subject_id
AND ab_tbl.ab_id = me2ab.ab_id
AND me2ab.micro_seq = 1