Skip to content

Instantly share code, notes, and snippets.

@shio-yaamaa
Last active May 16, 2021 08:23
Show Gist options
  • Save shio-yaamaa/180003f45711b3e51a5f75ae366f9130 to your computer and use it in GitHub Desktop.
Save shio-yaamaa/180003f45711b3e51a5f75ae366f9130 to your computer and use it in GitHub Desktop.
FreeStyle Libre - Query to Exported CSV
WITH
meal AS (
SELECT
Device_Timestamp AS timestamp,
Notes AS meal
FROM
`<dataset>.raw`
WHERE
Record_Type = 6
AND Notes IS NOT NULL ),
glucose AS (
SELECT
Device_Timestamp AS timestamp,
Historic_Glucose_mg_dL AS glucose
FROM
`<dataset>.raw`
WHERE
Record_Type = 0 ),
# CROSS JOIN meal and glucose
joined AS (
SELECT
meal.timestamp AS meal_timestamp,
meal.meal AS meal,
glucose.timestamp AS glucose_timestamp,
glucose.glucose AS glucose,
ABS(TIMESTAMP_DIFF(meal.timestamp, glucose.timestamp, MINUTE)) AS plus_0hrs_difference,
ABS(TIMESTAMP_DIFF(TIMESTAMP_ADD(meal.timestamp, INTERVAL 2 HOUR), glucose.timestamp, MINUTE)) AS plus_2hrs_difference,
ABS(TIMESTAMP_DIFF(TIMESTAMP_ADD(meal.timestamp, INTERVAL 3 HOUR), glucose.timestamp, MINUTE)) AS plus_3hrs_difference,
(TIMESTAMP_DIFF(glucose.timestamp, meal.timestamp, MINUTE) BETWEEN 0
AND 120) AS is_in_2hrs
FROM
meal
CROSS JOIN
glucose ),
# Subqueries for "+0hrs"
ranked_plus_0hrs AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
ROW_NUMBER() OVER(PARTITION BY meal_timestamp ORDER BY plus_0hrs_difference ASC) AS ranked_by_0hrs_difference
FROM
joined ),
plus_0hrs AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
FROM
ranked_plus_0hrs
WHERE
ranked_by_0hrs_difference = 1 ),
# Subqueries for "+2hrs"
ranked_plus_2hrs AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
ROW_NUMBER() OVER(PARTITION BY meal_timestamp ORDER BY plus_2hrs_difference ASC) AS ranked_by_2hrs_difference
FROM
joined ),
plus_2hrs AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
FROM
ranked_plus_2hrs
WHERE
ranked_by_2hrs_difference = 1 ),
# Subqueries for "+3hrs"
ranked_plus_3hrs AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
ROW_NUMBER() OVER(PARTITION BY meal_timestamp ORDER BY plus_3hrs_difference ASC) AS ranked_by_3hrs_difference
FROM
joined ),
plus_3hrs AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
FROM
ranked_plus_3hrs
WHERE
ranked_by_3hrs_difference = 1 ),
# Subqueries for "peak"
in_2hrs AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
FROM
joined
WHERE
is_in_2hrs IS TRUE ),
ranked_in_2hrs AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
ROW_NUMBER() OVER(PARTITION BY meal_timestamp ORDER BY glucose DESC) AS ranked_by_glucose
FROM
in_2hrs ),
peak AS (
SELECT
meal_timestamp,
meal,
glucose_timestamp,
glucose,
FROM
ranked_in_2hrs
WHERE
ranked_by_glucose = 1 )
SELECT
# Common field
plus_0hrs.meal AS meal,
# Fields for each metric
plus_0hrs.glucose AS plus_0hrs,
peak.glucose AS peak,
plus_2hrs.glucose AS plus_2hrs,
plus_3hrs.glucose AS plus_3hrs
FROM
plus_0hrs
INNER JOIN
plus_2hrs
USING
(meal_timestamp)
INNER JOIN
plus_3hrs
USING
(meal_timestamp)
INNER JOIN
peak
USING
(meal_timestamp)
ORDER BY
peak DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment