Eman-RazDistribution of Traders Based on Swaps Count
Updated 2023-04-07
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 tab4 as (with tab3 as (with tab1 as (select trader, count(distinct tx_hash) as "Selling"
from near.core.ez_dex_swaps
where TOKEN_IN='USN'
group by 1
order by 1),
tab2 as (select trader, count(distinct tx_hash) as "Buying"
from near.core.ez_dex_swaps
where TOKEN_out='USN'
group by 1
order by 1)
select tab1.trader as "Trader", "Selling", "Buying", case
when "Buying" is null then 0
else "Buying"
end as "Buying TXs", case
when "Selling" is null then 0
else "Selling"
end as "Selling TXs"
from tab1 left join tab2 on tab1.trader=tab2.trader
order by 2 desc)
select "Trader", ("Selling TXs"+"Buying TXs") as "Swap", case
when ("Selling TXs"+"Buying TXs")=1 then 'n=1 Swap'
when ("Selling TXs"+"Buying TXs")>1 and ("Selling TXs"+"Buying TXs")<=5 then '1<n<=5'
when ("Selling TXs"+"Buying TXs")>5 and ("Selling TXs"+"Buying TXs")<=10 then '5<n<=10'
when ("Selling TXs"+"Buying TXs")>10 and ("Selling TXs"+"Buying TXs")<=20 then '10<n<=20'
when ("Selling TXs"+"Buying TXs")>20 and ("Selling TXs"+"Buying TXs")<=50 then '20<n<=50'
when ("Selling TXs"+"Buying TXs")>50 and ("Selling TXs"+"Buying TXs")<=100 then '50<n<=100'
else 'n>100'
end as "Group"
from tab3
order by 2 desc)
select "Group", count(distinct "Trader") as "Traders Count"
from tab4
group by 1
order by 2
Run a query to Download Data