text: -- ------------------------------------------------------------------
  -- Title: Quick Sequential Organ Failure Assessment (qSOFA)
  -- This query extracts the quick sequential organ failure assessment.
  -- This score was a recent revision of SOFA, aiming to detect patients at risk of sepsis.
  -- The score is calculated on the first day of each ICU patients' stay - though needn't be.
  -- ------------------------------------------------------------------
  
  -- Reference for qSOFA:
  --    Singer M, et al. The Third International Consensus Definitions for Sepsis and Septic Shock (Sepsis-3)
  --    Seymour CW, et al. Assessment of Clinical Criteria for Sepsis: For the Third International Consensus Definitions for Sepsis and Septic Shock (Sepsis-3)
  
  -- Variables used in qSOFA:
  --  GCS, respiratory rate, systolic blood pressure
  
  -- The following views required to run this query:
  --  1) gcsfirstday - generated by gcs-first-day.sql
  --  2) vitalsfirstday - generated by vitals-first-day.sql
  
  -- Note:
  --  The score is calculated for *all* ICU patients, with the assumption that the user will subselect appropriate ICUSTAY_IDs.
  --  For example, the score is calculated for neonates, but it is likely inappropriate to actually use the score values for these patients.
  
  with scorecomp as
  (
  select ie.icustay_id
    , v.sysbp_min
    , v.resprate_max
    , gcs.mingcs
  FROM `physionet-data.mimiciii_clinical.icustays` ie
  left join `physionet-data.mimiciii_clinical.vitals_first_day` v
    on ie.icustay_id = v.icustay_id
  left join `physionet-data.mimiciii_clinical.gcs_first_day` gcs
    on ie.icustay_id = gcs.icustay_id
  )
  , scorecalc as
  (
    -- Calculate the final score
    -- note that if the underlying data is missing, the component is null
    -- eventually these are treated as 0 (normal), but knowing when data is missing is useful for debugging
    select icustay_id
    , case
        when sysbp_min is null then null
        when sysbp_min   <= 100 then 1
        else 0 end
      as sysbp_score
    , case
        when mingcs is null then null
        when mingcs   <= 13 then 1
        else 0 end
      as gcs_score
    , case
        when resprate_max is null then null
        when resprate_max   >= 22 then 1
        else 0 end
      as resprate_score
    from scorecomp
  )
  select ie.subject_id, ie.hadm_id, ie.icustay_id
  , coalesce(sysbp_score,0)
   + coalesce(gcs_score,0)
   + coalesce(resprate_score,0)
   as qsofa
  , sysbp_score
  , gcs_score
  , resprate_score
  FROM `physionet-data.mimiciii_clinical.icustays` ie
  left join scorecalc s
    on ie.icustay_id = s.icustay_id
  order by ie.icustay_id;
  