SocioCryptohistogram
Updated 2022-06-20Copy Reference Fork
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 bins as (
select
floor(diff_pcnt/10.00)*10 as bin_floor,
count(user_address) as count
from
(
SELECT user_address,
100*(avg_past_31_days-avg_jan)/avg_jan as diff_pcnt,
ntile(100/{{percent}}) over (order by avg_jan DESC) as ntile_percent
FROM
(
SELECT user_address,
avg(CASE WHEN balance_date between '2022-01-01' AND '2022-01-31' THEN balance end) as avg_jan,
avg(CASE WHEN balance_date >=CURRENT_DATE-31 THEN balance end) as avg_past_31_days
FROM flipside_prod_db.ethereum.erc20_balances
WHERE symbol = 'TOKE' --address_name = 'tokemak: toke token'
AND (balance_date between '2022-01-01' AND '2022-01-31' or balance_date >=CURRENT_DATE-31)
GROUP BY user_address
HAVING avg_jan is not null
) a
LEFT JOIN ethereum.core.dim_labels b
ON a.user_address = b.address
qualify ntile_percent = 1
)
group by 1
order by 1
-- same query as above, just in a CTE
)
select
bin_floor,
bin_floor || ' - ' || (bin_floor + 10) as bin_range,
Run a query to Download Data