PheKnowVec Cohort Query: https://github.com/callahantiff/PheKnowVec/wiki/Sickle-Cell-Disease

SICKLECELLDISEASE_COHORT.sql

WITH dx_case_inclusion_criteria_1 AS (
  SELECT co.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.condition_occurrence co,
    {database}.SICKLECELLDISEASE_COHORT_VARS cohort
  WHERE 
    co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.standard_code_set = {code_set_group}
    GROUP BY co.person_id, cohort.standard_code_set
  HAVING
    COUNT(DISTINCT co.condition_concept_id) >= 1
),

visit_inclusion_criteria_1 AS (
  SELECT person_id FROM 
  
    -- identify patients with at least 2 outpatient visits at least 30 days apart
    (SELECT v1.person_id
    FROM 
      {database}.visit_occurrence v1,
      {database}.visit_occurrence v2
    WHERE 
      v1.person_id = v2.person_id
      AND v1.visit_concept_id in (9202)
      AND v2.visit_concept_id in (9202)
      AND v1.visit_start_datetime < v2.visit_start_datetime
      AND DATETIME_DIFF(DATETIME(v2.visit_start_datetime), DATETIME(v1.visit_start_datetime), DAY) >= 30
      GROUP BY v1.person_id, v1.visit_start_datetime, v2.visit_start_datetime
    ORDER BY
      v1.visit_start_datetime, v2.visit_start_datetime)
  
  UNION ALL
  
    -- identify patients with at least 1 hospitalization
    (SELECT person_id FROM 
        {database}.visit_occurrence
      WHERE 
        visit_concept_id in (9201, 9203)
      GROUP BY
        person_id, visit_occurrence_id
      HAVING
        COUNT(DISTINCT visit_occurrence_id) >= 1)
),

dx_case_exclusion_criteria_1 AS (
  SELECT v.person_id, cohort.standard_code_set AS code_set
  FROM 
    {database}.visit_occurrence v,
    {database}.care_site c, 
    {database}.condition_occurrence co, 
    {database}.SICKLECELLDISEASE_COHORT_VARS cohort 
  JOIN
    (SELECT DISTINCT v.person_id, COUNT(DISTINCT co.condition_concept_id) AS trait_count, cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.care_site c, 
      {database}.condition_occurrence co, 
      {database}.SICKLECELLDISEASE_COHORT_VARS cohort 
     WHERE co.visit_occurrence_id = v.visit_occurrence_id 
      AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
      AND cohort.phenotype_definition_number = 2 
      AND cohort.standard_code_set = {code_set_group}
     GROUP BY 
      v.person_id, code_set) b
    ON v.person_id = b.person_id
   WHERE co.visit_occurrence_id = v.visit_occurrence_id 
    AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1 
  GROUP BY 
    v.person_id, code_set, b.trait_count
  HAVING
    COUNT(DISTINCT co.condition_concept_id) > b.trait_count
)
  
SELECT person_id, code_set, 'SICKLECELLDISEASE_CASE' AS cohort_type FROM (
  SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
   WHERE person_id IN (SELECT person_id FROM visit_inclusion_criteria_1)
  EXCEPT DISTINCT
  SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
;

SICKLECELLDISEASE_COHORT_CODES.SQL

-- CODES + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY

WITH dx_case_inclusion_criteria_1 AS (
  SELECT co.person_id, cohort.standard_code_set AS code_set
  FROM 
    {database}.condition_occurrence co, 
    {database}.SICKLECELLDISEASE_COHORT_VARS cohort 
  WHERE co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1
    AND cohort.standard_code_set = {code_set_group}
  GROUP BY 
    co.person_id, code_set
  HAVING 
    COUNT(DISTINCT co.condition_concept_id) >= 1
)
 
SELECT * FROM

# all clinical domains + codes
(SELECT person_id, code_set, 'SICKLECELLDISEASE_CASE' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
FROM (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1))
 
UNION ALL

# only conditions + codes
(SELECT person_id, code_set, 'SICKLECELLDISEASE_CASE' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment 
FROM (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1))
;

SICKLECELLDISEASE_COHORT_DEFINITIONS.SQL

-- DEFINITIONS + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY
WITH dx_case_inclusion_criteria_1 AS (
  SELECT co.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.condition_occurrence co,
    {database}.SICKLECELLDISEASE_COHORT_VARS cohort
  WHERE 
    co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.standard_code_set = {code_set_group}
    GROUP BY co.person_id, cohort.standard_code_set
  HAVING
    COUNT(DISTINCT co.condition_concept_id) >= 1
),

visit_inclusion_criteria_1 AS (
  SELECT person_id FROM 
  
    -- identify patients with at least 2 outpatient visits at least 30 days apart
    (SELECT v1.person_id
    FROM 
      {database}.visit_occurrence v1,
      {database}.visit_occurrence v2
    WHERE 
      v1.person_id = v2.person_id
      AND v1.visit_concept_id in (9202)
      AND v2.visit_concept_id in (9202)
      AND v1.visit_start_datetime < v2.visit_start_datetime
      AND DATETIME_DIFF(DATETIME(v2.visit_start_datetime), DATETIME(v1.visit_start_datetime), DAY) >= 30
      GROUP BY v1.person_id, v1.visit_start_datetime, v2.visit_start_datetime
    ORDER BY
      v1.visit_start_datetime, v2.visit_start_datetime)
  
  UNION ALL
  
    -- identify patients with at least 1 hospitalization
    (SELECT person_id FROM 
        {database}.visit_occurrence
      WHERE 
        visit_concept_id in (9201, 9203)
      GROUP BY
        person_id, visit_occurrence_id
      HAVING
        COUNT(DISTINCT visit_occurrence_id) >= 1)
),

dx_case_exclusion_criteria_1 AS (
  SELECT v.person_id, cohort.standard_code_set AS code_set
  FROM 
    {database}.visit_occurrence v,
    {database}.care_site c, 
    {database}.condition_occurrence co, 
    {database}.SICKLECELLDISEASE_COHORT_VARS cohort 
  JOIN
    (SELECT DISTINCT v.person_id, COUNT(DISTINCT co.condition_concept_id) AS trait_count, cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.care_site c, 
      {database}.condition_occurrence co, 
      {database}.SICKLECELLDISEASE_COHORT_VARS cohort 
     WHERE co.visit_occurrence_id = v.visit_occurrence_id 
      AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
      AND cohort.phenotype_definition_number = 2 
      AND cohort.standard_code_set = {code_set_group}
     GROUP BY 
      v.person_id, code_set) b
    ON v.person_id = b.person_id
   WHERE co.visit_occurrence_id = v.visit_occurrence_id 
    AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1 
  GROUP BY 
    v.person_id, code_set, b.trait_count
  HAVING
    COUNT(DISTINCT co.condition_concept_id) > b.trait_count
)
 
SELECT * FROM

# all clinical domains + definitions
(SELECT person_id, code_set, 'SICKLECELLDISEASE_CASE' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment 
FROM 
(SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
 WHERE person_id IN (SELECT person_id FROM visit_inclusion_criteria_1)
  EXCEPT DISTINCT
 SELECT person_id, code_set FROM dx_case_exclusion_criteria_1))
 
UNION ALL

# only conditions + definitions
(SELECT person_id, code_set, 'SICKLECELLDISEASE_CASE' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment 
FROM
(SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
  EXCEPT DISTINCT
 SELECT person_id, code_set FROM dx_case_exclusion_criteria_1))
;