# PURPOSE: This query is designed to query an OMOP instance and return 6 columns.
# This query makes the assumption that the other shops would be willing to return
# some results to us, rather than calculating coverage statistics locally 

WITH 
  condition_concepts
  AS (SELECT
        c.condition_concept_id AS CONCEPT_ID,
        c1.concept_name AS CONCEPT_LABEL,
        v.vocabulary_version AS VOCABULARY_VERSION,
        COUNT(c.condition_concept_id) AS CONCEPT_COUNT,
        COUNT(DISTINCT c.visit_occurrence_id) AS VISIT_COUNT,
        COUNT(DISTINCT c.person_id) AS PATIENT_COUNT
      FROM 
        CHCO_DeID_Oct2018.condition_occurrence c 
        JOIN CHCO_DeID_Oct2018.concept c1 ON c.condition_concept_id = c1.concept_id
        JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id
      WHERE 
        c1.concept_name != "No matching concept"
        AND c1.domain_id = "Condition"
        AND c1.vocabulary_id = "SNOMED"
        AND c1.standard_concept = 'S'
      GROUP BY CONCEPT_ID, CONCEPT_LABEL, VOCABULARY_VERSION),
  
  measurement_concepts
  AS (SELECT
        m.measurement_concept_id AS CONCEPT_ID,
        c.concept_name AS CONCEPT_LABEL,
        v.vocabulary_version AS VOCABULARY_VERSION,
        COUNT(m.measurement_concept_id) AS CONCEPT_COUNT,
        COUNT(DISTINCT m.visit_occurrence_id) AS VISIT_COUNT,
        COUNT(DISTINCT m.person_id) AS PATIENT_COUNT
      FROM 
        CHCO_DeID_Oct2018.measurement m 
        JOIN CHCO_DeID_Oct2018.concept c ON m.measurement_concept_id = c.concept_id
        JOIN CHCO_DeID_Oct2018.vocabulary v ON c.vocabulary_id = v.vocabulary_id
      WHERE 
        c.concept_name != "No matching concept"
        AND c.domain_id = "Measurement"
        AND c.vocabulary_id = "LOINC"
        AND c.standard_concept = 'S'
      GROUP BY CONCEPT_ID, CONCEPT_LABEL, VOCABULARY_VERSION),
  
  ingredient_concepts
  AS (SELECT
        c1.concept_id AS CONCEPT_ID,
        c1.concept_name AS CONCEPT_LABEL,
        v.vocabulary_version AS VOCABULARY_VERSION,
        COUNT(d.drug_concept_id) AS CONCEPT_COUNT,
        COUNT(DISTINCT d.visit_occurrence_id) AS VISIT_COUNT,
        COUNT(DISTINCT d.person_id) AS PATIENT_COUNT
      FROM 
        CHCO_DeID_Oct2018.concept_ancestor ca
        JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id
        INNER JOIN CHCO_DeID_Oct2018.drug_exposure d ON ca.descendant_concept_id = d.drug_concept_id
        JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id
      WHERE 
        c1.concept_name != "No matching concept"
        AND c1.domain_id = "Drug"
        AND c1.concept_class_id = "Ingredient"
        AND c1.vocabulary_id = "RxNorm"
        AND c1.standard_concept = 'S'
      GROUP BY CONCEPT_ID, CONCEPT_LABEL, VOCABULARY_VERSION)

SELECT *
FROM
  (SELECT CONCEPT_ID, CONCEPT_LABEL, VOCABULARY_VERSION, VISIT_COUNT, PATIENT_COUNT, CONCEPT_COUNT FROM condition_concepts
    UNION DISTINCT
   SELECT CONCEPT_ID, CONCEPT_LABEL, VOCABULARY_VERSION, VISIT_COUNT, PATIENT_COUNT, CONCEPT_COUNT FROM measurement_concepts
    UNION DISTINCT
   SELECT CONCEPT_ID, CONCEPT_LABEL, VOCABULARY_VERSION, VISIT_COUNT, PATIENT_COUNT, CONCEPT_COUNT FROM ingredient_concepts);