PheKnowVec Cohort Query: https://github.com/callahantiff/PheKnowVec/wiki/ADHD

ADHD_COHORT.sql

WITH age_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.person p, 
  {database}.condition_occurrence co, 
  {database}.ADHD_COHORT_VARS cohort 
WHERE p.person_id = co.person_id 
  AND 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, p.birth_datetime, cohort.standard_code_set
HAVING
  DATETIME_DIFF(DATETIME(MIN(co.condition_start_datetime)), DATETIME(p.birth_datetime), DAY) >= 1460
),

dx_case_inclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.ADHD_COHORT_VARS cohort, 
  {database}.visit_occurrence v 
WHERE 
  co.visit_occurrence_id = v.visit_occurrence_id 
  AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND v.admitting_source_concept_id IN (44814672, 8870, 38004279, 8863, 8546) 
  AND cohort.phenotype_definition_number = 1 
  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 
  AND COUNT(DISTINCT v.visit_start_date) >= 1
),

rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.ADHD_COHORT_VARS cohort 
WHERE 
  de.drug_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 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 3
  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
),

dx_case_inclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.ADHD_COHORT_VARS cohort, 
  {database}.visit_occurrence v 
WHERE 
  co.visit_occurrence_id = v.visit_occurrence_id 
  AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND v.admitting_source_concept_id IN (44814672, 8870, 38004279, 8863, 8546) 
  AND cohort.phenotype_definition_number = 1 
  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 
  AND COUNT(DISTINCT v.visit_start_date) >= 2
),

age_criteria_2 AS (
SELECT co.person_id, {code_set_group} AS code_set 
FROM 
  {database}.person p,
  {database}.condition_occurrence co
WHERE p.person_id = co.person_id 
GROUP BY
  co.person_id, p.birth_datetime
HAVING
  DATETIME_DIFF(DATETIME(MIN(co.condition_start_datetime)), DATETIME(p.birth_datetime), DAY) >= 1460
),

visit_criteria_1 AS (
SELECT v.person_id, {code_set_group} AS code_set 
FROM 
  {database}.visit_occurrence v
GROUP BY 
  v.person_id, v.visit_end_datetime 
HAVING
  DATETIME(visit_end_datetime) >= DATETIME_ADD((SELECT DATETIME(MAX(visit_end_datetime)) FROM {database}.visit_occurrence), INTERVAL -5 YEAR)
  AND COUNT(DISTINCT v.visit_start_date) >= 2
),

rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 4
  AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 5
  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
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 6
  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
)

SELECT * FROM
  (SELECT person_id, code_set, 'ADHD_CASE_TYPE1' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM age_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM rx_case_inclusion_criteria_1)
      EXCEPT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT person_id, code_set, 'ADHD_CASE_TYPE2' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM age_criteria_1
      INTERSECT DISTINCT 
    SELECT person_id, code_set FROM dx_case_inclusion_criteria_2)
      EXCEPT DISTINCT
    SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
  GROUP BY person_id, code_set, cohort_type)
  
  UNION ALL 
  
  (SELECT p.person_id, {code_set_group} AS code_set, 'ADHD_CONTROL' AS cohort_type
    FROM {database}.person p
    WHERE p.person_id NOT IN (
      SELECT person_id FROM rx_control_exclusion_criteria_1
        UNION DISTINCT
      SELECT person_id FROM dx_control_exclusion_criteria_1
        UNION DISTINCT
      SELECT person_id FROM dx_control_exclusion_criteria_2)
    AND p.person_id IN (
      SELECT person_id FROM age_criteria_2
        INTERSECT DISTINCT
      SELECT person_id FROM visit_criteria_1)
  GROUP BY p.person_id, code_set, cohort_type)
;

ADHD_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}.ADHD_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, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1 
),

rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.ADHD_COHORT_VARS cohort 
WHERE 
  de.drug_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 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 3
  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
),

dx_case_inclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.ADHD_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, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1 
),

rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 4
  AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 5
  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
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 6
  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
)

