select date_day

from ethereum.core.dim_dates

where date_day >= '2022-09-01'),

pricet as (

select hour::date as date_day,

symbol,

token_address,

case when token_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then '0x9f088240b604f2b6edb81e1cc5e1d05af9debde5' -- WAVAX

when token_address = '0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd' then '0xd4e841e89213a323a4c00629bcefd48a04810924' -- JOE

when token_address = '0x8729438eb15e2c8b576fcc6aecda6a148776c0f5' then '0x035b3ae3d79ed759052fa0bea6a6b6e93df74669' -- QI

when token_address = '0xd1c3f94de7e5b45fa4edbba472491a9f4b166fc4' then '0x64a3258fb638d755f45b1891f0b01aca849e9307' -- XAVA

when token_address = '0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be' then '0x8cb90a81f018308230d98ffd48727e0825d4ca1f' -- sAVAX

end as vtoken_address,

decimals,

avg (price) as usdprice

from avalanche.price.ez_prices_hourly

where token_address in ('0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7', -- WAVAX

'0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd', -- JOE

'0x8729438eb15e2c8b576fcc6aecda6a148776c0f5', -- QI

'0xd1c3f94de7e5b45fa4edbba472491a9f4b166fc4', -- XAVA

'0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be') -- sAVAX

group by 1,2,3,4,5),

deposit_updates as (

select t1.tx_hash,

t1.block_timestamp,

t1.contract_address as vtoken_address,

t2.token_address,

t2.symbol,

t2.decimals,

sum (decoded_log:amount + decoded_log:depositedAmount) as amount

from avalanche.core.ez_decoded_event_logs t1 join pricet t2 ON t1.contract_address = t2.vtoken_address

where origin_to_address in ('0xf2e0869b9e96ed25a5eb7e551229f3e50388224c','0xd6dd95610fc3a3579a2c32fe06158d8bfb8f4ee9','0xc3025042e553d1437b771bbb1b633aab8d242164')

and event_name in ('VTokenTransfer','UpdateDeposit')