adambalaUntitled Query
Updated 2022-08-09
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
›
⌄
select
balance_date as Dates,
sum(case when (contract_address = lower('0xb4bd4628e6efb0cb521d9ec35050c75840320374') and balance < 0.001) then 1 else 0 end) as Tier_6,
sum(case when (contract_address = lower('0xb4bd4628e6efb0cb521d9ec35050c75840320374') and balance >= 0.001 and balance < 0.01) then 1 else 0 end) as Tier_5,
sum(case when (contract_address = lower('0xb4bd4628e6efb0cb521d9ec35050c75840320374') and balance >= 0.01 and balance < 0.1) then 1 else 0 end) as Tier_4,
sum(case when (contract_address = lower('0xb4bd4628e6efb0cb521d9ec35050c75840320374') and balance >= 0.1 and balance < 1) then 1 else 0 end) as Tier_3,
sum(case when (contract_address = lower('0xb4bd4628e6efb0cb521d9ec35050c75840320374') and balance >= 1 and balance < 10) then 1 else 0 end) as Tier_2,
sum(case when (contract_address = lower('0xb4bd4628e6efb0cb521d9ec35050c75840320374') and balance > 10) then 1 else 0 end) as Tier_1,
Tier_1 + Tier_2 + Tier_3 +Tier_6 +Tier_5 +Tier_4 as Total_members,
case when Total_members = 0 then 0 else 100*Tier_6/Total_members end as Tier_6_Ratio,
case when Total_members = 0 then 0 else 100*Tier_5/Total_members end as Tier_5_Ratio,
case when Total_members = 0 then 0 else 100*Tier_4/Total_members end as Tier_4_Ratio,
case when Total_members = 0 then 0 else 100*Tier_3/Total_members end as Tier_3_Ratio,
case when Total_members = 0 then 0 else 100*Tier_2/Total_members end as Tier_2_Ratio,
case when Total_members = 0 then 0 else 100*Tier_1/Total_members end as Tier_1_Ratio
from ethereum.erc20_balances
where balance_date > '2022-01-01' and contract_address = lower('0xb4bd4628e6efb0cb521d9ec35050c75840320374') and user_address not in (
'0x573c0609a8cac30b7a8a65e3652f0511caeffd30', -- main wallet
'0x0fbb8d17027b16810795b12cbeadc65b252530c4', -- truefreeze.eth
'0x4c0c29539c463af348f8cba8c02d644a8d68c320' , --xToken: Deployer
'0xa4b35daf88a8c894c4664f0625c6e263c61bfcc9', -- Uniswap V3:
'0x5a9638277f5146f0637fe2ad35b70d43f0e3167c', -- Uniswap V3:
'0x39e856863e5f6f0654a0b87b12bc921da23d06bb' ,-- charliemarketplace.eth
'0xe8c060f8052e07423f71d445277c61ac5138a2e5' --MEV Bot
)
group by Dates
order by Dates DESC