select date(block_timestamp) as stake_day, delegator_address as staker_address from osmosis.core.fact_staking

where action='delegate'),

unstake_dtls as (

select date(block_timestamp) as unstake_day, delegator_address as unstaker_address from osmosis.core.fact_staking

where action='undelegate'

),

unstake_durations as (

select datediff('day',stake_day, unstake_day) as duration, staker_address as wallet

from stake_dtls inner join unstake_dtls on staker_address = unstaker_address

and unstake_day > stake_day

),

unstaking_catg as (

select count(distinct wallet) as no_of_unstakers,

CASE

when duration < 10 then 'less than 10 days'

when duration >= 10 and duration < 50 then 'Around 10 to 25 days'

when duration >= 25 and duration < 50 then 'Around 25 to 50 days'

when duration >= 50 and duration < 75 then 'Around 50 to 75 days'

when duration >= 75 and duration < 100 then 'Around 75 to 100 days'

when duration >= 100 and duration < 125 then 'Around 100 to 125 days'

when duration >= 125 and duration < 150 then 'Around 125 to 150 days'

when duration >= 150 and duration < 200 then 'Around 150 to 200 days'

when duration >= 200 and duration < 250 then 'Around 200 to 250 days'

when duration >= 250 and duration < 300 then 'Around 250 to 300 days'

when duration >= 300 and duration < 365 then 'Around 300 to 365 days'

when duration >= 365 then 'Greater than 1 year'

end as unstaking_frequency

from unstake_durations

group by unstaking_frequency

order by no_of_unstakers desc)

select * from unstaking_catg