from cosmos.core.fact_msg_attributes a join cosmos.core.fact_transactions b on a.tx_id = b.tx_id

where b.TX_SUCCEEDED = 'TRUE' and ATTRIBUTE_KEY = 'proposal_id')

,

vote_option as ( select date, voter, a.tx_id, proposal_id,try_parse_json (attribute_value) option,

option:option as vote_option, case when vote_option = 1 then 'YES'

when vote_option = 2 then 'Abstain'

when vote_option = 3 then 'No'

when vote_option = 4 then 'No With Veto' end as vote_option

from cosmos.core.fact_msg_attributes a join proposals b on a.tx_id = b.tx_id

where ATTRIBUTE_KEY = 'option')

,

redelage as ( select date(a.block_timestamp) as date, tx_from , b.tx_id, replace(ATTRIBUTE_VALUE,'uatom','' ) as amount

from cosmos.core.fact_msg_attributes a join cosmos.core.fact_transactions b on a.tx_id = b.tx_id

where MSG_TYPE = 'redelegate' and ATTRIBUTE_KEY = 'amount'

and ATTRIBUTE_VALUE ilike '%uatom%')

,

source as ( select date(block_timestamp) as date, tx_from , a.tx_id,ATTRIBUTE_VALUE as source, amount/pow(10,6) as amounts

from cosmos.core.fact_msg_attributes a join redelage b on a.tx_id = b.tx_id

where ATTRIBUTE_KEY = 'source_validator')

,

desti as ( select date, tx_from , a.tx_id, source , ATTRIBUTE_VALUE as destination , amounts

from cosmos.core.fact_msg_attributes a join source b on a.tx_id = b.tx_id

where ATTRIBUTE_KEY = 'destination_validator'

,

label as ( select date, tx_from , tx_id, source , b.LABEL as source_name, destination , c.label as destination_name, amounts

from desti a join cosmos.core.fact_validators b on a.source = b.address

join cosmos.core.fact_validators c on a.destination = c.address)

select concat(source_name,'->',destination_name) as redelgate_name,

count(DISTINCT(tx_from)) as total_redelegator, count(DISTINCT(source_name)) as total_source_validator,

count(DISTINCT(tx_id)) as total_redelegate, sum(amounts) as toatl_atom, avg(amounts) as avg_amounts

from label

group by 1

order by 2 desc