PheKnowVec Cohort Query: https://github.com/callahantiff/PheKnowVec/wiki/Sleep-Apnea

SLEEPAPNEA_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}.SLEEPAPNEA_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 person_id, code_set, 'SLEEPAPNEA_CASE' AS cohort_type FROM (
  SELECT * FROM dx_case_inclusion_criteria_1)
;

SLEEPAPNEA_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}.SLEEPAPNEA_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, 'SLEEPAPNEA_CASE' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
FROM (SELECT * FROM dx_case_inclusion_criteria_1))

UNION ALL

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

SLEEPAPNEA_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}.SLEEPAPNEA_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 + definitions
(SELECT person_id, code_set, 'SLEEPAPNEA_CASE' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
FROM (SELECT * FROM dx_case_inclusion_criteria_1))

UNION ALL

# only conditions + definition
(SELECT person_id, code_set, 'SLEEPAPNEA_CASE' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
FROM (SELECT * FROM dx_case_inclusion_criteria_1))
;