### Set PostgreSQL Search Path

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Sets the PostgreSQL search path to the 'mimiciii' schema. This is a prerequisite for running queries assuming MIMIC-III is installed under this schema.

```sql
set search_path to mimiciii;

```

--------------------------------

### Calculate Pre-ICU Stay Duration

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This SQL query extends the previous one by calculating the duration (in days) between the admission time and the ICUSTAY start time (preiculos). It also incorporates the admissions table.

```SQL
SELECT ie.subject_id, ie.hadm_id, ie.icustay_id,
    ie.intime, ie.outtime,
    DATETIME_DIFF(adm.admittime, pat.dob, YEAR) as age,
    DATETIME_DIFF(ie.intime, adm.admittime, DAY) as preiculos,
    CASE
        WHEN DATETIME_DIFF(adm.admittime, pat.dob, YEAR) <= 1
            THEN 'neonate'
        WHEN DATETIME_DIFF(adm.admittime, pat.dob, YEAR) <= 14
            THEN 'middle'
        WHEN DATETIME_DIFF(adm.admittime, pat.dob, YEAR) > 89
            THEN '>89'
        ELSE 'adult'
        END AS ICUSTAY_AGE_GROUP
FROM `physionet-data.mimiciii_clinical.icustays` ie
INNER JOIN `physionet-data.mimiciii_clinical.patients` pat
  ON ie.subject_id = pat.subject_id
INNER JOIN `physionet-data.mimiciii_clinical.admissions` adm
  ON ie.hadm_id = adm.hadm_id;
```

--------------------------------

### SQL: Basic ICU Stays Information Retrieval

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This query selects core details of ICU stays including subject_id, hadm_id, icustay_id, intime, and outtime from the icustays table. It serves as the starting point for analyzing ICU patient data. No filters applied; outputs all ICU stay records for further joins with patients or admissions tables.

```sql
SELECT ie.subject_id, ie.hadm_id, ie.icustay_id,
    ie.intime, ie.outtime
FROM `physionet-data.mimiciii_clinical.icustays` ie;
```

--------------------------------

### Get Distinct Patient Genders

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Retrieves the unique values present in the 'gender' column of the 'patients' table.

```sql
SELECT DISTINCT(gender) 
FROM patients;

```

--------------------------------

### Calculate Earliest Admission Time (SQL)

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This SQL query finds the earliest admission time for each patient by using the MIN function and PARTITION BY clause. It joins the patients and admissions tables and calculates the first admission time for each subject.

```SQL
SELECT p.subject_id, p.dob, a.hadm_id,
    a.admittime, p.expire_flag, 
    MIN (a.admittime) OVER (PARTITION BY p.subject_id) AS first_admittime
FROM `physionet-data.mimiciii_clinical.admissions` a
INNER JOIN `physionet-data.mimiciii_clinical.patients` p
  ON p.subject_id = a.subject_id
ORDER BY a.hadm_id, p.subject_id;
```

--------------------------------

### Find earliest admission per patient

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Uses window functions to identify each patient's first admission time. Returns patient data with an additional column showing their earliest admission timestamp.

```SQL
SELECT p.subject_id, p.dob, a.hadm_id,
    a.admittime, p.expire_flag,
    MIN (a.admittime) OVER (PARTITION BY p.subject_id) AS first_admittime
FROM admissions a
INNER JOIN patients p
ON p.subject_id = a.subject_id
ORDER BY a.hadm_id, p.subject_id;
```

--------------------------------

### Retrieve Patient and Admission Data (SQL)

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This SQL query retrieves patient and admission data from the MIMIC-III database, joining the 'patients' and 'admissions' tables based on the 'subject_id'. It retrieves subject ID, date of birth, admission ID, admission time, and expire flag for each patient and admission.

```SQL
SELECT p.subject_id, p.dob, a.hadm_id,
    a.admittime, p.expire_flag
FROM `physionet-data.mimiciii_clinical.admissions` a
INNER JOIN `physionet-data.mimiciii_clinical.patients` p
  ON p.subject_id = a.subject_id;
```

--------------------------------

### Display PostgreSQL Table Metadata

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Displays detailed metadata for a specified table in PostgreSQL. This includes column names, data types, and constraints.

