SpecterBURROW staking
    Updated 2025-01-21
    WITH Ncprice AS (
    SELECT
    trunc(hour, 'day') AS day,
    AVG(price) AS price
    FROM
    near.price.ez_prices_hourly
    WHERE
    token_address = 'token.burrow.near'
    GROUP BY
    day
    ),

    Burrow AS (
    SELECT
    block_timestamp,
    tx_hash,
    signer_id,
    try_parse_json(CLEAN_LOG) AS log,
    CASE
    WHEN log:event = 'booster_stake' THEN 'Stake'
    ELSE 'Unstake'
    END AS method,
    log:data[0]:total_booster_amount / 1e18 AS amount_adj,
    trunc(block_timestamp, 'day') AS day
    FROM
    near.core.fact_logs
    WHERE
    receiver_id = 'contract.main.burrow.near'
    AND log:event IN ('booster_stake', 'booster_unstake' )
    AND receipt_succeeded = 1
    AND block_timestamp::date >= '2022-04-22'
    )

    SELECT method,
    COUNT(DISTINCT b.tx_hash) AS total_transactions,
    COUNT(DISTINCT b.signer_id) AS total_users,
    QueryRunArchived: QueryRun has been archived