Sample Queries

Sample Scenarios

Queries solving scenarios are provided in the section below

1. Patient Count for a Specific Condition: This would involve running a query to count the number of patients diagnosed with a particular medical condition.
Ex. Finding the number of patients in the database that have the condition of “Acute renal failure on dialysis”, a serious kidney malfunction that causes the patient to rely on a special machine to remove waste from the blood. 2. Count number of patients of certain diseases 3. Demographic Analysis: Running queries to retrieve demographic information of patients.
Ex. Find the age, gender, or other distribution of patients with a specific disease. 4. Medication Usage: Identifying the number of patients who have been prescribed to medication in the past 3 months. This might be useful for assessing the usage rates of different drugs. 5. Clinical Outcomes Analysis: Determining number of patients and their status prescribed by certain medication.

Queries with scenarios

- Scenario 1

SPARQL SQL 
PREFIX fhir: <http://hl7.org/fhir/>

SELECT  ?display ?code ?system (count(?patient) AS ?totalPatients)
WHERE {
	?condition a fhir:Condition;
        fhir:Condition.subject [fhir:link ?patient];
		fhir:Condition.code[
		   fhir:CodeableConcept.coding[ 
			fhir:Coding.code[fhir:value ?code];
			fhir:Coding.system[fhir:value ?system];
			fhir:Coding.display[fhir:value ?display]
		]
	].
FILTER(regex(?display, ".*renal.*", "i"))
FILTER (str(?system) = 'SNOMED' )

}
GROUP BY ?display ?code ?system 

          
SELECT cp.concept_code AS code, cp.concept_id, cp.vocabulary_id AS system, cp.concept_name AS display, COUNT(DISTINCT co.person_id) AS patient_count
FROM condition_occurrence co
JOIN concept cp ON co.condition_concept_id = cp.concept_id
WHERE cp.concept_name ILIKE '%renal%' AND cp.vocabulary_id = 'SNOMED'
GROUP BY cp.concept_name, cp.concept_id

- Scenario 2

SPARQL SQL 
PREFIX fhir: <http://hl7.org/fhir/>

SELECT DISTINCT ?code ?system ?display (count(?patient) as ?count)
WHERE {
  ?condition a fhir:Condition ;
             fhir:Condition.subject ?patient ;
             fhir:Condition.code [
               fhir:CodeableConcept.coding [
                 fhir:Coding.code [ fhir:value ?code ] ;
                 fhir:Coding.system [ fhir:value ?system ] ;
                 fhir:Coding.display [ fhir:value ?display ]
               ]
             ] .

FILTER(regex(?display, ".*acute renal failure.*", "i")) 
FILTER (str(?system) = 'SNOMED')
  
}
GROUP BY ?code ?system ?display ORDER BY ?count


SELECT cp.concept_code AS code, cp.concept_id, cp.vocabulary_id AS system, cp.concept_name AS display, 
COUNT(DISTINCT co.person_id) AS patient_count
FROM omop.condition_occurrence co
JOIN omop.concept cp ON co.condition_concept_id = cp.concept_id
WHERE cp.concept_name ILIKE '%acute renal failure%' AND cp.vocabulary_id = 'SNOMED'
GROUP BY cp.concept_name, cp.concept_id
ORDER BY patient_count ASC

- Scenario 3

SPARQL SQL 
PREFIX fhir: <http://hl7.org/fhir/>

SELECT DISTINCT ?condition ?code ?system ?display ?patient ?gender ?birthdate
WHERE {
  ?condition a fhir:Condition ;
             fhir:Condition.code ?codeResource .
  ?codeResource fhir:CodeableConcept.coding ?coding .
  ?coding fhir:Coding.code [ fhir:value ?code ] ;
          fhir:Coding.system [ fhir:value ?system] ;
          fhir:Coding.display [ fhir:value ?display ] .
  ?condition fhir:Condition.subject [fhir:link ?patient].
  ?patient fhir:Patient.gender [ fhir:value ?gender ] ;
           fhir:Patient.birthDate [ fhir:value ?birthdate ] .
  
  FILTER(regex(?display, ".*acute.*", "i"))
}



