SpecterTop depositor
    Updated 2025-03-03
    WITH Ncprice AS (
    SELECT
    trunc(hour, 'day') AS day,
    AVG(price) AS price
    FROM
    near.price.ez_prices_hourly
    WHERE
    token_address = 'wrap.near'
    GROUP BY
    day
    ),

    Deposits AS (
    SELECT
    d.signer_id AS owner,
    SUM((try_parse_json(d.CLEAN_LOG):data[0]:amounts[0] / 1e24) * p.price) AS total_deposit_usd
    FROM near.core.fact_logs d
    LEFT JOIN Ncprice p
    ON trunc(d.block_timestamp, 'day') = p.day
    WHERE d.predecessor_id = 'wrap.near'
    AND d.receiver_id = 'intents.near'
    AND d.receipt_succeeded = 1
    GROUP BY owner
    )

    SELECT
    owner,
    total_deposit_usd
    FROM Deposits
    ORDER BY total_deposit_usd DESC
    LIMIT 50;

    QueryRunArchived: QueryRun has been archived