Drug Exposure‎ > ‎

DEX11: Distribution of brands used for a given generic drug

This query provides the brands that are used for a generic drug. The input to the query is a value of a drug_concept_id. See vocabulary queries for obtaining valid drug_concept_id values.

Note that depending on the mapping available for the source_values in the drug_exposure table, branded drug information might only partially or not be provided. See the Standard Vocabulary Specifications at


 Parameter  Example  Mandatory  Notes
drug_concept_id 19019306 Yes Nicotine Patch

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue.

SELECT tt.drug_name, tt.brand_name,
       100.00*tt.part_brand/tt.total_brand as perc_brand_count
      t.drug_name, t.brand_name,
      t.cn_3_02 part_brand,
      SUM(t.cn_3_02) OVER() total_brand
  FROM (
      SELECT sum((select count(1) from drug_exposure d where d.drug_concept_id = cr003.concept_id_2)) cn_3_02,
              A.Concept_Name            drug_name,
              D.Concept_Name            brand_name
      FROM   vocabulary.concept_relationship  CR003,
             vocabulary.concept               A,
             vocabulary.concept_relationship  CR007,
             vocabulary.concept_relationship  CR006,
             vocabulary.concept                 D
      WHERE  CR003.relationship_ID  = 3
      AND    CR003.concept_id_1     = A.concept_id
      AND    lower(A.concept_class) = 'clinical drug'
      AND    CR007.concept_id_2     = CR003.concept_id_2
      AND    CR007.relationship_ID  = 7
      AND    CR007.concept_id_1     = CR006.concept_id_1
      AND    CR006.relationship_ID  = 6
      AND    CR006.concept_id_2     = D.concept_id
      AND    lower(D.concept_class) = 'brand name'
      AND    A.concept_Id           = 19019306
      GROUP BY
              A.Concept_Name ,
  ) t  
) tt
WHERE tt.total_brand > 0      

Output field list:
 Field  Description
drug_name The name of the query drug
brand_name The name of the brand
The market share for each brand

Sample output record:
 Field Content