Discussion on: #SQL30 Day 3: Weather Data

WITH monthly AS (
  SELECT c.ogc_fid, date_trunc('month', m.taken) as month,
    AVG(m.value) / 10 AS temp_mean,
    MIN(m.value) / 10 AS temp_min,
    MAX(m.value) / 10 AS temp_max
  FROM county c
    JOIN station s ON ST_Contains(c.wkb_geometry, ST_SETSRID(ST_MakePoint(s.lat, s.long), 4326))
    JOIN measurement m ON s.idx = m.idx
  WHERE c.geo_id = '0500000US01003'  -- running for all counties is slow, this is good for testing
  GROUP BY 1, 2
)
SELECT c.geo_id, c.state, c.county, c.name,
  m.month::DATE,
  round(m.temp_mean::numeric, 2) AS month_temp_mean,
  MIN(m.temp_min) OVER year AS year_min_temp,
  MAX(m.temp_max) OVER year AS year_max_temp
FROM county c
  JOIN monthly m USING (ogc_fid)
WINDOW year AS (PARTITION BY c.geo_id, date_trunc('year', m.month));