Hypertension Control

WITH RECURSIVE
subtypeids (sourceId) AS (
   -- This recursive CTE returns rows containing subtype SCTIDs
   SELECT r.sourceId
   FROM sct_relationship r
   WHERE r.destinationId = '38341003'               -- concept for 'Hypertension (disorder)'
      AND r.typeId = '116680003'                     -- relationship is a 'is a' relationship
      AND active = 1                               -- relationship is active
   UNION ALL
   SELECT r.sourceId
   FROM sct_relationship r
               INNER JOIN subtypeids ON r.destinationId = subtypeids.sourceid
   WHERE r.active = 1                             -- relationship is active
      AND r.typeId = '116680003' -- relationship is a 'is a' relationship)
),
sctids (sourceId) AS (
   SELECT '38341003' as sourceId -- add parent concept to get the final set of ids to find
   UNION ALL
   SELECT DISTINCT sourceId -- IMPORTANT: distinct is required
   FROM subtypeids
),
patient_ers AS ( -- find the experiencer relation ids where patient  is implicit or explicitly mentioned
   SELECT DISTINCT er.id AS experiencer_rel_id, e.type_ as mention
   FROM experiencerrelation er
               JOIN entity e
                  ON er.experiencer_id = e.id -- NOTE: find experience(r) entities
               JOIN foundentity fe
                  ON fe.id = e.entity_id AND e.type_ = 'found' -- IMPORTANT: this condition is required.
               JOIN foundentityconcept fec
                  ON fe.id = fec.found_entity_id
               JOIN concept c
                  ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
   WHERE ( c.ontology = 'snomed' AND c.concept_id = '116154003')  -- SCTID of 'Patient (person)'
   UNION
   SELECT er.id AS experiencer_rel_id, e.type_ as mention
   FROM entity e
               JOIN experiencerrelation er
                  ON er.experiencer_id = e.id -- NOTE: experience(r) entities
               JOIN assumedentity ae
                  ON ae.id = e.entity_id  AND e.type_ = 'assumed' -- NOTE: this condition is required.
   WHERE ae.value = 'patient'
)
SELECT -- count(*)
   -- fe.id as feid, qr.id, er.id,  -- debug use
   c.description                           AS hypertension_concept,
   c.concept_id                            AS subject_cid,
   fe.polarity,
   fe.uncertainty,
   q_c.description                         AS qualifier_concept,
   q_c.concept_id                          AS qualifier_cid,
   CASE
      WHEN c.concept_id='401117004' THEN 'controlled/moderate' -- Moderate hypertension control (finding)
      WHEN c.concept_id='170577003' THEN 'controlled/good'     -- Good hypertension control (finding)
      WHEN c.concept_id='170578008' THEN 'uncontrolled/poor'   -- Poor hypertension control (finding)
      WHEN c.concept_id NOT IN('401117004','170577003','170578008') AND q_c.concept_id='19032002' THEN 'uncontrolled'
      WHEN c.concept_id NOT IN('401117004','170577003','170578008') AND q_c.concept_id='31509003' THEN 'controlled'
      ELSE 'unknown'
   END control_label,
   fe.text                                AS term,
   regexp_replace(d.filename, '.*/', '') AS filename,
   REGEXP_REPLACE(sl.text, '\n', ' ') AS sentence
FROM patient_ers
      JOIN experiencerrelation er ON patient_ers.experiencer_rel_id = er.id
      JOIN entity e ON er.experienced_id = e.id  -- NOTE: join on experience(d)_id
      JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
      JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
      JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology AND c.ontology = 'snomed'
      JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
      JOIN location l ON fel.location_id=l.id AND (l.type_='sentence')
      JOIN sentencelocation sl ON sl.id = l.location_id AND (l.type_ = 'sentence')
      JOIN document d ON d.id = l.document_id
      -- Left join to retrieve qualifier values when present or NULL if not
      LEFT JOIN qualifierrelation qr on e.id = qr.qualifies_id -- NOTE: join on qualifie(s)_id
      LEFT JOIN entity q_e ON qr.qualifier_id = q_e.id AND q_e.type_ = 'found'
      LEFT JOIN foundentity q_fe ON q_e.entity_id = q_fe.id
      LEFT JOIN foundentityconcept q_fec ON q_fe.id = q_fec.found_entity_id  AND q_fec.concept_ontology = 'snomed'
      LEFT JOIN concept q_c ON q_fec.concept_id = q_c.concept_id AND q_fec.concept_ontology = q_c.ontology
WHERE c.ontology = 'snomed'
-- 'Hypertensive disorder, systemic arterial (disorder)' and its subtypes
AND c.concept_id IN (SELECT * FROM sctids) AND (q_c.concept_id IS NULL OR q_c.concept_id IN ( '19032002', '31509003' )) -- controlled, uncontrolled or null
-- additional related concepts related to types of control of hypertension
OR c.concept_id IN ( '401117004', '170577003', '170578008' ) -- hypertension concepts with inherent qualifier semantics
ORDER BY control_label;