count(DISTINCT(depositor_address)) as users , sum(SUPPLIED_USD) as volume,

avg(SUPPLIED_USD) as avg_volume, max(SUPPLIED_USD) as max_volume, median(SUPPLIED_USD) as median_volume

from ethereum.aave.ez_deposits

where block_timestamp::date >= current_date - {{N_Days}}

and symbol is not null

and symbol = 'AAVE'

group by 1,2,3)

,

withdraw as ( select date(block_timestamp) as withdraw_date, 'Withdraw' as type,depositor_address, count(DISTINCT(tx_hash)) as tx,

count(DISTINCT(depositor_address)) as users , sum(WITHDRAWN_USD) as volume,

avg(WITHDRAWN_USD) as avg_volume, max(WITHDRAWN_USD) as max_volume, median(WITHDRAWN_USD) as median_volume

from ethereum.aave.ez_withdraws

where block_timestamp::date >= current_date - {{N_Days}}

and symbol is not null

and symbol = 'AAVE'

group by 1,2,3)

,

final as ( select a.depositor_address, deposit_date, withdraw_date, a.volume as deposit_volume, b.volume as withdraw_volume

from deposit a join withdraw b on a.depositor_address = b.depositor_address

where b.volume <= a.volume)

,

final_2 as ( select datediff('day',deposit_date,withdraw_date) as days, depositor_address

from final)

select avg(days) as avg_holding

from final_2

where days >= 0