pressure.sql

with hourly_pressure as (
    select
        t as date_time
        , t::date as date_day
        , hour(t) as date_hour
        , v:main:pressure::int as pressure_mbars
        , pressure_mbars / 1000.0 as pressure_bars
        , v:city.country as country
        , v:city.name as city
        
        , lag(pressure_bars) over (
            partition by country, city
            order by t
          ) as t1h_pressure_bars
        , max(pressure_bars) over (
            partition by country, city
            order by t
            rows between 35 preceding and current row
          ) as t36h_max_pressure_bars
        
        , max(pressure_bars) over (
            partition by country, city
            order by t
            rows between 23 preceding and current row
          ) as t24h_max_pressure_bars
          
        , case
            when t1h_pressure_bars > pressure_bars then -1 
            when t1h_pressure_bars < pressure_bars then 1 
            else 0
          end as pressure_change_direction


    from snowflake_sample_data.weather.weather_14_total
    where t > '2020-08-01'
      and v:city.country = 'US'
      and v:city.name = 'Philadelphia'
)

select
  *
  , pressure_bars - t36h_max_pressure_bars as t36h_pressure_delta
  , pressure_bars - t24h_max_pressure_bars as t24h_pressure_delta
  , avg(t1h_pressure_bars) over (
      partition by country, city
      order by date_time
      rows between 11 preceding and current row
    ) as t12_avg_hourly_pressure_delta
  , sum(case when pressure_change_direction = -1 then 1 else 0 end) over (
      partition by country, city
      order by date_time
      rows between 23 preceding and current row
    ) as last_24h_hours_with_negative_pressure_delta
  
from hourly_pressure
order by 1