select

date_trunc('day', block_timestamp) as date,

attribute_value,

count(distinct tx_id) as votes_count

from cosmos.core.fact_msg_attributes

where

attribute_key = 'proposal_id'

group by 1, 2

),

voters as (

select

date_trunc('day', t.block_timestamp) as day,

attribute_value,

count(distinct tx_from) as unique_voters

from cosmos.core.fact_transactions t join cosmos.core.fact_msg_attributes m on t.tx_id = m.tx_id

where

attribute_key = 'proposal_id'

group by 1, 2

select *

from votes join voters on date = day and votes.attribute_value = voters.attribute_value

where date >= '2023-01-01'