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

```sql
-- ------------------------------------------------------------------
-- Title: Charlson Comorbidity Index
-- Calculates the Charlson Comorbidity Index (CCI) for each hospital
-- admission using ICD-9 and ICD-10 diagnosis codes. Includes 17
-- comorbidity conditions with original Charlson weights and age score.
-- ------------------------------------------------------------------

-- Reference:
--    Charlson ME et al. "A new method of classifying prognostic
--    comorbidity in longitudinal studies." J Chronic Dis. 1987;40(5):373-83.

-- ICD mapping reference:
--    Quan H et al. "Coding algorithms for defining comorbidities in
--    ICD-9-CM and ICD-10 administrative data." Med Care. 2005;43(11):1130-9.

-- Adapted from mimic-code charlson.sql

WITH diag AS (
  SELECT
    hadm_id,
    CASE WHEN icd_version = 9 THEN icd_code ELSE NULL END AS icd9_code,
    CASE WHEN icd_version = 10 THEN icd_code ELSE NULL END AS icd10_code
  FROM mimiciv_hosp.diagnoses_icd
), com AS (
  SELECT
    ad.hadm_id,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) IN ('410', '412')
        OR SUBSTR(icd10_code, 1, 3) IN ('I21', 'I22')
        OR SUBSTR(icd10_code, 1, 4) = 'I252'
        THEN 1
        ELSE 0
      END
    ) AS myocardial_infarct,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) = '428'
        OR SUBSTR(icd9_code, 1, 5) IN ('39891', '40201', '40211', '40291', '40401', '40403', '40411', '40413', '40491', '40493')
        OR SUBSTR(icd9_code, 1, 4) BETWEEN '4254' AND '4259'
        OR SUBSTR(icd10_code, 1, 3) IN ('I43', 'I50')
        OR SUBSTR(icd10_code, 1, 4) IN ('I099', 'I110', 'I130', 'I132', 'I255', 'I420', 'I425', 'I426', 'I427', 'I428', 'I429', 'P290')
        THEN 1
        ELSE 0
      END
    ) AS congestive_heart_failure,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) IN ('440', '441')
        OR SUBSTR(icd9_code, 1, 4) IN ('0930', '4373', '4471', '5571', '5579', 'V434')
        OR SUBSTR(icd9_code, 1, 4) BETWEEN '4431' AND '4439'
        OR SUBSTR(icd10_code, 1, 3) IN ('I70', 'I71')
        OR SUBSTR(icd10_code, 1, 4) IN ('I731', 'I738', 'I739', 'I771', 'I790', 'I792', 'K551', 'K558', 'K559', 'Z958', 'Z959')
        THEN 1
        ELSE 0
      END
    ) AS peripheral_vascular_disease,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) BETWEEN '430' AND '438'
        OR SUBSTR(icd9_code, 1, 5) = '36234'
        OR SUBSTR(icd10_code, 1, 3) IN ('G45', 'G46')
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'I60' AND 'I69'
        OR SUBSTR(icd10_code, 1, 4) = 'H340'
        THEN 1
        ELSE 0
      END
    ) AS cerebrovascular_disease,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) = '290'
        OR SUBSTR(icd9_code, 1, 4) IN ('2941', '3312')
        OR SUBSTR(icd10_code, 1, 3) IN ('F00', 'F01', 'F02', 'F03', 'G30')
        OR SUBSTR(icd10_code, 1, 4) IN ('F051', 'G311')
        THEN 1
        ELSE 0
      END
    ) AS dementia,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) BETWEEN '490' AND '505'
        OR SUBSTR(icd9_code, 1, 4) IN ('4168', '4169', '5064', '5081', '5088')
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'J40' AND 'J47'
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'J60' AND 'J67'
        OR SUBSTR(icd10_code, 1, 4) IN ('I278', 'I279', 'J684', 'J701', 'J703')
        THEN 1
        ELSE 0
      END
    ) AS chronic_pulmonary_disease,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) = '725'
        OR SUBSTR(icd9_code, 1, 4) IN ('4465', '7100', '7101', '7102', '7103', '7104', '7140', '7141', '7142', '7148')
        OR SUBSTR(icd10_code, 1, 3) IN ('M05', 'M06', 'M32', 'M33', 'M34')
        OR SUBSTR(icd10_code, 1, 4) IN ('M315', 'M351', 'M353', 'M360')
        THEN 1
        ELSE 0
      END
    ) AS rheumatic_disease,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) IN ('531', '532', '533', '534')
        OR SUBSTR(icd10_code, 1, 3) IN ('K25', 'K26', 'K27', 'K28')
        THEN 1
        ELSE 0
      END
    ) AS peptic_ulcer_disease,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) IN ('570', '571')
        OR SUBSTR(icd9_code, 1, 4) IN ('0706', '0709', '5733', '5734', '5738', '5739', 'V427')
        OR SUBSTR(icd9_code, 1, 5) IN ('07022', '07023', '07032', '07033', '07044', '07054')
        OR SUBSTR(icd10_code, 1, 3) IN ('B18', 'K73', 'K74')
        OR SUBSTR(icd10_code, 1, 4) IN ('K700', 'K701', 'K702', 'K703', 'K709', 'K713', 'K714', 'K715', 'K717', 'K760', 'K762', 'K763', 'K764', 'K768', 'K769', 'Z944')
        THEN 1
        ELSE 0
      END
    ) AS mild_liver_disease,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 4) IN ('2500', '2501', '2502', '2503', '2508', '2509')
        OR SUBSTR(icd10_code, 1, 4) IN ('E100', 'E101', 'E106', 'E108', 'E109', 'E110', 'E111', 'E116', 'E118', 'E119', 'E120', 'E121', 'E126', 'E128', 'E129', 'E130', 'E131', 'E136', 'E138', 'E139', 'E140', 'E141', 'E146', 'E148', 'E149')
        THEN 1
        ELSE 0
      END
    ) AS diabetes_without_cc,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 4) IN ('2504', '2505', '2506', '2507')
        OR SUBSTR(icd10_code, 1, 4) IN ('E102', 'E103', 'E104', 'E105', 'E107', 'E112', 'E113', 'E114', 'E115', 'E117', 'E122', 'E123', 'E124', 'E125', 'E127', 'E132', 'E133', 'E134', 'E135', 'E137', 'E142', 'E143', 'E144', 'E145', 'E147')
        THEN 1
        ELSE 0
      END
    ) AS diabetes_with_cc,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) IN ('342', '343')
        OR SUBSTR(icd9_code, 1, 4) IN ('3341', '3440', '3441', '3442', '3443', '3444', '3445', '3446', '3449')
        OR SUBSTR(icd10_code, 1, 3) IN ('G81', 'G82')
        OR SUBSTR(icd10_code, 1, 4) IN ('G041', 'G114', 'G801', 'G802', 'G830', 'G831', 'G832', 'G833', 'G834', 'G839')
        THEN 1
        ELSE 0
      END
    ) AS paraplegia,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) IN ('582', '585', '586', 'V56')
        OR SUBSTR(icd9_code, 1, 4) IN ('5880', 'V420', 'V451')
        OR SUBSTR(icd9_code, 1, 4) BETWEEN '5830' AND '5837'
        OR SUBSTR(icd9_code, 1, 5) IN ('40301', '40311', '40391', '40402', '40403', '40412', '40413', '40492', '40493')
        OR SUBSTR(icd10_code, 1, 3) IN ('N18', 'N19')
        OR SUBSTR(icd10_code, 1, 4) IN ('I120', 'I131', 'N032', 'N033', 'N034', 'N035', 'N036', 'N037', 'N052', 'N053', 'N054', 'N055', 'N056', 'N057', 'N250', 'Z490', 'Z491', 'Z492', 'Z940', 'Z992')
        THEN 1
        ELSE 0
      END
    ) AS renal_disease,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) BETWEEN '140' AND '172'
        OR SUBSTR(icd9_code, 1, 4) BETWEEN '1740' AND '1958'
        OR SUBSTR(icd9_code, 1, 3) BETWEEN '200' AND '208'
        OR SUBSTR(icd9_code, 1, 4) = '2386'
        OR SUBSTR(icd10_code, 1, 3) IN ('C43', 'C88')
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C00' AND 'C26'
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C30' AND 'C34'
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C37' AND 'C41'
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C45' AND 'C58'
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C60' AND 'C76'
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C81' AND 'C85'
        OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C90' AND 'C97'
        THEN 1
        ELSE 0
      END
    ) AS malignant_cancer,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 4) IN ('4560', '4561', '4562')
        OR SUBSTR(icd9_code, 1, 4) BETWEEN '5722' AND '5728'
        OR SUBSTR(icd10_code, 1, 4) IN ('I850', 'I859', 'I864', 'I982', 'K704', 'K711', 'K721', 'K729', 'K765', 'K766', 'K767')
        THEN 1
        ELSE 0
      END
    ) AS severe_liver_disease,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) IN ('196', '197', '198', '199')
        OR SUBSTR(icd10_code, 1, 3) IN ('C77', 'C78', 'C79', 'C80')
        THEN 1
        ELSE 0
      END
    ) AS metastatic_solid_tumor,
    MAX(
      CASE
        WHEN SUBSTR(icd9_code, 1, 3) IN ('042', '043', '044')
        OR SUBSTR(icd10_code, 1, 3) IN ('B20', 'B21', 'B22', 'B24')
        THEN 1
        ELSE 0
      END
    ) AS aids
  FROM mimiciv_hosp.admissions AS ad
  LEFT JOIN diag
    ON ad.hadm_id = diag.hadm_id
  GROUP BY
    ad.hadm_id
), ag AS (
  SELECT
    hadm_id,
    age,
    CASE
      WHEN age <= 50
      THEN 0
      WHEN age <= 60
      THEN 1
      WHEN age <= 70
      THEN 2
      WHEN age <= 80
      THEN 3
      ELSE 4
    END AS age_score
  FROM mimiciv_derived.age
)
SELECT
  ad.subject_id,
  ad.hadm_id,
  ag.age_score,
  myocardial_infarct,
  congestive_heart_failure,
  peripheral_vascular_disease,
  cerebrovascular_disease,
  dementia,
  chronic_pulmonary_disease,
  rheumatic_disease,
  peptic_ulcer_disease,
  mild_liver_disease,
  diabetes_without_cc,
  diabetes_with_cc,
  paraplegia,
  renal_disease,
  malignant_cancer,
  severe_liver_disease,
  metastatic_solid_tumor,
  aids,
  age_score + myocardial_infarct + congestive_heart_failure + peripheral_vascular_disease + cerebrovascular_disease + dementia + chronic_pulmonary_disease + rheumatic_disease + peptic_ulcer_disease + GREATEST(mild_liver_disease, 3 * severe_liver_disease) + GREATEST(2 * diabetes_with_cc, diabetes_without_cc) + GREATEST(2 * malignant_cancer, 6 * metastatic_solid_tumor) + 2 * paraplegia + 2 * renal_disease + 6 * aids AS charlson_comorbidity_index
FROM mimiciv_hosp.admissions AS ad
LEFT JOIN com
  ON ad.hadm_id = com.hadm_id
LEFT JOIN ag
  ON com.hadm_id = ag.hadm_id
```
