jkhuhnke11 Number of Active Delegates Filtered
    Updated 2023-04-13
    WITH num_props_active AS (
    SELECT
    ceil(count(distinct proposal_id)*0.1) as num_props
    FROM "ETHEREUM"."CORE"."EZ_SNAPSHOT"
    WHERE
    space_id = 'opcollective.eth'
    ),
    voted as (
    SELECT
    voter,
    count(distinct id) as props_voted
    FROM ethereum.core.ez_snapshot
    GROUP BY voter
    ),
    active as (
    SELECT
    voter,
    case when props_voted > num_props THEN
    'TRUE'
    else 'FALSE'
    END AS is_active
    FROM voted a
    JOIN num_props_active
    INNER JOIN crosschain.core.address_tags t
    ON t.address = LOWER(voter)
    WHERE creator = 'jkhuhnke11'
    AND blockchain = 'optimism'
    AND tag_type = 'delegate_name'
    )
    SELECT
    count(distinct voter) as delegate
    FROM active
    WHERE is_active = 'TRUE'

    Run a query to Download Data