FreeStyle Libre - Query to Exported CSV

glucose_after_meal.sql

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;