SELECT c.condition_occurrence_id AS condition, cpt.concept_id AS code, cpt.concept_name AS condition_name, cpt.vocabulary_id AS display, p.person_id AS patient, p.gender_source_value AS gender, p.birth_datetime AS birthdate
FROM omop.condition_occurrence AS c
JOIN omop.concept AS cpt ON c.condition_concept_id = cpt.concept_id
JOIN omop.person AS p ON c.person_id = p.person_id
WHERE LOWER (cpt.concept_name) LIKE '%acute%'

- Scenario 4

SPARQL SQL 
PREFIX fhir: <http://hl7.org/fhir/>

SELECT *
WHERE {
  ?medicationStatement a fhir:MedicationStatement ;
                       fhir:MedicationStatement.status [ fhir:value ?status ] ;
                       fhir:MedicationStatement.subject [ fhir:link ?patient ] ;
                      
                                                          
                       fhir:MedicationStatement.medicationCodeableConcept [
                         fhir:CodeableConcept.coding [
                           fhir:Coding.code [ fhir:value ?code ] ;
                           fhir:Coding.display [ fhir:value ?medication ]
                         ]
                       ] .
  					   
  OPTIONAL{?medicationStatement fhir:MedicationStatement.effectiveDateTime [fhir:value ?prescribedTime].}
  OPTIONAL{?medicationStatement fhir:MedicationStatement.effectivePeriod [fhir:value ?effectivePeriod].}
  FILTER (?prescribedTime > "2149-12-20T00:00:00"^^xsd:dateTime)

}
LIMIT 100


SELECT drug_exposure_id, person_id, concept_code, concept_name AS medication,
drug_exposure_start_datetime as pescribed_time
FROM omop.drug_exposure AS de
JOIN omop.concept AS cpt 
ON de.drug_concept_id = cpt.concept_id
WHERE drug_exposure_start_datetime > '2149-12-20'
LIMIT 100

- Scenario 5

SPARQL SQL 
PREFIX fhir: <http://hl7.org/fhir/>


SELECT  ?medicationID ?medication ?observationStatus  (COUNT(?patient) AS ?count)
WHERE {
	?observation a fhir:Observation;
		fhir:Observation.status [fhir:value ?observationStatus];
		fhir:Observation.subject [fhir:link ?patient];
		fhir:Observation.effectiveDateTime [fhir:value ?time].
	?medicationStatement a fhir:MedicationStatement;
		fhir:MedicationStatement.subject [fhir:link ?patient];
  		fhir:MedicationStatement.medicationCodeableConcept[
      	fhir:CodeableConcept.coding [
        		fhir:Coding.display [ fhir:value ?medication ];
      		    fhir:Coding.code [ fhir:value ?medicationID ]
    ]
  ].
FILTER (regex(?medicationID, ".*288.*", "i"))

}
GROUP BY ?observationStatus ?medicationID ?medication




SELECT concept_code AS medicationID, concept_name AS medication, count(de.drug_exposure_id)AS patient_count
FROM omop.concept cpt
JOIN omop.drug_exposure de
ON de.drug_concept_id = cpt.concept_id
JOIN omop.person p
ON de.person_id = p.person_id
WHERE concept_code LIKE '%288%'
GROUP BY concept_code, concept_name

Important Syntaxes Used

In the development of queries to address various challenges, certain key syntaxes play a crucial role. Among them, FILTER, OPTIONAL, and GROUP BY are instrumental for effective data extraction and analysis.
The FILTER keyword is versatile, allowing the filtering of query results based on specific conditions or constraints. It can be used in conjunction with comparisons, regular expressions, logical operators, and functions, providing a wide range of options for refining query results.
OPTIONAL is used to specify optional patterns within a query. Patterns defined under OPTIONAL might not find a match in the data, enabling queries to return results even if certain optional patterns do not match. This syntax is particularly useful when some data might be missing or unavailable, ensuring that the query still retrieves other relevant information.
Similarly, GROUP BY functions in SPARQL as it does in SQL. It groups query results based on one or more variables, facilitating data aggregation and enabling various calculations or operations on grouped data subsets.