count(DISTINCT tx_id) as votes,

count(DISTINCT voter) as voters,

count(DISTINCT PROGRAM_NAME) as Active_DAOs,

count(DISTINCT proposal) as proposals ,

sum (votes) over (order by DATE) as Cumulative_Votes,

sum (voters) over (order by DATE) as Cumulative_Voters,

sum (Active_DAOs) over (order by DATE) as Cumulative_Active_DAOs,

sum (Proposals) over (order by DATE) as Cumulative_proposals,

Cumulative_Votes / Cumulative_Active_DAOs as Average_votes_per_DAOs,

Cumulative_Votes / Cumulative_Voters as Average_Votes_per_Voters,

Cumulative_Votes / Cumulative_proposals as Average_voters_per_proposal,

Cumulative_proposals / Cumulative_Active_DAOs as Average_proposals_per_dao

FROM solana.core.fact_proposal_votes WHERE GOVERNANCE_PLATFORM LIKE 'realms' and DATE >= '2022-01-01'

GROUP BY 1,2),

snapshotProgram as (SELECT date_trunc('day', vote_timestamp) as DATE, 'Snapshot' as program,

count(DISTINCT id) as votes,

count(DISTINCT voter) as voters,

COUNT(DISTINCT space_id) as Active_DAOs,

COUNT(DISTINCT PROPOSAL_ID) as proposals,

sum (votes) over (order by DATE) as Cumulative_Votes,

sum (voters) over (order by DATE) as Cumulative_Voters,

sum (Active_DAOs) over (order by DATE) as Cumulative_Active_DAOs,

sum (Proposals) over (order by DATE) as Cumulative_proposals,

Cumulative_Votes / Cumulative_Active_DAOs as Average_votes_per_DAOs,

Cumulative_Votes / Cumulative_Voters as Average_Votes_per_Voters,

Cumulative_Votes / Cumulative_proposals as Average_voters_per_proposal,

Cumulative_proposals / Cumulative_Active_DAOs as Average_proposals_per_dao

FROM ethereum.core.ez_snapshot

WHERE DATE >= '2022-01-01'

GROUP BY 1,2 )

SELECT * FROM realmsProgram UNION SELECT * FROM snapshotProgram