Daily weather

1-- core.openweather.daily_weather
2-- Daily weather per location, shaped for joining to sales, covers or footfall on
3-- date: temperatures, rain, wind, and a few plain-language flags.
4-- Grain: date x location (city, country).
5--
6-- The flags use fixed thresholds you may want to tune for your climate:
7--   is_wet_day      at least 1 mm of precipitation
8--   is_heavy_rain   at least 10 mm
9--   temp_band       on the daily maximum: below 5, 5-15, 15-25, 25 and above (°C)
10-- temp_max_vs_7d_avg is the day's maximum against the average maximum of the
11-- seven days before it at the same location: "warmer than last week" in degrees.
12
13SELECT
14    date,
15    city,
16    country,
17    FORMAT_DATE('%A', date)                         AS weekday,
18    temp_min_c,
19    temp_max_c,
20    temp_afternoon_c,
21    temp_evening_c,
22    precipitation_mm,
23    precipitation_mm >= 1                           AS is_wet_day,
24    precipitation_mm >= 10                          AS is_heavy_rain,
25    CASE
26        WHEN temp_max_c < 5  THEN 'below_5c'
27        WHEN temp_max_c < 15 THEN '5_15c'
28        WHEN temp_max_c < 25 THEN '15_25c'
29        ELSE '25c_and_above'
30    END                                             AS temp_band,
31    humidity_afternoon_pct,
32    cloud_cover_afternoon_pct,
33    wind_max_speed_ms,
34    ROUND(temp_max_c - AVG(temp_max_c) OVER (
35        PARTITION BY city, country
36        ORDER BY UNIX_DATE(date)
37        RANGE BETWEEN 7 PRECEDING AND 1 PRECEDING
38    ), 1)                                           AS temp_max_vs_7d_avg
39FROM {{staging.openweather.daily_weather}}
40WHERE date IS NOT NULL