price as (

select

HOUR::date as day, token_address,avg(PRICE) as price

from ethereum.core.fact_hourly_token_prices

--where TOKEN_ADDRESS = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

group by 1 ,2

),

total_deposit as(

distinct depositor as depositors,

case when symbol is null then 'others' else symbol end as symbol,

sum (AMOUNT_DEPOSITED * price) as total_usd_deposited

from ethereum.maker.ez_deposits a

LEFT outer JOIN price b

on a. token_deposited =b.token_address

and a.BLOCK_TIMESTAMP::date= b.day

-- where TOKEN_DEPOSITED='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --WETH

where BLOCK_TIMESTAMP::date >='2021-10-01'

AND TX_STATUS = 'SUCCESS'

group by 1,2

order by 1

),

total_withdrawal as(

distinct WITHDRAWER withdrawers,

case when symbol is null then 'others' else symbol end as symbol,

sum (AMOUNT_WITHDRAWN* price) as total_usd_withdrawn