SELECT

DISTINCT realms_id

FROM solana.core.fact_proposal_votes

WHERE governance_platform = 'realms'

),

props AS (

SELECT

DISTINCT

realms_id,

proposal,

block_timestamp :: date AS creation_date

FROM solana.core.fact_proposal_creation

SELECT

v.realms_id AS space,

count(distinct voter) as unique_voters,

count(distinct v.proposal) as unique_proposals,

min(creation_date) AS date_first_proposal,

max(creation_date) AS date_last_proposal

FROM solana.core.fact_proposal_votes v

INNER JOIN realms r

ON v.realms_id = r.realms_id

INNER JOIN props p

ON v.realms_id = p.realms_id

GROUP BY v.realms_id