Demonstrate the richness of the data that are available in OMOP at UCLH for patients with a stroke phenotype

stroke_and_omop_exemplar.sql

-- example script to extract stroke diagnoses
-- concept_id pulled from https://athena.ohdsi.org/search-terms/start by searching for ICD or OPCS codes
-- e.g. OPCS U54.3 --> https://athena.ohdsi.org/search-terms/terms/44514242

SELECT 
	c.* 
FROM dsf_omop.condition_occurrence c
LEFT JOIN dsf_omop.concept co ON c.condition_concept_id = co.concept_id
WHERE 
	-- see https://github.com/spiros/chronological-map-phenotypes/blob/master/secondary_care/ICD_Isch_stroke.csv
	-- ischaemic stroke
	co.concept_id IN (443454, 45591475, 45562366, 45605808, 45586592, 45596216, 45605810)
	OR
	-- sequaele of ischaemic stroke
	co.concept_id IN (45533476)
	OR
	-- haemorrhagic stroke
	co.concept_id IN (45576882, 45591473, 45562361, 45557547, 45567186, 45605803, 45562360, 45557546, 45576881, 376713, 40475099)
	OR
	-- sequaele of haemorrhagic stroke
	co.concept_id IN (45538404)
--LIMIT 3
;

-- I think the nice thing would then be to show the richness of the data here
-- so if we joined against visit occurrence onto a CTE then we could report how many
-- different measuresments we have for these patients
-- and then repeat for obs, procedures, drugs, etc.
WITH q AS (
SELECT 
	M.measurement_concept_id
	,COUNT(*) N
FROM dsf_omop.measurement M
GROUP BY M.measurement_concept_id
ORDER BY n DESC
LIMIT 1000
)

SELECT 
	 q.measurement_concept_id
	,C.concept_name
	,q.n
FROM q
LEFT JOIN dsf_omop.concept C
	ON q.measurement_concept_id = C.concept_id
;