```sql
\d+ MIMICIII.ADMISSIONS;

```

--------------------------------

### Join patient and admission tables

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Combines patient demographics with admission records using subject_id as the join key. Returns patient ID, date of birth, admission ID, admission time, and mortality flag.

```SQL
SELECT p.subject_id, p.dob, a.hadm_id,
    a.admittime, p.expire_flag
FROM admissions a
INNER JOIN patients p
ON p.subject_id = a.subject_id;
```

--------------------------------

### Select All Columns from Patients Table

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Retrieves all columns and records from the 'patients' table in the MIMIC-III database. Results are typically paginated.

```sql
SELECT * 
FROM patients;

```

--------------------------------

### Query all patient records from MIMIC-III

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

Retrieve all patient data from the clinical dataset. This query returns 50 records per page by default in BigQuery interface. Useful for initial data exploration and understanding table structure.

```sql
SELECT *
FROM `physionet-data.mimiciii_clinical.patients`
```

--------------------------------

### Query Patient Transfers from MIMIC-III Database

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Demonstrates basic querying of patient movement data from the transfers table. Shows how to retrieve all transfer records and filter for specific patients. The transfers table tracks patient movements between care units, with columns for previous and current care units, ward IDs, and timestamps.

```SQL
SELECT *
FROM transfers;
```

```SQL
SELECT *
FROM transfers
WHERE HADM_ID = 112213;
```

--------------------------------

### Categorize Patients by Age Group (SQL)

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This SQL query uses a WITH clause to create a temporary view for patient's first admission information and then categorizes patients into age groups (neonate, middle, adult, >89). It utilizes the CASE statement to assign patients to the appropriate age category based on their first admission age.

```SQL
WITH first_admission_time AS
(
  SELECT
      p.subject_id, p.dob, p.gender
      , MIN (a.admittime) AS first_admittime
      , MIN( DATETIME_DIFF(admittime, dob, YEAR) ) 
          AS first_admit_age
  FROM `physionet-data.mimiciii_clinical.patients` p
  INNER JOIN `physionet-data.mimiciii_clinical.admissions` a
  ON p.subject_id = a.subject_id
  GROUP BY p.subject_id, p.dob, p.gender
  ORDER BY p.subject_id
)
SELECT
    subject_id, dob, gender
    , first_admittime, first_admit_age
    , CASE
        -- all ages > 89 in the database were replaced with 300
        WHEN first_admit_age > 89
            then '>89'
        WHEN first_admit_age >= 15
            THEN 'adult'
        WHEN first_admit_age <= 1
            THEN 'neonate'
        ELSE 'middle'
        END AS age_group
FROM first_admission_time
ORDER BY subject_id
```

--------------------------------

### SQL: Calculate Patient Age Groups and Count by Demographics

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This query identifies the first admission time, computes age at admission, categorizes patients into age groups (neonate, middle, adult, >89), and counts patients by age group and gender. It joins patients and admissions tables, handling age censoring for those over 89. Inputs are subject IDs; outputs include age_group, gender, and patient counts; note no middle-aged patients due to lack of pediatric data.

```sql
WITH first_admission_time AS
(
  SELECT
      p.subject_id, p.dob, p.gender
      , MIN (a.admittime) AS first_admittime
      , MIN( DATETIME_DIFF(admittime, dob, YEAR) )
          AS first_admit_age
  FROM `physionet-data.mimiciii_clinical.patients` p
  INNER JOIN `physionet-data.mimiciii_clinical.admissions` a
  ON p.subject_id = a.subject_id
  GROUP BY p.subject_id, p.dob, p.gender
  ORDER BY p.subject_id
)
, age as
(
  SELECT
      subject_id, dob, gender
      , first_admittime, first_admit_age
      , CASE
          -- all ages > 89 in the database were replaced with 300
          WHEN first_admit_age > 89
              then '>89'
          WHEN first_admit_age >= 14
              THEN 'adult'
          WHEN first_admit_age <= 1
              THEN 'neonate'
          ELSE 'middle'
          END AS age_group
  FROM first_admission_time
)
select age_group, gender
  , count(subject_id) as NumberOfPatients
from age
group by age_group, gender
```

--------------------------------

