mimic-gcs-24h-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: Glasgow Coma Scale (GCS) — first day minimum
-- This query extracts the minimum GCS score for the first 24 hours
-- of each ICU stay, along with the component values (motor, verbal,
-- eyes) at the time of the minimum total GCS.
-- ------------------------------------------------------------------

-- Reference for GCS:
--    Teasdale G, Jennett B. "Assessment of coma and impaired
--    consciousness. A practical scale." Lancet. 1974;2(7872):81-84.

-- Adapted from mimic-code first_day_gcs.sql

WITH gcs_final AS (
    SELECT
        ie.subject_id,
        ie.stay_id,
        g.gcs,
        g.gcs_motor,
        g.gcs_verbal,
        g.gcs_eyes,
        g.gcs_unable,
        -- Deterministic tie-breaker: minimum total GCS alone can select
        -- multiple rows with different component values. Chart time plus
        -- components keeps the chosen tuple stable across execution plans
        -- while preserving minimum total GCS as the primary clinical rule.
        ROW_NUMBER() OVER (
            PARTITION BY g.stay_id
            ORDER BY
                g.gcs NULLS FIRST,
                g.charttime NULLS LAST,
                g.gcs_motor NULLS LAST,
                g.gcs_verbal NULLS LAST,
                g.gcs_eyes NULLS LAST,
                g.gcs_unable NULLS LAST
        ) AS gcs_seq
    FROM mimiciv_icu.icustays AS ie
    LEFT JOIN mimiciv_derived.gcs AS g
        ON ie.stay_id = g.stay_id
        AND g.charttime >= ie.intime - INTERVAL '6' HOUR
        AND g.charttime <= ie.intime + INTERVAL '1' DAY
)

SELECT
    ie.subject_id, ie.hadm_id, ie.stay_id
    -- DEVIATION from mimic-code: first_day_gcs leaves missing values NULL
    -- and returns gcs_unable. The benchmark evaluates only total/motor/verbal/
    -- eyes and follows the task instruction to treat missing GCS as normal.
    , COALESCE(gs.gcs, 15) AS gcs_min
    , COALESCE(gs.gcs_motor, 6) AS gcs_motor
    , COALESCE(gs.gcs_verbal, 5) AS gcs_verbal
    , COALESCE(gs.gcs_eyes, 4) AS gcs_eyes
FROM mimiciv_icu.icustays AS ie
LEFT JOIN gcs_final AS gs
    ON ie.stay_id = gs.stay_id AND gs.gcs_seq = 1
;