Updated 2025-01-29
    with price AS (
    select
    date_trunc('day', hour) AS dt,
    token_address,
    avg(price) AS price_usd
    FROM
    crosschain.price.ez_prices_hourly
    WHERE
    token_address = lower('0xb8d7710f7d8349a506b75dd184f05777c82dad0c')
    GROUP BY
    1,
    2
    )
    SELECT
    'Stake' AS type,
    COUNT(DISTINCT origin_from_address) AS users,
    SUM(decoded_log:value / pow(10, 18)) AS volume,
    SUM((decoded_log:value / pow(10, 18)) * price_usd) AS volume_usd
    FROM
    avalanche.core.ez_decoded_event_logs
    LEFT JOIN price on block_timestamp :: date = dt
    WHERE
    origin_to_address = '0xeffb809d99142ce3b51c1796c096f5b01b4aaec4'
    AND event_name = 'Transfer'
    AND origin_function_signature = '0xb6b55f25'
    AND contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c'
    AND origin_from_address = decoded_log:from
    UNION
    ALL
    SELECT
    'Unstake' AS type,
    COUNT(DISTINCT origin_from_address) AS users,
    SUM(decoded_log:value / pow(10, 18)) AS volume,
    SUM((decoded_log:value / pow(10, 18)) * price_usd) AS volume_usd
    FROM
    avalanche.core.ez_decoded_event_logs
    Last run: 25 days ago
    TYPE
    USERS
    VOLUME
    VOLUME_USD
    1
    Unstake2601584587683.8085345350182.78114996
    2
    Stake81231134300921.7544510090469.9569683
    2
    101B
    19s