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
    ),
    stake_data AS (
    SELECT
    origin_from_address AS user,
    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
    GROUP BY
    1
    )
    SELECT
    CASE
    WHEN volume_usd < 50 THEN '< $50'
    WHEN volume_usd BETWEEN 50
    AND 100 THEN '$50 - $100'
    WHEN volume_usd BETWEEN 100
    Last run: 25 days ago
    STAKE_CATEGORY
    USERS
    1
    < $504913
    2
    $100 - $5001208
    3
    $50 - $100635
    4
    $1,000 - $5,000619
    5
    > $5,000389
    6
    $500 - $1,000359
    6
    119B
    20s