kaibladeMonthly Active Pool Volume
    Updated 2022-11-09
    WITH raw_data AS
    (
    SELECT block_timestamp,
    tx_hash,
    tx_receiver,
    tx_signer,
    tx:actions[0]:FunctionCall:deposit/1e24 AS staked_amount,
    tx:receipt[0]:outcome:logs as logs,
    logs[array_size(logs)-1] AS last_item,
    split(last_item, ' ') AS item_array,
    TRIM(item_array[5], '.') AS total_node_stake,
    item_array[10] AS total_stake_shares
    FROM near.core.fact_transactions
    WHERE tx_hash IN ( SELECT tx_hash
    FROM near.core.fact_actions_events_function_call
    WHERE method_name IN ('deposit_and_stake'))
    AND (total_node_stake IS NOT NULL AND total_stake_shares IS NOT NULL)
    )


    SELECT
    DATE_TRUNC('month',block_timestamp) AS months,
    count(DISTINCT tx_receiver) as active_pools
    FROM raw_data
    GROUP BY 1

    Run a query to Download Data