SELECT

voter,

vote_timestamp,

id,

proposal_id :: STRING as pollid

FROM ethereum.core.ez_snapshot

WHERE

space_id = 'aave.eth'

UNION

SELECT

voter,

block_timestamp,

tx_hash,

proposal_id :: STRING as pollid

FROM ethereum.aave.ez_votes

SELECT

voter,

1 as count,

count(id) as tx_count,

count(distinct pollid) as polls_voted,

min(date_trunc('month', vote_timestamp)) as month,

min(date(vote_timestamp)) as first_vote,

max(date(vote_timestamp)) as last_vote,

datediff('day', first_vote, getdate()) as age_in_op,

datediff('day', first_vote, last_vote) as active_days_in_op,

datediff('day', last_vote, getdate()) as days_since_last_vote

FROM polls

GROUP BY 1

ORDER BY polls_voted DESC