public.stg_weather_tokyo

CREATE VIEW stg_weather_tokyo AS (
 WITH deduplicated AS (
         SELECT raw_weather_tokyo.id,
            raw_weather_tokyo.recorded_at,
            raw_weather_tokyo.temperature_c,
            raw_weather_tokyo.relative_humidity_pct,
            raw_weather_tokyo.precipitation_mm,
            raw_weather_tokyo.shortwave_radiation_wm2,
            raw_weather_tokyo.wind_speed_ms,
            raw_weather_tokyo.wind_direction_deg,
            raw_weather_tokyo.cloud_cover_pct,
            raw_weather_tokyo.pressure_hpa,
            raw_weather_tokyo.loaded_at,
            row_number() OVER (PARTITION BY raw_weather_tokyo.recorded_at ORDER BY raw_weather_tokyo.loaded_at DESC) AS row_num
           FROM raw_weather_tokyo
        )
 SELECT deduplicated.id,
    ((deduplicated.recorded_at AT TIME ZONE 'Asia/Tokyo'::text))::date AS weather_date,
    ((deduplicated.recorded_at AT TIME ZONE 'Asia/Tokyo'::text))::time without time zone AS weather_time,
    deduplicated.temperature_c,
    deduplicated.relative_humidity_pct,
    deduplicated.precipitation_mm,
    deduplicated.shortwave_radiation_wm2,
    deduplicated.wind_speed_ms,
    deduplicated.wind_direction_deg,
    deduplicated.cloud_cover_pct,
    deduplicated.pressure_hpa,
    deduplicated.loaded_at
   FROM deduplicated
  WHERE (deduplicated.row_num = 1)
)