Dashboard Summary

Custom SQL query returning 1 row (hide)

WITH latest AS (
  SELECT MAX(recorded_at) AS max_recorded_at
  FROM sensor_log
), windowed AS (
  SELECT t.*
  FROM sensor_log t, latest
  WHERE t.recorded_at >= datetime(latest.max_recorded_at, '-5 minutes')
), latest_row AS (
  SELECT recorded_at, door_status, light_level
  FROM sensor_log
  ORDER BY recorded_at DESC
  LIMIT 1
), rising AS (
  SELECT COUNT(*) AS rising_windows
  FROM (
    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 sensor_log
      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 1
    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
  )
)
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_level AS current_light_level,
  CASE WHEN rising.rising_windows > 0 THEN 'CHECK' ELSE 'OK' END AS fridge_temp_status
FROM windowed, latest_row, rising;

Edit SQL