headitmanagerFrequently of Osmosis unstaking
    Updated 2022-07-16
    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