with tab1 as (

SELECT

voter as v1,

CASE WHEN vote_events = 1 THEN 'one vote' ELSE 'vote change' END as vote

FROM (

SELECT

voter,

--date_trunc('hour', block_timestamp) as day,

--description,

count(DISTINCT description) as vote_events

--sum(VOTE_WEIGHT) as vote_power

FROM osmosis.core.fact_governance_votes

LEFT outer join osmosis.core.dim_vote_options

on vote_id = vote_option

WHERE proposal_id = 362

AND block_timestamp > '2022-11-13'

GROUP by 1

)

), tab2 as (

SELECT

VOTER as v2,

min(block_timestamp) as first_vote

FROM osmosis.core.fact_governance_votes

LEFT outer join osmosis.core.dim_vote_options

on vote_id = vote_option

WHERE proposal_id = 362

AND block_timestamp > '2022-11-13'

GROUP by 1

SELECT

date_trunc('hour', block_timestamp) as hour,

CASE WHEN vote LIKE 'one vote' or first_vote = block_timestamp THEN 'First Vote' ELSE 'Changed Their Vote' END as vote_type,

count(*)

FROM osmosis.core.fact_governance_votes

LEFT outer join tab1