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(

a.block_timestamp::date as day,

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

count (tx_hash) as tx_count,

count (distinct depositor) as depositors,

sum (AMOUNT_DEPOSITED * price) as total_usd,

sum (total_usd) over (order by a.block_timestamp::date) as cum_usd,

sum (depositors) over (order by a.block_timestamp::date) as cum_dep,

sum (tx_count) over (order by a.block_timestamp::date) as cum_tx_count

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 TOKEN_DEPOSITED='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

AND TX_STATUS = 'SUCCESS'

group by 1,2

order by 1

),

total_withdrawal as(