kaibladeNew Authorizers copy
    Updated 2023-03-15
    -- forked from 69856e79-1d53-4ae5-9f4f-0a9e4514d888

    with
    social_inits as (
    select * from near.core.fact_actions_events_addkey
    where receiver_id = 'social.near'
    ),
    add_signer as (
    select
    k.tx_hash,
    k.block_id,
    k.block_timestamp,
    t.tx_signer,
    k.allowance,
    t.tx_signer = t.tx_receiver
    from social_inits k
    left join near.core.fact_transactions t using (tx_hash)
    ),
    de_dupe_resigners as (
    select
    block_timestamp,
    tx_signer
    from add_signer
    -- ignore subsequent authorizations by the same signer
    qualify ROW_NUMBER() over (
    PARTITION BY tx_signer
    ORDER BY
    block_timestamp ASC
    ) = 1
    )
    select
    block_timestamp::date as "Date",
    count(distinct tx_signer) as "Unique Authorizers",
    sum("Unique Authorizers") over (order by "Date") as "Cumulative Unique Authorizers"
    from de_dupe_resigners
    Run a query to Download Data