Freezer Current Stats (5 minute average)

Custom SQL query returning 1 row (hide)

WITH latest AS (
  SELECT MAX(recorded_at) AS max_recorded_at
  FROM freezer_1
), windowed AS (
  SELECT t.*
  FROM freezer_1 t, latest
  WHERE t.recorded_at >= datetime(latest.max_recorded_at, '-5 minutes')
), latest_row AS (
  SELECT recorded_at, door_status, light_lux
  FROM freezer_1
  ORDER BY recorded_at DESC
  LIMIT 1
)
SELECT
  latest_row.recorded_at AS latest_reading_at,
  ROUND(AVG(windowed.temperature_c), 2) AS avg_temperature_c,
  ROUND(AVG(windowed.humidity_pct), 2) AS avg_humidity_pct,
  ROUND(AVG(windowed.pressure_hpa), 2) AS avg_pressure_hpa,
  latest_row.door_status AS current_door_status,
  latest_row.light_lux AS current_light_lux,
  COUNT(*) AS samples_in_window
FROM windowed, latest_row;

Edit SQL