SELECT * FROM

  # all clinical domains + codes
  (SELECT person_id, code_set, 'ADHD_CASE_TYPE1' 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
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM rx_case_inclusion_criteria_1)
      EXCEPT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
   GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

  UNION ALL

  (SELECT person_id, code_set, 'ADHD_CASE_TYPE2' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
    FROM (
     SELECT person_id, code_set FROM dx_case_inclusion_criteria_2
      EXCEPT DISTINCT
    SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
  
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'ADHD_CONTROL' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
    FROM {database}.person p
    WHERE p.person_id NOT IN (
      SELECT person_id FROM rx_control_exclusion_criteria_1
        UNION DISTINCT
      SELECT person_id FROM dx_control_exclusion_criteria_1
        UNION DISTINCT
      SELECT person_id FROM dx_control_exclusion_criteria_2)
   GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

UNION ALL

  # only conditions + codes
  (SELECT person_id, code_set, 'ADHD_CASE_TYPE1' 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)
      EXCEPT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
   GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

  UNION ALL

  (SELECT person_id, code_set, 'ADHD_CASE_TYPE2' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
    FROM (
    (SELECT person_id, code_set FROM dx_case_inclusion_criteria_2)
      EXCEPT DISTINCT
    SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
  
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'ADHD_CONTROL' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
    FROM {database}.person p
    WHERE p.person_id NOT IN (
      SELECT person_id FROM dx_control_exclusion_criteria_1
        UNION DISTINCT
      SELECT person_id FROM dx_control_exclusion_criteria_2)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment) 
;

ADHD_COHORT_DEFINITIONS.SQL

-- DEFINITIONS + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY

WITH age_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.person p, 
  {database}.condition_occurrence co, 
  {database}.ADHD_COHORT_VARS cohort 
WHERE p.person_id = co.person_id 
  AND 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, p.birth_datetime, cohort.standard_code_set
HAVING
  DATETIME_DIFF(DATETIME(MIN(co.condition_start_datetime)), DATETIME(p.birth_datetime), DAY) >= 1460
),

dx_case_inclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.ADHD_COHORT_VARS cohort, 
  {database}.visit_occurrence v 
WHERE 
  co.visit_occurrence_id = v.visit_occurrence_id 
  AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND v.admitting_source_concept_id IN (44814672, 8870, 38004279, 8863, 8546) 
  AND cohort.phenotype_definition_number = 1 
  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 
  AND COUNT(DISTINCT v.visit_start_date) >= 1
),

rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.ADHD_COHORT_VARS cohort 
WHERE 
  de.drug_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 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 3
  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
),

dx_case_inclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.ADHD_COHORT_VARS cohort, 
  {database}.visit_occurrence v 
WHERE 
  co.visit_occurrence_id = v.visit_occurrence_id 
  AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND v.admitting_source_concept_id IN (44814672, 8870, 38004279, 8863, 8546) 
  AND cohort.phenotype_definition_number = 1 
  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 
  AND COUNT(DISTINCT v.visit_start_date) >= 2
),

age_criteria_2 AS (
SELECT co.person_id, {code_set_group} AS code_set 
FROM 
  {database}.person p,
  {database}.condition_occurrence co
WHERE p.person_id = co.person_id 
GROUP BY
  co.person_id, p.birth_datetime
HAVING
  DATETIME_DIFF(DATETIME(MIN(co.condition_start_datetime)), DATETIME(p.birth_datetime), DAY) >= 1460
),

visit_criteria_1 AS (
SELECT v.person_id, {code_set_group} AS code_set 
FROM 
  {database}.visit_occurrence v
GROUP BY 
  v.person_id, v.visit_end_datetime 
HAVING
  DATETIME(visit_end_datetime) >= DATETIME_ADD((SELECT DATETIME(MAX(visit_end_datetime)) FROM {database}.visit_occurrence), INTERVAL -5 YEAR)
  AND COUNT(DISTINCT v.visit_start_date) >= 2
),

rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 4
  AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 5
  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
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.ADHD_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 6
  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
)

SELECT * FROM
  
  # all clinical domains + definitions
  (SELECT person_id, code_set, 'ADHD_CASE_TYPE1' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    FROM (
    (SELECT person_id, code_set FROM age_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM rx_case_inclusion_criteria_1)
      EXCEPT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
   GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

  UNION ALL

  (SELECT person_id, code_set, 'ADHD_CASE_TYPE2' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    FROM (
    (SELECT person_id, code_set FROM age_criteria_1
      INTERSECT DISTINCT 
    SELECT person_id, code_set FROM dx_case_inclusion_criteria_2)
      EXCEPT DISTINCT
    SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
  
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'ADHD_CONTROL' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    FROM {database}.person p
    WHERE p.person_id NOT IN (
      SELECT person_id FROM rx_control_exclusion_criteria_1
        UNION DISTINCT
      SELECT person_id FROM dx_control_exclusion_criteria_1
        UNION DISTINCT
      SELECT person_id FROM dx_control_exclusion_criteria_2)
    AND p.person_id IN (
      SELECT person_id FROM age_criteria_2
        INTERSECT DISTINCT
      SELECT person_id FROM visit_criteria_1)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

UNION ALL

  # only conditions + definitions
  (SELECT person_id, code_set, 'ADHD_CASE_TYPE1' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    FROM (
    (SELECT person_id, code_set FROM age_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_case_inclusion_criteria_1)
      EXCEPT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
   GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

  UNION ALL

  (SELECT person_id, code_set, 'ADHD_CASE_TYPE2' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    FROM (
    (SELECT person_id, code_set FROM age_criteria_1
      INTERSECT DISTINCT 
    SELECT person_id, code_set FROM dx_case_inclusion_criteria_2)
      EXCEPT DISTINCT
    SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
  
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'ADHD_CONTROL' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    FROM {database}.person p
    WHERE p.person_id NOT IN (
      SELECT person_id FROM dx_control_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id FROM dx_control_exclusion_criteria_2)
    AND p.person_id IN (
      SELECT person_id FROM age_criteria_2
        INTERSECT DISTINCT
      SELECT person_id FROM visit_criteria_1)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;