Fridge Temperature Rising

Custom SQL query returning 615 rows (hide)

WITH buckets AS (
  SELECT
    strftime('%Y-%m-%d %H:', recorded_at) || printf('%02d', (CAST(strftime('%M', recorded_at) AS INTEGER) / 5) * 5) AS window_start,
    AVG(temperature_c) AS avg_temperature_c
  FROM fridge_1
  GROUP BY 1
), trends AS (
  SELECT
    window_start,
    avg_temperature_c,
    LAG(avg_temperature_c, 1) OVER (ORDER BY window_start) AS prev_avg_temperature_c,
    LAG(avg_temperature_c, 2) OVER (ORDER BY window_start) AS prev2_avg_temperature_c
  FROM buckets
)
SELECT
  window_start,
  ROUND(avg_temperature_c, 2) AS avg_temperature_c,
  ROUND(avg_temperature_c - prev_avg_temperature_c, 2) AS delta_from_previous_c
FROM trends
WHERE prev2_avg_temperature_c IS NOT NULL
  AND avg_temperature_c > prev_avg_temperature_c
  AND prev_avg_temperature_c > prev2_avg_temperature_c
ORDER BY window_start DESC;

Edit SQL