MLDZMNprice.2
    Updated 2022-11-23
    with tb1 as (select
    HOUR::date as day,
    avg(price) as ETH_price,
    lag(ETH_price) ignore nulls over(ORDER BY day ASC) as lag_price,
    ((ETH_price-lag_price)/ETH_price)*100 as deviation_price_ETH,
    ((first_value(ETH_price) over ( order by day asc) -
    first_value(ETH_price) over (order by day desc)
    )/first_value(ETH_price) over ( order by day asc))*100 as diff_ETH
    from ethereum.core.fact_hourly_token_prices
    where hour>= '2022-11-01'
    and symbol in ('WETH')
    group by 1),

    tb2 as (select
    HOUR::date as day,
    avg(price) as BTC_price,
    lag(BTC_price) ignore nulls over(ORDER BY day ASC) as lag_price,
    ((BTC_price-lag_price)/BTC_price)*100 as deviation_price_BTC,
    ((first_value(BTC_price) over ( order by day asc) -
    first_value(BTC_price) over (order by day desc)
    )/first_value(BTC_price) over ( order by day asc))*100 as diff_BTC
    from ethereum.core.fact_hourly_token_prices
    where hour>= '2022-11-01'
    and symbol in ('WBTC')
    group by 1),

    tb3 as (select
    recorded_hour::date as day,
    avg (close) as SOL_price,
    lag(SOL_price) ignore nulls over(ORDER BY day ASC) as lag_price,
    ((SOL_price-lag_price)/SOL_price)*100 as deviation_price_SOL,
    ((first_value(SOL_price) over ( order by day asc) -
    first_value(SOL_price) over (order by day desc)
    )/first_value(SOL_price) over ( order by day asc))*100 as diff_SOL
    from solana.core.fact_token_prices_hourly
    where symbol='SOL'