Index Date query with diagnosis condition

index-date-condition-query.sql

WITH person_date AS (
    SELECT
        person_id,
        MIN(condition_start_date) AS index_date
    FROM
        ? schema.condition_occurrence
    WHERE (condition_concept_id IN (1, 2, 3))
GROUP BY
    person_id
HAVING
    COUNT(*) >= 1
),
diagnoses_around_ix_date AS (
    SELECT
        person_id,
        ARRAY_AGG(condition_concept_id) FILTER (WHERE condition_start_date <= index_date) AS conditions_before_and_on_ix,
        ARRAY_AGG(condition_concept_id) FILTER (WHERE condition_start_date BETWEEN index_date - INTERVAL '-150 days' AND index_date + INTERVAL '150 days') AS conditions_minus_5_months_before_plus_5_months_after_ix
    FROM
        ? schema.condition_occurrence
        INNER JOIN person_date USING (person_id)
    GROUP BY
        person_id
)
SELECT
    1,
    person_id
FROM
    ? schema.person
    INNER JOIN person_date USING (person_id)
    INNER JOIN diagnoses_around_ix_date USING (person_id)
WHERE (((conditions_before_and_on_ix && ARRAY[1, 2])
        AND array_length(ARRAY (
                SELECT
                    unnest(conditions_minus_5_months_before_plus_5_months_after_ix)
                INTERSECT
                SELECT
                    unnest(ARRAY[2, 3])), 1) >= 2))