mimic-suspicion-infection-raw

Reference SQL (matched-content control)

The following block is the public reference SQL used to construct the ground truth for this task. It is provided verbatim, without procedural prose, to test whether matched task-relevant content alone explains the WITH-SKILL gain.

-- ------------------------------------------------------------------
-- 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