with priceTb as (
select
HOUR::date as p_date,
avg(PRICE) as usd_price
from ethereum.core.fact_hourly_token_prices
where SYMBOL = 'WAVAX'
AND HOUR::date > CURRENT_DATE - 90
AND HOUR::date <= CURRENT_DATE - 1
group by 1
)
, osmoTb as (
SELECT
BLOCK_TIMESTAMP::date as date,
tt1.PROJECT_NAME || ' => ' || tt2.PROJECT_NAME as pair,
COUNT(DISTINCT tx_id) as swap_tx,
COUNT(DISTINCT TRADER) as swapper,
coalesce(sum(case when FROM_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (FROM_AMOUNT/pow(10,FROM_DECIMAL))*usd_price end)*-1 ,0) sell_vol,
coalesce(sum(case when TO_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (TO_AMOUNT/pow(10,TO_DECIMAL))*usd_price end ),0) buy_vol,
buy_vol + sell_vol as net_swap_vol,
round(buy_vol - sell_vol, 2) as total_swap_vol,
sum(swap_tx) over (order by date) as cume_swap_tx,
sum(buy_vol) over (order by date) as cume_buy_vol,
sum(sell_vol) over (order by date) as cume_sell_vol,
sum(net_swap_vol) over (order by date) as cume_net_vol,
sum(total_swap_vol) over (order by date) as cume_total_vol
FROM osmosis.core.fact_swaps ts LEFT JOIN osmosis.core.dim_tokens tt1 on ts.FROM_CURRENCY = tt1.address
LEFT JOIN osmosis.core.dim_tokens tt2 on ts.TO_CURRENCY = tt2.address
join priceTb p ON ts.BLOCK_TIMESTAMP::date = p.p_date
WHERE (FROM_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373'
OR TO_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373')
AND TX_SUCCEEDED = TRUE
GROUP by 1,2
ORDER by 1
)
, avaxTb as (
select tx_hash