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 - 60

AND HOUR::date <= CURRENT_DATE - 1

group by 1

)

, osmoTb as (

SELECT

BLOCK_TIMESTAMP::date as date,

tx_id,

TRADER,

coalesce(avg(case

when TO_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (TO_AMOUNT/pow(10,TO_DECIMAL))*usd_price

when FROM_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (FROM_AMOUNT/pow(10,FROM_DECIMAL))*usd_price

end),0) avg_swap_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,3

ORDER by 1

)

, avaxTb as (

select tx_hash

from avalanche.core.fact_event_logs

where event_name = 'Swap'

and block_timestamp::date >= '2023-01-21'

)

, avalancheTb as (

select