voter

, count(distinct proposal_id) as total_proposals

, sum(voting_power)/count(DISTINCT proposal_id) as sum_ape_voted

from ETHEREUM.CORE.EZ_SNAPSHOT

where space_id = 'apecoin.eth'

group by 1

order by 3 desc

),

voters_categorized AS (

SELECT

p.*

, (CASE WHEN total_proposals = 1 THEN 'One-time'

WHEN total_proposals >= 68 THEN 'All'

ELSE 'Occasional' END) AS freq_group

, (CASE WHEN sum_ape_voted < 1 THEN '< 1 APE'

WHEN sum_ape_voted <= 100 THEN '1 to 100 APE'

WHEN sum_ape_voted <= 1000 THEN '10 to 1000 APE'

ELSE '> 1000 APE' END) AS voting_power_group

, 1 AS dummy_count

FROM proposals AS p)

freq_group

, count(*) AS voters

FROM voters_categorized

GROUP BY 1