exercise answers sql

-- How many patients had a disease episode of primary malignant neoplasm of bladder with stage pT1? -- N = 42743

SELECT COUNT(DISTINCT ep.person_id)

FROM cdm_oncology.episode ep

INNER JOIN cdm_oncology.measurement meas

ON ep.episode_object_concept_id IN (

SELECT descendant_concept_id

FROM cdm_oncology.concept_ancestor

WHERE ancestor_concept_id = 196360			

)

AND meas.modifier_of_field_concept_id = 123456 -- 'episode.episode_id'

AND meas.modifier_of_event_id = ep.episode_id

AND meas.measurement_concept_id = 1538550 -- 'pT1'

;

---

-- How many patients had an episode of remission after an episode of metastatic disease for the same overall disease?

-- N = 19203

SELECT COUNT(DISTINCT e1.person_id)

FROM cdm_oncology.episode e1

INNER JOIN cdm_oncology.episode e2

ON e1.episode_parent_id = e2.episode_parent_id

AND e1.episode_start_datetime < e2.episode_start_datetime

-- OR AND e1.episode_number < e2.episode_number

AND e1.episode_concept_id = 32944 -- 'Metastatic Disease'

AND e2.episode_concept_id = 32945 -- 'Remission'

;

---

-- How many patients received a treatment regimen involving the drug ingredient Methotrexate?

-- Approach 1 : via EPISODE_EVENT and DRUG_EXPOSURE

-- N = 3398

SELECT COUNT (distinct drug.person_id)

FROM cdm_oncology.episode_event ev

INNER JOIN cdm_oncology.drug_exposure drug

ON episode_event_field_concept_id = 1147094 -- 'drug_exposure.drug_exposure_id'

AND ev.event_id = drug.drug_exposure_id

AND drug.drug_concept_id in (

SELECT descendant_concept_id

FROM cdm_oncology.concept_ancestor

WHERE ancestor_concept_id = 1305058			-- 'Methotrexate' -- ingredient

)

;

-- What is the maximum number of treatment cycles given for a Mitomycin monotherapy treatment regimen?

-- result : 124

SELECT MAX(child_episodes.episode_number)

FROM

(

SELECT * 

FROM cdm_oncology.episode 

WHERE episode_concept_id = 32531 			-- 'treatment regimen'

AND episode_object_concept_id = 35804136	-- 'Mitomycin monotherapy' 

) parent_episodes

INNER JOIN cdm_oncology.episode child_episodes

ON parent_episodes.episode_id = child_episodes.episode_parent_id

;

OHDSI 2020 Oncology Tutorial - Block 2 (SQL) ANSWERS.txt Displaying OHDSI 2020 Oncology Tutorial - Block 2 (SQL) ANSWERS.txt.