SalehJup_Voting Trends_hour
    Updated 2024-05-19
    --From Kellen
    with lst_all as (
    select decoded_instruction:args:side::string as side
    , decoded_instruction:args:weight::int * power(10, -6) as weight
    , signers[0]::string as voter
    , value:pubkey::string AS proposal
    , value:name::string AS name
    , block_timestamp
    from solana.core.fact_decoded_instructions i
    , LATERAL FLATTEN(input => decoded_instruction:accounts)
    where block_timestamp >= '2024-03-06'
    and i.program_id in (
    'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'
    )
    and name = 'proposal'
    and proposal = '6txWyf3guJrhnNJXcAHxnV2oVxBcvebuSbfYsgB3yUKc'
    and side is not null
    and weight is not null
    qualify(
    row_number() over (partition by voter order by block_timestamp desc) = 1
    )
    )
    select date_trunc('hour',block_timestamp) as date,
    case when side = '1' then 'Sharky'
    when side = '2' then 'Uprock'
    when side = '3' then 'Banx'
    when side = '4' then 'Zeus Network'
    when side = '5' then 'Monkey Dex'
    when side = '6' then 'Srcful'
    else 'Other' end as Protocol
    , round(sum(weight)) as Votes
    , count(1) as Voters

    from lst_all
    where weight > 0
    group by 1, 2