PheKnowVec Cohort Query: https://github.com/callahantiff/PheKnowVec/wiki/Systemic-Lupus-Erythematosus

SYSTEMICLUPUSERYTHEMATOSUS_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}.SYSTEMICLUPUSERYTHEMATOSUS_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) >= 3
),

mx_case_inclusion_criteria_1 AS (
SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.measurement m, 
  {database}.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS cohort  
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 2 
  AND cohort.standard_code_set = {code_set_group}
  AND m.value_as_number > m.range_high
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_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}.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS cohort  
WHERE 
  de.drug_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 
  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}.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS cohort 
   WHERE co.condition_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
      co.person_id, code_set
   HAVING
     COUNT(DISTINCT co.condition_concept_id) >= 1
)

SELECT * FROM
  (SELECT person_id, code_set, 'SYSTEMICLUPUSERYTHEMATOSUS_CASE' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM mx_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)
;

SYSTEMICLUPUSERYTHEMATOSUS_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}.SYSTEMICLUPUSERYTHEMATOSUS_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
),

mx_case_inclusion_criteria_1 AS (
SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.measurement m, 
  {database}.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS cohort  
WHERE 
  m.measurement_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 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_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}.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS cohort  
WHERE 
  de.drug_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 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
)

SELECT * FROM

  # all clinical domains + codes
  (SELECT person_id, code_set, 'SYSTEMICLUPUSERYTHEMATOSUS_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
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM mx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM rx_case_inclusion_criteria_1)
    GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
   
 UNION ALL
 
  # only conditions + codes
  (SELECT person_id, code_set, 'SYSTEMICLUPUSERYTHEMATOSUS_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)
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;

SYSTEMICLUPUSERYTHEMATOSUS_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}.SYSTEMICLUPUSERYTHEMATOSUS_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) >= 3
),

mx_case_inclusion_criteria_1 AS (
SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.measurement m, 
  {database}.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS cohort  
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 2 
  AND cohort.standard_code_set = {code_set_group}
  AND m.value_as_number > m.range_high
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_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}.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS cohort  
WHERE 
  de.drug_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 
  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}.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS cohort 
   WHERE co.condition_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
      co.person_id, code_set
   HAVING
     COUNT(DISTINCT co.condition_concept_id) >= 1
)

SELECT * FROM

  # all clinical domains + definitions
  (SELECT person_id, code_set, 'SYSTEMICLUPUSERYTHEMATOSUS_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
    INTERSECT DISTINCT 
   SELECT person_id, code_set FROM mx_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

 # only conditions + definitions
 (SELECT person_id, code_set, 'SYSTEMICLUPUSERYTHEMATOSUS_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)
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;