from osmosis.core.fact_msg_attributes

where attribute_value not like '%gam%' and attribute_value not like '%ibc%' and attribute_value not like '%uio%' and attribute_key = 'amount'

and msg_type not in ('delegate', 'add_tokens_to_lock','lock_tokens') and attribute_value like '%uosmo%')

,

total_supply as ( select sup_date, sum(sup_amount) as circulating, sum(circulating) over (order by sup_date asc) as cum_circulating

from supply

group by 1

order by 1)

,

stake as ( select date(block_timestamp) as date ,msg_type , tx_id, to_number(replace(attribute_value,'uosmo',''))/1e6 as amount

from osmosis.core.fact_msg_attributes

where msg_type = 'delegate' and attribute_value like '%osmo%' and attribute_key = 'amount'

and block_timestamp::date >= CURRENT_DATE - 90)

,

stake_amount as ( select date , count(DISTINCT(tx_id)) as number_of_stake , sum(amount) as total_stake_amount,

sum(total_stake_amount) over (order by date asc) as cum_stake_amount

from stake

group by 1

order by 1)

select date , total_stake_amount , circulating , (total_stake_amount/circulating)*100 as percentage

from total_supply a left outer join stake_amount b on a.sup_date = b.date

order by 1