select *,

case

when month = '2022-05-01' then (staking_power/386000000*100)

when month = '2022-06-01' then (staking_power/406000000*100)

when month = '2022-07-01' then (staking_power/423000000*100)

when month = '2022-08-01' then (staking_power/439000000*100)

when month = '2022-09-01' then (staking_power/456000000*100)

when month = '2022-10-01' then (staking_power/473000000*100)

when month = '2022-11-01' then (staking_power/489000000*100)

when month = '2022-12-01' then (staking_power/506000000*100)

when month = '2023-01-01' then (staking_power/523000000*100)

when month = '2023-02-01' then (staking_power/539000000*100)

when month = '2023-03-01' then (staking_power/556000000*100)

when month = '2023-04-01' then (staking_power/572000000*100) end as validator_staking_power_to_circulating_supply

from (

select distinct

sum(staking_power) over (partition by validator order by month) as staking_power,

month,

case when label is not null then label else validator end as validator

from

select

sum(case when action in ('delegate', 'redelegate') then (amount/pow(10, 6)) else (-amount/pow(10, 6)) end) as staking_power,

validator_address as validator,

date_trunc('month', block_timestamp) as month

from osmosis.core.fact_staking

group by 2, 3

union all

select

sum(-amount/pow(10, 6)) as staking_power,

REDELEGATE_SOURCE_VALIDATOR_ADDRESS as validator,

date_trunc('month', block_timestamp) as month

from osmosis.core.fact_staking

where action = 'redelegate'