Daily forecast

1-- core.openweather.daily_forecast
2-- The 3-hourly forecast rolled up to local days per location: the next five days
3-- of weather in the same shape as core.openweather.daily_weather, for staffing
4-- and ordering decisions.
5-- Grain: local date x location (city, country).
6--
7-- Steps are assigned to local dates using the location's UTC offset from the
8-- daily weather stream; locations without daily weather fall back to UTC. The
9-- first and last day are partial (the forecast starts now and ends five days out);
10-- steps_in_day says how many 3-hour steps a day has, 8 being a full day.
11-- Past dates keep the last forecast made before them, which lets you compare
12-- forecast with actual using core.openweather.daily_weather.
13
14WITH offsets AS (
15    SELECT city, country, utc_offset
16    FROM {{staging.openweather.daily_weather}}
17    WHERE utc_offset IS NOT NULL
18    QUALIFY ROW_NUMBER() OVER (PARTITION BY city, country ORDER BY date DESC) = 1
19),
20
21steps AS (
22    SELECT
23        f.*,
24        DATE(f.forecast_at, COALESCE(o.utc_offset, '+00:00')) AS forecast_date
25    FROM {{staging.openweather.forecast}} f
26    LEFT JOIN offsets o USING (city, country)
27)
28
29SELECT
30    forecast_date,
31    city,
32    country,
33    COUNT(*)                                        AS steps_in_day,
34    MIN(temp_min_c)                                 AS temp_min_c,
35    MAX(temp_max_c)                                 AS temp_max_c,
36    ROUND(AVG(feels_like_c), 1)                     AS avg_feels_like_c,
37    ROUND(SUM(rain_mm), 1)                          AS rain_mm,
38    MAX(precipitation_probability)                  AS max_precipitation_probability,
39    MAX(wind_speed_ms)                              AS max_wind_speed_ms,
40    APPROX_TOP_COUNT(weather_main, 1)[OFFSET(0)].value AS most_common_weather,