Longitudinal Multi-Timepoint Search

One of the classic use cases is being able to support multiple timepoints. In this example, we will find patients that have the first timepoint as a Chest X-ray scan with a nodule present.

After this timepoint, we want the patient to have had a Chest CT scan and carcinoma positive finding in the radiology note.

CREATE OR REPLACE TABLE `gradient-pacs-siskin-172863.eris_test.longitudinal` AS (
  WITH 
    timepoint_1 AS (
        SELECT 
          Patient.patient_id, 
          Study.row_id, 
          Study.study_date,
          Series.series_uid 
        FROM `gradient-pacs-siskin-172863.eris_test.patients_json` AS Patient, 
        UNNEST(studies) AS Study,
        UNNEST(Study.series) AS Series
        WHERE 
          Study.positive_entities LIKE '%nodule%'
          AND (Series.modality = 'DX' OR Series.modality = 'CR')
          AND LOWER(Study.study_description) LIKE '%chest%'
    ),
    timepoint_2 AS (
        SELECT 
          Patient.patient_id, 
          Study.row_id, 
          Study.study_date,
          Series.series_uid 
        FROM `gradient-pacs-siskin-172863.eris_test.patients_json` AS Patient, 
        UNNEST(studies) AS Study,
        UNNEST(Study.series) AS Series
        WHERE 
          (Study.positive_entities LIKE '%carcinoma%') 
          AND Series.modality = 'CT'
          AND LOWER(Study.study_description) LIKE '%chest%'
    ),
    merged_timepoints AS (
        SELECT
            t1.patient_id,
            t1.row_id AS row_id_1,
            t1.series_uid AS series_uid_1,
            t1.study_date AS study_date_1,
            t2.row_id AS row_id_2,
            t2.series_uid AS series_uid_2,
            t2.study_date AS study_date_2
        FROM timepoint_1 AS t1
        JOIN timepoint_2 AS t2
            ON t1.patient_id = t2.patient_id
            AND DATE_DIFF(t2.study_date, t1.study_date, DAY) BETWEEN 1 AND 90 -- 90 day ordered
            -- AND abs(DATE_DIFF(t2.study_date, t1.study_date, DAY)) BETWEEN 1 AND 90
            -- Note, that there is a difference between absolute date diff and not absolute, 
            -- it is the difference between having timepoint 1 before timepoint 2 and
            -- having timepoints interchangable.
    )

    SELECT
        patient_id,
        ARRAY_AGG(DISTINCT row_id_1) || ARRAY_AGG(DISTINCT row_id_2) AS row_ids,
        ARRAY_AGG(DISTINCT series_uid_1) || ARRAY_AGG(DISTINCT series_uid_2) AS series_uids
    FROM merged_timepoints
    GROUP BY patient_id
    ORDER BY patient_id
)

This query:

1. Defines two timepoints - first a chest X-ray with nodule findings, then a chest CT with carcinoma findings
2. Joins the timepoints on patient_id with a date difference constraint (1-90 days)
3. Returns only patients who match both criteria in the specified order and timeframe