select

date_trunc('day', block_timestamp) as date,

sum(replace(attribute_value, 'uatom', '' )/1e6) as redelegate_amount,

count(distinct tx_id) as redelegate_count

from cosmos.core.fact_msg_attributes

where msg_type = 'redelegate' and attribute_key = 'amount'

group by 1

select

case

when date between '2022-10-31' and '2022-11-14' then '2. During Prop #82'

when date < '2022-10-31' then '1. Before Prop #82'

else '3. After Prop #82'

end as period,

avg(redelegate_amount) as avg_redelegate_amount,

avg(redelegate_count) as avg_redelegate_count

from redelegate

group by 1