### Calculate Pre-ICU Hospital Stay Duration with Multi-Table Joins

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Comprehensive query joining icustays, patients, and admissions tables to analyze pre-ICU hospital stay duration. Calculates both patient age and time elapsed between hospital admission and ICU admission, providing insights into hospital progression and ICU timing.

```SQL
SELECT ie.subject_id, ie.hadm_id, ie.icustay_id,
    ie.intime, ie.outtime,
    ROUND((cast(ie.intime as date) - cast(pat.dob as date))/365.242, 2) as age,
    ROUND((cast(ie.intime as date) - cast(adm.admittime as date))/365.242, 2) as preiculos,
    CASE
        WHEN ROUND((cast(ie.intime as date) - cast(pat.dob as date))/365.242, 2) <= 1
            THEN 'neonate'
        WHEN ROUND((cast(ie.intime as date) - cast(pat.dob as date))/365.242, 2) <= 14
            THEN 'middle'
        -- all ages > 89 in the database were replaced with 300
        WHEN ROUND((cast(ie.intime as date) - cast(pat.dob as date))/365.242, 2) > 100
            THEN '>89'
        ELSE 'adult'
        END AS ICUSTAY_AGE_GROUP
FROM icustays ie
INNER JOIN patients pat
ON ie.subject_id = pat.subject_id
INNER JOIN admissions adm
ON ie.hadm_id = adm.hadm_id;
```

--------------------------------

### Calculate patient age groups

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii

Creates a temporary view to calculate patient ages at first admission and categorizes them into groups (neonate, middle, adult, >89). Uses date arithmetic and CASE statements for classification.

```SQL
WITH first_admission_time AS
(
  SELECT
      p.subject_id, p.dob, p.gender
      , MIN (a.admittime) AS first_admittime
      , MIN( ROUND( (cast(admittime as date) - cast(dob as date)) / 365.242,2) )
          AS first_admit_age
  FROM patients p
  INNER JOIN admissions a
  ON p.subject_id = a.subject_id
  GROUP BY p.subject_id, p.dob, p.gender
  ORDER BY p.subject_id
)
SELECT
    subject_id, dob, gender
    , first_admittime, first_admit_age
    , CASE
        -- all ages > 89 in the database were replaced with 300
        WHEN first_admit_age > 89
            then '>89'
        WHEN first_admit_age >= 14
            THEN 'adult'
        WHEN first_admit_age <= 1
            THEN 'neonate'
        ELSE 'middle'
        END AS age_group
FROM first_admission_time
ORDER BY subject_id
```

--------------------------------

### Calculate Patient Age from ICUSTAYS

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This SQL query retrieves patient subject ID, hospital admission ID, ICUSTAY ID, admission and discharge times, and calculates the patient's age using the difference between the admission time and date of birth. It joins the icustays and patients tables.

```SQL
SELECT ie.subject_id, ie.hadm_id, ie.icustay_id,
    ie.intime, ie.outtime,
    DATETIME_DIFF(admittime, dob, YEAR) AS age
-- we use 'ie' as an alias for the icustays table
FROM `physionet-data.mimiciii_clinical.icustays` ie
-- we use 'pat' as an alias for the patients table
INNER JOIN `physionet-data.mimiciii_clinical.patients` pat
-- since subject_id is unique for every row in patients, we will get
-- one row for every row in icustays (ie)
  ON ie.subject_id = pat.subject_id;
```

--------------------------------

### Count female patients in database

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

Filter patient records to count only female patients. Demonstrates basic WHERE clause usage for conditional data extraction based on categorical values.

```sql
SELECT COUNT(*)
FROM `physionet-data.mimiciii_clinical.patients`
WHERE gender = 'F';
```

--------------------------------

### Analyze patient mortality statistics

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

Examine hospital mortality rates by grouping patients according to expire_flag status. Essential for outcome analysis and understanding death recording methodology in MIMIC-III.

```sql
SELECT expire_flag, COUNT(*)
FROM `physionet-data.mimiciii_clinical.patients`
GROUP BY expire_flag;
```

--------------------------------

### Query Metavision Text Concepts in MIMIC-III d_items Table

Source: https://mimic.mit.edu/docs/iii/about/releasenotes

