select

distinct sum(delegated_amount/pow(10, 6)) as delegated_amount,

date,

validator

from

(

select

distinct CS.tx_id,

CS.msg_index,

date_trunc('day', CS.block_timestamp) as date,

case when CS.attribute_key = 'validator' then CS.attribute_value end as validator,

case when CMS.attribute_value not ilike '%uatom%' then CMS.attribute_value else substr(CMS.attribute_value, 1, (length(CMS.attribute_value)-5)) end as delegated_amount

from cosmos.core.fact_msg_attributes CS

join cosmos.core.fact_msg_attributes CMS

on CS.msg_index = CMS.msg_index

and CS.tx_id = CMS.tx_id

where CS.msg_type ilike 'delegate'

and validator is not null

and delegated_amount is not null

and CMS.attribute_key = 'amount'

group by 2, 3

),

redelegate1 as (

select amount,

date,

source_validator,

destination_validator

from (

select

distinct

TU.tx_id,

TU.msg_index,

date_trunc('day', TU.block_timestamp) as date,

case when TU.attribute_key = 'source_validator' then TU.attribute_value end as source_validator,