vault_creation as (select

date_trunc({{scale}},BLOCK_TIMESTAMP)::date as date,

case when tx_hash in (select tx_hash from ethereum.maker.ez_vault_creation) then 'Vault creation'

else 'Re-deposit' end as type,

TOKEN_DEPOSITED,

TX_HASH,

DEPOSITOR,

AMOUNT_DEPOSITED

from

ethereum.maker.ez_deposits

where TOKEN_DEPOSITED = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

),

price as (

select

date_trunc({{scale}},HOUR)::date as datee,avg(PRICE) as price

from ethereum.core.fact_hourly_token_prices

where TOKEN_ADDRESS = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

group by 1

select

count(distinct TX_HASH) as total_tx,

count(distinct DEPOSITOR) as total_depositors,

sum(AMOUNT_DEPOSITED) as native_amount_dep,

sum(price*AMOUNT_DEPOSITED) as dep_value_usd ,

avg(price*AMOUNT_DEPOSITED) as avg_value_usd ,

Median(price*AMOUNT_DEPOSITED) as Median_value_usd ,

total_tx / {{days_back}} as deposits_per_day,

dep_value_usd / {{days_back}} as usd_per_day,

total_depositors / {{days_back}} as depositors_per_day