CoinConverseFriktion Bitcoin Volt (BTC Volt Distribution)
Updated 2022-03-19
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
with BTC_Volt as (select block_timestamp::date as dt, inner_instruction:instructions[1]:parsed:info:amount/1e6 as amount_btc,
inner_instruction:instructions[1]:parsed:info:authority as users
from solana.events
where posttokenbalances[1]:mint = '3BjcHXvyzMsjmeqE2qFLx45K4XFx3JPiyRnjJiF5MAHt' and succeeded = 'TRUE' and index = 1 and amount_btc is not null
and block_timestamp::date >='2022-01-28'),
total_btc_per_user as (select users, sum(amount_btc) as amount_btc_per_user
from BTC_Volt
group by 1)
select count(distinct users) as num_users, case when amount_btc_per_user > 0 and amount_btc_per_user <= 10 then 'a. Shrimp 0-10'
when amount_btc_per_user > 10 and amount_btc_per_user <= 100 then 'b. Crab 10-100'
when amount_btc_per_user > 100 and amount_btc_per_user <= 500 then 'c. Octopus 100-500'
when amount_btc_per_user > 500 and amount_btc_per_user <= 1000 then 'd. Fish 500-1k'
when amount_btc_per_user > 1000 and amount_btc_per_user <= 10000 then 'e. Dolphin 1k-10k'
when amount_btc_per_user > 10000 and amount_btc_per_user <= 100000 then 'f. Shark 10k-100k'
when amount_btc_per_user > 100000 then 'g. Whale >1M' else null end as Tier
from total_btc_per_user
group by 2
Run a query to Download Data