satsihMeta Pool Staking Share
    Updated 2022-11-22
    with
    meta_pool_staking as (
    SELECT
    DATE_TRUNC('day', block_timestamp) as date,
    action,
    tx_receiver,
    sum(stake_amount) as amount,
    count(distinct tx_hash) as daily_stakes,
    count(distinct tx_signer) as daily_stakers
    FROM
    (SELECT
    a.block_timestamp,
    b.tx_receiver,
    CASE
    WHEN method_name = 'deposit_and_stake' THEN 'stake'
    WHEN method_name = 'unstake' THEN 'unstake'
    END AS action,
    a.tx_hash,
    b.tx_signer,
    CASE
    WHEN method_name = 'deposit_and_stake' THEN deposit/pow(10,24)
    ELSE deposit
    END AS stake_amount
    FROM
    near.core.fact_actions_events_function_call a
    INNER JOIN near.core.fact_transactions b ON a.tx_hash = b.tx_hash
    WHERE method_name = 'deposit_and_stake' OR method_name = 'unstake' AND deposit < 1000000)
    GROUP BY 1, 2, 3
    ORDER BY 1
    )

    SELECT
    date,
    action,
    CASE
    WHEN tx_receiver = 'meta-pool.near' THEN 'Meta Pool'