-- not including shell
-- not including breadcrumbs from sqlpage_aide_navigation
-- not including page title from sqlpage_aide_navigation
SELECT
'html' as component;
SELECT
'<div class="px-4">' as html;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Liability Index <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| ROUND(CAST((SUM(CASE WHEN CGM_Value < 70 THEN 1 ELSE 0 END) + SUM(CASE WHEN CGM_Value > 180 THEN 1 ELSE 0 END)) AS REAL) / COUNT(*), 2) ||' mg/dL<formula-component content="The Liability Index quantifies the risk associated with glucose variability, measured in mg/dL."></formula-component></div></div></div>
<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Hypoglycemic Episodes <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| SUM(CASE WHEN CGM_Value < 70 THEN 1 ELSE 0 END) ||'<formula-component content="This metric counts the number of occurrences when glucose levels drop below a specified hypoglycemic threshold, indicating potentially dangerous low blood sugar events."></formula-component></div></div></div>
<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Euglycemic Episodes <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| SUM(CASE WHEN CGM_Value BETWEEN 70 AND 180 THEN 1 ELSE 0 END) ||'<formula-component content="This metric counts the number of instances where glucose levels remain within the target range, indicating stable and healthy glucose control."></formula-component></div></div></div>
<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Hyperglycemic Episodes <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| SUM(CASE WHEN CGM_Value > 180 THEN 1 ELSE 0 END) ||'<formula-component content="This metric counts the number of instances where glucose levels exceed a certain hyperglycemic threshold, indicating potentially harmful high blood sugar events."></formula-component></div></div></div>' as html
FROM combined_cgm_tracing
WHERE participant_id = $participant_id AND Date(Date_Time) BETWEEN $start_date AND $end_date
GROUP BY participant_id;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">M Value <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| round((MAX(CGM_Value) - MIN(CGM_Value)) /
((strftime('%s', MAX(DATETIME(Date_Time))) - strftime('%s', MIN(DATETIME(Date_Time)))) / 60.0),3) ||' mg/dL<formula-component content="The M Value provides a measure of glucose variability, calculated from the mean of the absolute differences between consecutive CGM values over a specified period."></formula-component></div></div></div>' as html
FROM combined_cgm_tracing
WHERE participant_id = $participant_id AND Date(Date_Time) BETWEEN $start_date AND $end_date
GROUP BY participant_id;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Mean Amplitude <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| round(AVG(amplitude),3) ||'<formula-component content="Mean Amplitude quantifies the average degree of fluctuation in glucose levels over a given time frame, giving insight into glucose stability."></formula-component></div></div></div>' as html
FROM (SELECT ABS(MAX(CGM_Value) - MIN(CGM_Value)) AS amplitude
FROM combined_cgm_tracing WHERE participant_id = $participant_id AND Date(Date_Time) BETWEEN $start_date AND $end_date
GROUP BY DATE(Date_Time)
);
CREATE TEMPORARY TABLE DailyRisk AS
SELECT
participant_id,
DATE(date_time) AS day,
MAX(CGM_Value) - MIN(CGM_Value) AS daily_range
FROM
combined_cgm_tracing cct
WHERE
participant_id = $participant_id
AND DATE(date_time) BETWEEN DATE($start_date) AND DATE($end_date)
GROUP BY
participant_id,
DATE(date_time);
CREATE TEMPORARY TABLE AverageDailyRisk AS
SELECT
participant_id,
AVG(daily_range) AS average_daily_risk
FROM
DailyRisk
WHERE
participant_id = $participant_id
GROUP BY
participant_id;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Average Daily Risk Range <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| round(average_daily_risk,3) ||' mg/dL<formula-component content="This metric assesses the average risk associated with daily glucose variations, expressed in mg/dL."></formula-component></div></div></div>' as html
FROM
AverageDailyRisk
WHERE
participant_id = $participant_id;
DROP TABLE IF EXISTS DailyRisk;
DROP TABLE IF EXISTS AverageDailyRisk;
CREATE TEMPORARY TABLE glucose_stats AS
SELECT
participant_id,
AVG(CGM_Value) AS mean_glucose,
(AVG(CGM_Value * CGM_Value) - AVG(CGM_Value) * AVG(CGM_Value)) AS variance_glucose
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND DATE(Date_Time) BETWEEN DATE($start_date) AND DATE($end_date)
GROUP BY
participant_id;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">J Index <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| ROUND(0.001 * (mean_glucose + SQRT(variance_glucose)) * (mean_glucose + SQRT(variance_glucose)), 2) ||' mg/dL<formula-component content="The J Index calculates glycemic variability using both high and low glucose readings, offering a comprehensive view of glucose fluctuations."></formula-component></div></div></div>' as html
FROM
glucose_stats;
DROP TABLE IF EXISTS glucose_stats;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Low Blood Glucose Index <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| ROUND(SUM(CASE WHEN (CGM_Value - 2.5) / 2.5 > 0
THEN ((CGM_Value - 2.5) / 2.5) * ((CGM_Value - 2.5) / 2.5)
ELSE 0
END) * 5, 2) ||'<formula-component content="This metric quantifies the risk associated with low blood glucose levels over a specified period, measured in mg/dL."></formula-component></div></div></div>
<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">High Blood Glucose Index <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| ROUND(SUM(CASE WHEN (CGM_Value - 9.5) / 9.5 > 0
THEN ((CGM_Value - 9.5) / 9.5) * ((CGM_Value - 9.5) / 9.5)
ELSE 0
END) * 5, 2) ||'<formula-component content="This metric quantifies the risk associated with high blood glucose levels over a specified period, measured in mg/dL."></formula-component></div></div></div>' as html
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND DATE(Date_Time) BETWEEN $start_date AND $end_date;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Glycaemic Risk Assessment Diabetes Equation (GRADE) <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| round(AVG(CASE
WHEN CGM_Value < 90 THEN 10 * (5 - (CGM_Value / 18.0)) * (5 - (CGM_Value / 18.0))
WHEN CGM_Value > 180 THEN 10 * ((CGM_Value / 18.0) - 10) * ((CGM_Value / 18.0) - 10)
ELSE 0
END),3) ||'<formula-component content="GRADE is a metric that combines various glucose metrics to assess overall glycemic risk in individuals with diabetes, calculated using multiple input parameters."></formula-component></div></div></div>' as html
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND DATE(Date_Time) BETWEEN $start_date AND $end_date;
CREATE TEMPORARY TABLE lag_values AS
SELECT
participant_id,
Date_Time,
CGM_Value,
LAG(CGM_Value) OVER (PARTITION BY participant_id ORDER BY Date_Time) AS lag_CGM_Value
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND DATE(Date_Time) BETWEEN $start_date AND $end_date;
CREATE TEMPORARY TABLE conga_hourly AS
SELECT
participant_id,
SQRT(
AVG(
(CGM_Value - lag_CGM_Value) * (CGM_Value - lag_CGM_Value)
) OVER (PARTITION BY participant_id ORDER BY Date_Time)
) AS conga_hourly
FROM
lag_values
WHERE
lag_CGM_Value IS NOT NULL;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Continuous Overall Net Glycemic Action (CONGA) <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| round(AVG(conga_hourly),3) ||'<formula-component content="CONGA quantifies the net glycemic effect over time by evaluating the differences between CGM values at specified intervals."></formula-component></div></div></div>' as html
FROM
conga_hourly;
DROP TABLE IF EXISTS lag_values;
DROP TABLE IF EXISTS conga_hourly;
SELECT
'<div class="card-content my-3 border-bottom" style="display: flex; flex-direction: row; justify-content: space-between;">Mean of Daily Differences <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;">'|| round(AVG(daily_diff),3) ||'<formula-component content="This metric calculates the average of the absolute differences between daily CGM readings, giving insight into daily glucose variability."></formula-component></div></div></div>' as html
FROM (
SELECT
participant_id,
CGM_Value - LAG(CGM_Value) OVER (PARTITION BY participant_id ORDER BY DATE(Date_Time)) AS daily_diff
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND DATE(Date_Time) BETWEEN $start_date AND $end_date
) AS daily_diffs
WHERE
daily_diff IS NOT NULL;
SELECT
'</div>' as html;