hessTotal and Cumulative Hourly Withdraw ( A day Before Merge Until a day after Merge)
Updated 2022-09-17
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
›
⌄
with aave as ( select trunc(block_timestamp,'hour') as date, 'AAVE' as plt, count(DISTINCT(DEPOSITOR_ADDRESS)) as users,
count(DISTINCT(tx_hash)) as total_tx, sum(WITHDRAWN_TOKENS) as amounts, sum(amounts) over (order by date asc) as cum_amount,
avg(WITHDRAWN_TOKENS) as avg_amount, max(WITHDRAWN_TOKENS) as max, min(WITHDRAWN_TOKENS) as min
from ethereum.aave.ez_withdraws
where symbol in ('WETH','ETH') and date >= CURRENT_DATE - 30
group by 1)
,
uni as ( select trunc(block_timestamp,'hour') as date, LIQUIDITY_PROVIDER, tx_hash, AMOUNT0_ADJUSTED as amount
from ethereum.uniswapv3.ez_lp_actions
where ACTION = 'DECREASE_LIQUIDITY' and TOKEN0_SYMBOL = 'WETH'
UNION
select trunc(block_timestamp,'hour') as date, LIQUIDITY_PROVIDER, tx_hash, AMOUNT1_ADJUSTED as amount
from ethereum.uniswapv3.ez_lp_actions
where ACTION = 'DECREASE_LIQUIDITY' and TOKEN1_SYMBOL = 'WETH' )
,
uniswap as ( select date, 'Uniswap' as type, count(DISTINCT(LIQUIDITY_PROVIDER)) as users, count(DISTINCT(tx_hash)) as total_tx,
sum(amount) as amounts, sum(amounts) over (order by date asc) as cum_amount,
avg(amount) as avg_amount, max(amount) as max, min(amount) as min
from uni
where date >= CURRENT_DATE - 30
group by 1)
,
maker as ( select date(block_timestamp) as date, 'Maker' as type, count(DISTINCT(withdrawer)) as users, count(DISTINCT(tx_hash)) as total_tx,
sum(AMOUNT_WITHDRAWN) as amounts, sum(amounts) over (order by date asc) as cum_amount,
avg(AMOUNT_WITHDRAWN) as avg_amount, max(AMOUNT_WITHDRAWN) as max, min(AMOUNT_WITHDRAWN) as min
from ethereum.maker.ez_withdrawals
where symbol = 'WETH' and date >= CURRENT_DATE - 30
group by 1)
select *
from uni
where date >= '2022-09-14' and date <= '2022-09-16'
Run a query to Download Data