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 '2023-01-04' and '2023-01-18' then '2. During Prop #93'

when date < '2023-01-04' then '1. Before Prop #93'

else '3. After Prop #93'

end as period,

avg(redelegate_amount) as avg_redelegate_amount,

avg(redelegate_count) as avg_redelegate_count

from redelegate

group by 1