participant_cgm_date_range_view

Column	Type
tenant_id	VARCHAR
study_id	TEXT
participant_id	TEXT
participant_cgm_start_date	TEXT
participant_cgm_end_date	TEXT
end_date_minus_1_day	TEXT
end_date_minus_7_days	TEXT
end_date_minus_14_days	TEXT
end_date_minus_30_days	TEXT
end_date_minus_90_days	TEXT

SQL DDL

CREATE VIEW participant_cgm_date_range_view AS
SELECT
    (
        select
            party_id
        from
            party
        limit
            1
    ) as tenant_id,
    (
        select
            study_id
        from
            uniform_resource_study
        limit
            1
    ) as study_id,
    participant_id,
    CAST(strftime ('%Y-%m-%d', MIN(Date_Time)) AS TEXT) AS participant_cgm_start_date,
    CAST(strftime ('%Y-%m-%d', MAX(Date_Time)) AS TEXT) AS participant_cgm_end_date,
    CAST(
        strftime ('%Y-%m-%d', DATE (MAX(Date_Time), '-1 day')) AS TEXT
    ) AS end_date_minus_1_day,
    CAST(
        strftime ('%Y-%m-%d', DATE (MAX(Date_Time), '-7 day')) AS TEXT
    ) AS end_date_minus_7_days,
    CAST(
        strftime ('%Y-%m-%d', DATE (MAX(Date_Time), '-14 day')) AS TEXT
    ) AS end_date_minus_14_days,
    CAST(
        strftime ('%Y-%m-%d', DATE (MAX(Date_Time), '-30 day')) AS TEXT
    ) AS end_date_minus_30_days,
    CAST(
        strftime ('%Y-%m-%d', DATE (MAX(Date_Time), '-90 day')) AS TEXT
    ) AS end_date_minus_90_days
FROM
    combined_cgm_tracing
GROUP BY
    participant_id