week4-exercises.qmd

---
title: "Week 4 Exercises"
---

We'll first connect to the database:

```{r}
#| context: setup
library(duckdb)
library(DBI)
library(DiagrammeR)
con <- DBI::dbConnect(duckdb::duckdb(), 
                      "data/GiBleed_5.3_1.1.duckdb")
                      
```

## Subquery in `SELECT`

1.  Fill in the blank in the subquery below to find each patient's demographic data along with the **total number of procedures** they have had. Note that this query makes use of the `person` table as well as the `procedure_occurrence` table.

```{sql connection="con"}
SELECT 
  person_id,
  gender_source_value,
  race_source_value,
  (SELECT 
      COUNT(*) 
    FROM 
      procedure_occurrence 
    WHERE 
      person.person_id = ___________.person_id
  ) AS number_of_procedures
FROM 
  person;
```

2.  Fill in the blank in the query below to dynamically calculate the **number of months** between the **procedure date** and today for all procedures from the `procedure_occurrence` table

```{sql connection="con"}
#| eval: false
SELECT 
  person_id,
  visit_occurrence_id,
  procedure_occurrence_id,
  procedure_concept_id, 
  procedure_datetime,
  (SELECT 
    DATE_DIFF(
      'month', ______, DATE '2025-11-7'
    )
  ) AS procedure_time_to_today
FROM 
  procedure_occurrence;
```

## Subquery in `WHERE`

Collect patient demographic data for all patients who have an occurrence of a `condition_occurrence_id` = "40481087":

```{sql connection="con"}
SELECT 
  person_id, 
  birth_datetime, 
  gender_source_value, 
  race_source_value, 
  ethnicity_source_value
FROM 
  person
WHERE 
  person_id IN (
    SELECT 
      person_id 
    FROM 
      condition_occurrence
    WHERE 
      _____________ == '40481087'
  );
    
```

## Challenge: Creating a view (using `DATEDIFF` in a subquery)

5.  Create a view for senior citizen procedures, where we collect procedure occurrences for all patients aged \>= 50 at the time of their procedure.

Break it down: Create a query for patients aged \>= 50. You will need to use the `person` table and use `DATE_DIFF` function on the `birth_datetime` column.

```{sql connection="con"}
SELECT ---
FROM ---
WHERE DATE_DIFF('year', ----, DATE '2024-03-07') >= ---
```

Then, write the outer query of the view via the `person` table filtering where the `person_id` corresponds to your query above:

```{sql}
#| connection: "con"
CREATE VIEW senior_citizen_procedures AS

  
```

```{sql connection="con"}
SELECT * FROM senior_citizen_procedures 
```