kaibladeNear Social Stake Stats
    Updated 2023-03-29
    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
    WHERE block_timestamp::date BETWEEN '{{Start_Date}}' AND '{{End_Date}}'
    qualify ROW_NUMBER() over (
    PARTITION BY tx_signer
    ORDER BY
    block_timestamp
    ) = 1
    ),

    stake_actions_count AS
    (SELECT DISTINCT(tx_signer) AS stakers, COUNT(tx_hash) AS stake_count
    FROM near.core.dim_staking_actions
    WHERE tx_signer IN (SELECT tx_signer FROM de_dupe_resigners)
    -- AND action= 'Stake'
    GROUP BY stakers),

    Run a query to Download Data