tcsCumulative Sum of Users by Balance
Updated 2022-03-25
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
›
⌄
WITH max_date AS(
SELECT max(balance_date) AS max_balance_date
FROM ethereum.erc20_balances
WHERE contract_address = '0x7e9d8f07a64e363e97a648904a89fb4cd5fb94cd'
AND balance_date > '2022-03-20'
)
, total_holders_table AS(
SELECT COUNT(DISTINCT user_address) AS total_holders
FROM ethereum.erc20_balances e
JOIN max_date m
ON m.max_balance_date = e.balance_date
WHERE e.balance_date > '2022-03-20'
AND contract_address = '0x7e9d8f07a64e363e97a648904a89fb4cd5fb94cd'
)
SELECT ROUND(balance) AS rounded_balance
, COUNT(DISTINCT user_address) AS number_users
, 100*SUM(number_users) OVER (ORDER BY rounded_balance)/(SELECT total_holders FROM total_holders_table) AS cumsum_percent_number_users
FROM ethereum.erc20_balances e
JOIN max_date m
ON m.max_balance_date = e.balance_date
WHERE contract_address = '0x7e9d8f07a64e363e97a648904a89fb4cd5fb94cd'
AND balance_date > '2022-03-20'
AND balance < 5000
GROUP BY 1
ORDER BY 2 DESC
Run a query to Download Data