(select HOUR,symbol,PRICE

from ethereum.price.ez_hourly_token_prices

where symbol = 'CRV'

and HOUR >= '2022-04-14')

union all

(select HOUR,symbol,PRICE

from avalanche.price.ez_hourly_token_prices

where symbol in ('UST','USDC.e','USDT.e','WETH.e','WBTC.e','JOE','DAI.e','WAVAX','WXT')

and hour >= '2022-02-14')),

main as (

select

distinct BLOCK_TIMESTAMP,

tx_hash,

DECODED_LOG:reserve as reserve,

case

when DECODED_LOG:reserve in ('0x249848beca43ac405b8102ec90dd5f22ca513c06','0x47536f17f4ff30e64a96a7555826b8f9e66ec468') then 'CRV'

else b.symbol end as symbols,

case

when DECODED_LOG:reserve in ('0xc7198437980c041c805a1edcba50c1ce5db95118','0xa7d7079b0fead91f3e65f86e8915cb59c1a4c664','0xb599c3590f42f8f995ecfa0f85d2980b76862fc1') then DECODED_LOG:amount/pow(10,6)

when DECODED_LOG:reserve in ('0x50b7545627a5162f82a992c33b87adc75187b218') then DECODED_LOG:amount/pow(10,8)

else DECODED_LOG:amount/pow(10,18) end as amount,

case

when symbols in ('USDT.e') and c.price is null then amount

else amount*c.price end as usd_amount,

case

when DECODED_LOG:reserve = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then ORIGIN_FROM_ADDRESS

else DECODED_LOG:user end as user

from avalanche.core.ez_decoded_event_logs

left join avalanche.price.ez_hourly_token_prices b on reserve = TOKEN_ADDRESS

left join prices c on date_trunc('hour',BLOCK_TIMESTAMP) = c.hour and symbols = c.symbol

where CONTRACT_ADDRESS = '0xb9257597eddfa0ecaff04ff216939fbc31aac026'

and origin_to_address in ('0xb9257597eddfa0ecaff04ff216939fbc31aac026','0xcf9477a5e24c0c42122bfa9a06e02ebbb5b2b0fd')

and EVENT_NAME = 'Withdraw'

order by 1 desc)