0xaimanAverage ETH Balance over the Past Week
Updated 2022-06-24
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
›
⌄
select eth_avg_bal, count(user_address) as n_unique_address
from (with mm as (select from_address, min(block_timestamp) as mt from ethereum.transactions
where to_address=lower('0x881D40237659C251811CEC9c364ef91dC08D300C') and tx_fee>0
group by 1 order by 2),
balance as (select balance_date, a.user_address, balance from ethereum.erc20_balances a inner join mm on a.user_address=mm.from_address where contract_address='ETH')
select date_trunc('week', balance_date) as week, user_address, avg(balance) as avg_bal, case when avg_bal=0 then '0 ETH'
when avg_bal>0 and avg_bal<=1 then ' Between 0 and 1 ETH'
when avg_bal>1 and avg_bal<=10 then ' Between 1 and 10 ETH'
else ' more than 10 ETH' end as ETH_avg_bal
from balance
group by 1,2 order by 1)
where week>'2022-06-11'
group by 1
limit 100
Run a query to Download Data