(

select block_timestamp::date as "date",

event_inputs:voter as "voter",

event_inputs:proposalId as proposalId,

event_inputs:support as "yes",

event_inputs:votes as "number of votes"

from ethereum.core.fact_event_logs

where contract_address='0x5d2c31ce16924c2a71d317e5bbfd5ce387854039'

and event_name='VoteCast'

-- limit 1

),

tab as (

select v."date",v."voter",v."number of votes",v.proposalId ,sum(AMOUNT_USD) as balance

from vote_det v join flipside_prod_db.ethereum.erc20_balances b

on (v."voter"=b.USER_ADDRESS and v."date"=b.balance_date)

group by v."date",v."voter",v."number of votes",v.proposalId

),

tab2 as (

select *

,case when balance < 1 and balance > 0 then 'shrimp (0-1)'

when balance < 10 and balance >= 1 then 'crab (1-10)'

when balance < 50 and balance >= 10 then 'Octpus (10-50)'

when balance < 100 and balance >= 50 then 'Fish (50-100)'

when balance < 500 and balance >= 100 then 'Dolphins (100-500)'

when balance < 1000 and balance >= 500 then ' Shark (500-1000)'

when balance < 5000 and balance >= 1000 then ' Whale (1000-5000)'

when balance >= 5000 then 'Humpback whale (5000+)'

else 'holder'

end as user_type

from tab)

select user_type, sum("number of votes") as total_votes,count(distinct "voter") as total_voters, total_votes/total_voters as votes_per_voter

from tab2

group by user_type