count(distinct TX_HASH) as "d txCount", count(distinct DEPOSITOR_ADDRESS) as "Depositors" from ethereum.aave.ez_deposits z join ethereum.core.fact_hourly_token_prices x --.fact_hourly_token_prices x

on date_trunc('day', z.block_timestamp) = date_trunc('day', x.hour) where z.symbol = 'WETH' and x.symbol = 'WETH' and day >= '2022-07-01' and day < CURRENT_DATE

group by 1), tb3 as ( select date_trunc('day', block_timestamp) as day, sum(WITHDRAWN_TOKENS) as "eth amount", sum(WITHDRAWN_TOKENS) * avg(price) as "eth amount usd", --avg(price) as "eth amount usd",

count(distinct TX_HASH) as "w txCount", count(distinct DEPOSITOR_ADDRESS) as "Withdrawers" from ethereum.aave.ez_withdraws z join ethereum.core.fact_hourly_token_prices x

on date_trunc('day', z.block_timestamp) = date_trunc('day', x.hour) where z.symbol = 'WETH' and x.symbol = 'WETH' and day >= '2022-07-01' and day < CURRENT_DATE

group by 1), tb2 as (select tb1.day, case WHEN tb1.day >= '2022-09-01' and tb1.day < '2022-09-15' THEN 'Two weeks before the merge' WHEN tb1.day < '2022-09-01' THEN 'Before merge' ELSE 'After merge' end as Period,

tb1."eth amount" AS "Deposit eth amount", tb3."eth amount" AS "Withdraw eth amount", tb1."eth amount" - tb3."eth amount" as "Net eth amount", case when "Net eth amount"<0 then '-' else '+' end as "Net Amount status",

tb3."w txCount" AS "Withdraw TX number", tb1."d txCount" AS "Deposit TX number", tb3."Withdrawers", tb1."Depositors" from tb1 join tb3 on tb1.day = tb3.day order by "Net Amount status" asc )

select * ,sum("Deposit eth amount") over(order by day asc) as "Deposit eth amount commulative" ,sum("Net eth amount") over(order by day asc) as "Net eth commulative"

,sum("Withdraw eth amount") over(order by day asc) as "Withdraw eth amount commulative" ,(select sum("Withdraw eth amount") from tb2 where Period = 'Two weeks before the merge' ) as "Withdraw eth amount TWoweeks"

,(select sum("Deposit eth amount") from tb2 where Period = 'Two weeks before the merge' ) as "Deposit eth amount TWoweeks" -- "Deposit eth amount TWoweeks"

,((select sum("Withdraw eth amount") from tb2 where Period = 'Two weeks before the merge' ) /(select sum("Deposit eth amount") from tb2 where Period = 'Two weeks before the merge' )) as "w/d"

from tb2 order by day asc --from tb2 order by day asc