This SQL query retrieves item details for text data added to chartevents from Metavision sources in MIMIC-III v1.4. It filters d_items table for concepts linked to chartevents with text parameter type and Metavision database source. No inputs required beyond database access; outputs all matching rows including item IDs and descriptions; limited to text pick-list data added in this release.

```sql
select * from d_items  
where linksto = 'chartevents'  
and dbsource = 'metavision'  
and param_type = 'Text'
```

--------------------------------

### Calculate Patient Age at Admission (SQL)

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This SQL query calculates the age of patients at the time of their admission using the DATETIME_DIFF function. It joins the patients and admission tables and computes the age difference between the admission time and the date of birth.

```SQL
SELECT p.subject_id, p.dob, a.hadm_id,
    a.admittime, p.expire_flag,
    DATETIME_DIFF(admittime, dob, YEAR) as age
FROM `physionet-data.mimiciii_clinical.admissions` a
INNER JOIN `physionet-data.mimiciii_clinical.patients` p
  ON p.subject_id = a.subject_id
ORDER BY p.subject_id, a.hadm_id;
```

--------------------------------

### SQL: Retrieve All ICU Transfers Data

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

This query fetches all records from the transfers table to examine patient movements between care units. It includes columns like prev_careunit, curr_careunit, intime, outtime, and los for tracking ICU stays. Useful for understanding hospital transfers; no inputs specified, outputs full table data with potential multiple rows per patient.

```sql
SELECT *
FROM `physionet-data.mimiciii_clinical.transfers`;
```

--------------------------------

### Group patients by gender distribution

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

Aggregate patient counts by gender categories. Shows GROUP BY usage for demographic breakdowns and population statistics across categorical variables.

```sql
SELECT gender, COUNT(*)
FROM `physionet-data.mimiciii_clinical.patients`
GROUP BY gender;
```

--------------------------------

### Extract ICU stays with mortality flags using SQL

Source: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii-bq

Extracts ICU stay records from MIMIC-III by joining icustays, patients, and admissions tables. Calculates patient age, pre-ICU length of stay, and derives age groups and mortality flags using conditional logic. Outputs include subject identifiers, timestamps, age metrics, and hospital/ICU expiration indicators for clinical analysis.

```sql
SELECT ie.subject_id, ie.hadm_id, ie.icustay_id,
    ie.intime, ie.outtime, 
    -- patient death in hospital is stored in the admissions table
    adm.deathtime,
    DATETIME_DIFF(adm.admittime, pat.dob, YEAR) as age,
    DATETIME_DIFF(ie.intime, adm.admittime, DAY) as preiculos,
    CASE
        WHEN DATETIME_DIFF(adm.admittime, pat.dob, YEAR) <= 1
            THEN 'neonate'
        WHEN DATETIME_DIFF(adm.admittime, pat.dob, YEAR) <= 14
            THEN 'middle'
        WHEN DATETIME_DIFF(adm.admittime, pat.dob, YEAR) > 89
            THEN '>89'
        ELSE 'adult'
    END AS ICUSTAY_AGE_GROUP,
    -- the "hospital_expire_flag" field in the admissions table indicates if a patient died in-hospital
    CASE
        WHEN adm.hospital_expire_flag = 1 then 'Y'
    ELSE 'N'
    END AS hospital_expire_flag,
    -- note also that hospital_expire_flag is equivalent to "Is adm.deathtime not null?"
    CASE
        WHEN adm.deathtime BETWEEN ie.intime and ie.outtime
            THEN 'Y'
        -- sometimes there are typographical errors in the death date, so check before intime
        WHEN adm.deathtime <= ie.intime
            THEN 'Y'
        WHEN adm.dischtime <= ie.outtime
            AND adm.discharge_location = 'DEAD/EXPIRED'
            THEN 'Y'
        ELSE 'N'
        END AS ICUSTAY_EXPIRE_FLAG
FROM `physionet-data.mimiciii_clinical.icustays` ie
INNER JOIN `physionet-data.mimiciii_clinical.patients` pat
  ON ie.subject_id = pat.subject_id
INNER JOIN `physionet-data.mimiciii_clinical.admissions` adm
  ON ie.hadm_id = adm.hadm_id;
```