binhachonCopy of Top TOKE Holders - #2
Updated 2022-06-20
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
33
34
35
36
›
⌄
with top100 as (
select
user_address,
label_type,
balance as jan_balance,
amount_usd as jan_balance_usd
from flipside_prod_db.ethereum.erc20_balances
where contract_address = lower('0x2e9d63788249371f1DFC918a52f8d799F4a38C94')
and balance_date = '2022-01-01'
order by balance desc
limit 100
),
current_balance as (
select
*
from flipside_prod_db.ethereum.erc20_balances
where contract_address = lower('0x2e9d63788249371f1DFC918a52f8d799F4a38C94')
and balance_date in (select max(balance_date) from flipside_prod_db.ethereum.erc20_balances)
and user_address in (select user_address from top100)
),
user_stats as (
select
top100.user_address,
top100.label_type,
jan_balance,
coalesce(balance, 0) as current_balance,
case when jan_balance > current_balance then current_balance / jan_balance * 100 else 100 end as perc_hold,
case when jan_balance > current_balance then 'Not keep' else 'Keep' end as position_status,
coalesce(amount_usd, 0) as current_balance_usd
from top100
left join current_balance on (top100.user_address = current_balance.user_address)
)
select
position_status,
count(*) as number_of_users,
avg(perc_hold) as avg_perc_hold
Run a query to Download Data