nitsParticipants in other pools
Updated 2022-02-05
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 prices as (select * from (select avg(price) as avg_price, token_address, date(hour) as day from ethereum.token_prices_hourly
GROUP by 2,3 )
where day= CURRENT_DATE -1 ),
lp_sushi as (select from_address as ua , sum(p) as total_lp from(select *,presence*avg_price as p from (select * from
(select from_address, token_address as ta, sum(net_amt) as presence from (select *, case when direction = 'IN' then amount_in else amount_out*(-1) end as net_amt from ethereum.dex_swaps
where platform = 'sushiswap')
where net_amt is not NULL
GROUP by 1,2)
where presence > '0.0001' and presence < pow(10,5))
inner join prices
on token_address = ta
)
GROUP by 1 )
select distribution, avg(total_lp_others) as avg_lp_provided_to_others, avg(total_lp) as avg_sushi_lp, count(DISTINCT from_address) as UNIQUE_addresses
from
(select *, case when percent_SUSHI_LP = '0' then 'a- no lp'
when percent_SUSHI_LP > '0' and percent_SUSHI_LP < '0.01' then 'b- almost no lp'
when percent_SUSHI_LP >='0.01' and percent_SUSHI_LP < '1' then 'c-less than 1%'
when percent_SUSHI_LP >='1' and percent_SUSHI_LP < '10' then 'd-1-10%'
when percent_SUSHI_LP >='10' and percent_SUSHI_LP < '20' then 'e-10-20%'
when percent_SUSHI_LP >='20' and percent_SUSHI_LP < '30' then 'f-20-30%'
when percent_SUSHI_LP >='30' and percent_SUSHI_LP < '40' then 'g-30-40%'
when percent_SUSHI_LP >='40' and percent_SUSHI_LP < '50' then 'h-40-50%'
when percent_SUSHI_LP >='50' and percent_SUSHI_LP < '60' then 'i-50-60%'
when percent_SUSHI_LP >='60' and percent_SUSHI_LP < '70' then 'j-60-70%'
when percent_SUSHI_LP >='70' and percent_SUSHI_LP < '80' then 'k-70-80%'
when percent_SUSHI_LP >='80' and percent_SUSHI_LP < '90' then 'l-80-90%'
when percent_SUSHI_LP >='90' and percent_SUSHI_LP < '100' then 'm-90-100%'
when percent_SUSHI_LP ='100' then 'n-100%'
end as distribution
from
(select *, total_lp/ (total_lp + total_lp_others )*100 as percent_SUSHI_LP from
(select from_address , sum(p) as total_lp_others from(select *,presence*avg_price as p from (select * from
(select from_address, token_address as ta, sum(net_amt) as presence from (select *, case when direction = 'IN' then amount_in else amount_out*(-1) end as net_amt from ethereum.dex_swaps
where platform != 'sushiswap')
where net_amt is not NULL
Run a query to Download Data