HOUR::date as date_n,

avg(PRICE) as usd_price

from ethereum.core.fact_hourly_token_prices

where SYMBOL = 'WAVAX'

AND HOUR::date > CURRENT_DATE - 60

AND HOUR::date <= CURRENT_DATE - 1

group by 1

)

, osmoTab as (

SELECT

BLOCK_TIMESTAMP::date as date,

COUNT(DISTINCT TRADER) as swapper,

COUNT(DISTINCT tx_id) as swap_tx,

coalesce(sum(case when TO_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (TO_AMOUNT/pow(10,TO_DECIMAL))*usd_price end ),0) buy_vol,

coalesce(sum(case when FROM_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (FROM_AMOUNT/pow(10,FROM_DECIMAL))*usd_price end)*-1 ,0) sell_vol,

buy_vol + sell_vol as net_swap_vol,

buy_vol - sell_vol as tot_swap_vol,

sum(buy_vol) over (order by date) as cum_buy_vol,

sum(swap_tx) over (order by date) as cum_swap_tx,

sum(sell_vol) over (order by date) as cum_sell_vol,

sum(net_swap_vol) over (order by date) as cum_net_vol,

sum(tot_swap_vol) over (order by date) as cum_tot_vol

FROM osmosis.core.fact_swaps ts LEFT JOIN osmosis.core.dim_tokens X on ts.FROM_CURRENCY = X.address

LEFT JOIN osmosis.core.dim_tokens Y on ts.TO_CURRENCY = Y.address

join priceTab p ON ts.BLOCK_TIMESTAMP::date = p.date_n

WHERE (FROM_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373'

OR TO_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373')

AND TX_SUCCEEDED = TRUE

GROUP by 1

ORDER by 1

)

, avaxTab as (

select tx_hash

from avalanche.core.fact_event_logs