WITH first_vote as(

SELECT c.attribute_value as voter,

min(a.attribute_value) as first_vote

FROM cosmos.core.fact_msg_attributes a, cosmos.core.fact_msg_attributes c

WHERE a.msg_type = 'proposal_vote'

AND a.attribute_key = 'proposal_id'

AND a.tx_id = c.tx_id

AND c.msg_type = 'coin_spent' AND c.attribute_key = 'spender'

AND a.tx_succeeded = 'TRUE'

GROUP by voter

HAVING first_vote = '82'

),

change_vote as (

SELECT a.block_timestamp as time,

a.tx_id, c.attribute_value as voter,

CASE WHEN try_parse_json(b.attribute_value):option = '1' then 'Yes'

WHEN try_parse_json(b.attribute_value):option = '2' then 'Abstain'

WHEN try_parse_json(b.attribute_value):option = '3' then 'No'

WHEN try_parse_json(b.attribute_value):option = '4' then 'No With Veto'

END as vote,

rank()over(partition by voter ORDER BY time) as rank

FROM cosmos.core.fact_msg_attributes a, cosmos.core.fact_msg_attributes b, cosmos.core.fact_msg_attributes c

WHERE a.msg_type = 'proposal_vote' and b.msg_type = 'proposal_vote'

AND a.attribute_key = 'proposal_id' AND a.attribute_value = '82'

AND b.attribute_key = 'option'

AND a.tx_id = b.tx_id

AND a.tx_id = c.tx_id

AND c.msg_type = 'coin_spent' AND c.attribute_key = 'spender'

AND a.tx_succeeded = 'TRUE'

having vote is not null

),

received_atom as (

select receiver,

sum (amount)/pow(10,6) as amnt