headitmanagerFrequently of Osmosis unstaking
Updated 2022-07-16
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 stake as (select block_timestamp as ddate,delegator_address as d1 from osmosis.core.fact_staking where action='delegate')
, unstake as (select block_timestamp as unddate,delegator_address as d2 from osmosis.core.fact_staking where action='undelegate')
,diff_tbl as (select datediff('day',ddate::date,unddate::date) as diff , d1 from stake inner join unstake on d1=d2 and unddate>ddate)
, frequently as (select count(d1), diff , CASE
when diff < 10 then 'less than 10 days'
when diff >= 10 and diff < 30 then 'between 10 days to 1 month'
when diff >= 30 and diff < 90 then 'between 1 month to 3 months'
when diff >= 90 and diff < 180 then 'between 3 months and 6 months'
when diff >= 180 and diff < 270 then 'between 6 months and 9 months'
when diff >= 270 and diff < 365 then 'between 9 months and 1 year'
when diff >= 365 then 'more than 1 year' end as cat , 1
from diff_tbl
group by diff)
, total_number_of_unstake as (select count(distinct d2) from unstake)
, total_number_of_unstakers_that_swaps as (
select count(distinct trader) from osmosis.core.fact_swaps inner join unstake on osmosis.core.fact_swaps.trader=d2 and unddate < block_timestamp
where from_currency='uosmo')
, total_number_of_unstakers_that_redelegate as (select count(distinct delegator_address) from osmosis.core.fact_staking
inner join unstake on osmosis.core.fact_staking.delegator_address=d2
and unddate < block_timestamp
where action='redelegate')
, total_number_of_unstakers_that_LPposition as (select count(distinct liquidity_provider_address) from osmosis.core.fact_liquidity_provider_actions
inner join unstake on osmosis.core.fact_liquidity_provider_actions.liquidity_provider_address=d2
and unddate < block_timestamp)
, more_frequently_redelegate as (select count(*),delegator_address from osmosis.core.fact_staking inner join unstake on osmosis.core.fact_staking.delegator_address=d2
and unddate < block_timestamp
where action='redelegate'
group by delegator_address
order by count(*) DESC
Run a query to Download Data