SELECT

BLOCK_ID,

BLOCK_TIMESTAMP,

TX_ID

FROM cosmos.core.fact_msg_attributes

WHERE TX_SUCCEEDED = 'TRUE'

AND ATTRIBUTE_KEY = 'proposal_id'

AND MSG_TYPE = 'proposal_vote'

AND ATTRIBUTE_VALUE = '82'

),

voter as (

SELECT t2.*,t1.tx_from,t1.gas_used,t1.gas_wanted,t1.TX_LOG

FROM cosmos.core.fact_transactions t1 JOIN proposal t2 on t1.TX_ID = t2.TX_ID

),

amounts as(

SELECT

tx_id,

(REGEXP_substr(ATTRIBUTE_VALUE,'.*[0-9]')/1000000) as amount

from cosmos.core.fact_msg_attributes

where ATTRIBUTE_KEY = 'amount'

and MSG_TYPE = 'proposal_deposit'

),

sender as (

SELECT

t1.tx_id,

t1.ATTRIBUTE_VALUE as sender,

t2.amount

from cosmos.core.fact_msg_attributes t1 JOIN amounts t2 on t1.tx_id = t2.tx_id

WHERE ATTRIBUTE_KEY = 'sender'

),

r as (

SELECT

row_number() over (partition by tx_from order by BLOCK_TIMESTAMP::date desc) as rank,

*