-- not including shell
-- not including breadcrumbs from sqlpage_aide_navigation
-- not including page title from sqlpage_aide_navigation
SELECT 'json' AS component,
JSON_OBJECT(
'ambulatoryGlucoseProfile', (
WITH glucose_data AS (
SELECT
participant_id,
strftime('%H', Date_Time) AS hourValue,
CGM_Value AS glucose_level
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND Date_Time BETWEEN $start_date AND $end_date
),
percentiles AS (
SELECT
participant_id,
hourValue AS hour,
MAX(CASE WHEN row_num = CAST(0.05 * total_count AS INT) THEN glucose_level END) AS p5,
MAX(CASE WHEN row_num = CAST(0.25 * total_count AS INT) THEN glucose_level END) AS p25,
MAX(CASE WHEN row_num = CAST(0.50 * total_count AS INT) THEN glucose_level END) AS p50,
MAX(CASE WHEN row_num = CAST(0.75 * total_count AS INT) THEN glucose_level END) AS p75,
MAX(CASE WHEN row_num = CAST(0.95 * total_count AS INT) THEN glucose_level END) AS p95
FROM (
SELECT
participant_id,
hourValue,
glucose_level,
ROW_NUMBER() OVER (PARTITION BY participant_id, hourValue ORDER BY glucose_level) AS row_num,
COUNT(*) OVER (PARTITION BY participant_id, hourValue) AS total_count
FROM
glucose_data
) ranked_data
GROUP BY
participant_id, hourValue
)
SELECT JSON_GROUP_ARRAY(
JSON_OBJECT(
'participant_id', participant_id,
'hour', hour,
'p5', COALESCE(p5, 0),
'p25', COALESCE(p25, 0),
'p50', COALESCE(p50, 0),
'p75', COALESCE(p75, 0),
'p95', COALESCE(p95, 0)
)
) AS result
FROM
percentiles
GROUP BY
participant_id
)
) AS contents;