jkhuhnke11New Osmosis Users Vs Voters
    Updated 2023-04-13
    WITH dist_wallets AS (
    SELECT
    block_timestamp,
    tx_from
    FROM osmosis.core.fact_transactions
    qualify(ROW_NUMBER() over(PARTITION BY tx_from
    ORDER BY
    block_timestamp ASC)) = 1
    ),

    dist_voters AS (
    SELECT
    block_timestamp,
    voter
    FROM osmosis.core.fact_governance_votes
    qualify(ROW_NUMBER() over(PARTITION BY voter
    ORDER BY
    block_timestamp ASC)) = 1
    )

    SELECT
    d.block_timestamp :: DATE AS day,
    count(DISTINCT voter) as new_voters,
    count(DISTINCT tx_from) AS new_users,
    avg(count(DISTINCT voter) / count(DISTINCT tx_from))
    over (order by day rows between 2 preceding and current row)
    as "3-DAY MOVING AVERAGE"
    FROM dist_wallets d
    INNER JOIN dist_voters v
    ON d.block_timestamp :: DATE = v.block_timestamp :: DATE
    GROUP BY d.block_timestamp :: DATE
    Run a query to Download Data