sarathunstakeers 1
Updated 2022-07-17
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
›
⌄
with data1 as (select tx_caller_address as unstaker,count(distinct tx_id) as unstakes_count from osmosis.core.fact_staking where currency = 'uosmo'
and action = 'undelegate' and tx_status = 'SUCCEEDED'
group by 1),
data2 as (select tx_caller_address as redelegator, count(distinct tx_id) as redelegate_count from osmosis.core.fact_staking
where currency = 'uosmo' and action = 'redelegate' and tx_status = 'SUCCEEDED' and tx_caller_address in (select unstaker from unstakers)
group by 1),
data3 as (select liquidity_provider_address as LPuser,count(distinct tx_id) as LPs_count from osmosis.core.fact_liquidity_provider_actions
where currency = 'uosmo' and action = 'pool_joined' and tx_status = 'SUCCEEDED' and liquidity_provider_address in (select unstaker from unstakers)
group by 1),
swappers as (select
trader,
count(distinct tx_id) as swap_count
from osmosis.core.fact_swaps
where from_currency = 'uosmo'
and tx_status = 'SUCCEEDED'
and trader in (select unstaker from unstakers)
group by 1)
select 'Only Re-delegators' as type, count(redelegator) as users_count
from redelegators
where redelegator not in (select LPuser from data2)
and redelegator not in (select trader from swappers)
union
select 'Only Lpers' as type, count(LPuser) as users_count
from Lpers
where LPuser not in (select redelegator from redelegators)
and LPuser not in (select trader from swappers)
union
select 'Only Swappers' as type, count(trader) as users_count
from swappers
where trader not in (select redelegator from redelegators)
and trader not in (select LPuser from Lpers)
Run a query to Download Data