Sbhn_NPDaily votes of new vs old validators
    Updated 2022-11-09
    --credit : adriaparcerisas
    WITH
    news as (
    SELECT
    raw_metadata[0]['account_address'] as validator_address,
    min(trunc(block_timestamp,'day')) as debut
    FROM osmosis.core.fact_staking
    JOIN osmosis.core.dim_labels ON address = validator_address
    where currency LIKE 'uosmo'
    GROUP BY 1
    ),
    proposals_info as (
    SELECT
    proposal_id,
    voter,
    block_timestamp,
    tx_id
    FROM osmosis.core.fact_governance_votes
    )
    SELECT
    trunc(block_timestamp,'day') as date,
    proposal_id,
    case when datediff('day',debut,date)<30 then 'New validator'
    else 'Old validator' end as type,
    count(distinct voter) as voters,
    count(distinct tx_id) as votes
    from proposals_info x
    join news y on voter=validator_address
    group by 1,2,3 having proposal_id<400 order by 1 asc
    Run a query to Download Data