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 AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    COUNT(DISTINCT origin_from_address) AS stakers,
    SUM((decoded_log:value / POW(10, 18)) * price_usd) AS staked_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
    ),
    Unstake AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    COUNT(DISTINCT origin_from_address) AS unstakers,
    SUM((decoded_log:value / POW(10, 18)) * price_usd) AS unstaked_usd
    FROM
    Last run: 25 days ago
    DAY
    STAKERS
    UNSTAKERS
    STAKED_USD
    -UNSTAKED_USD
    NET_STAKED
    1
    2025-01-10 00:00:00.0005398134711.888423835-33336.9904922621374.897931573
    2
    2024-11-27 00:00:00.0001363733746.511369939-91841.985772973-58095.474403033
    3
    2024-11-23 00:00:00.00038813198975.560145316-169429.072826578-70453.512681262
    4
    2024-12-07 00:00:00.0003626063207.16372537-42337.28497626220869.878749108
    5
    2024-12-10 00:00:00.0001833842927.983900726-17749.86243967225178.121461054
    6
    2025-01-18 00:00:00.000224748722.772942307-36662.02967664-27939.256734333
    7
    2025-01-13 00:00:00.0008337340835.723757974-50798.242045681-9962.518287708
    8
    2024-11-05 00:00:00.0001595956115.547556379-41411.3413605514704.20619583
    9
    2024-12-13 00:00:00.00026359141961.256863492-100983.70361810340977.553245389
    10
    2024-12-24 00:00:00.0002815912015.415445201-23797.641287618-11782.225842416
    11
    2024-11-13 00:00:00.0001645758066.768470751-83603.880409397-25537.111938645
    12
    2024-12-09 00:00:00.0001765279961.443369713-87563.57705752-7602.133687807
    13
    2025-01-25 00:00:00.0002135337213.278336573-36775.935312804437.343023769
    14
    2025-01-17 00:00:00.0003477238097.485166683-35576.1827058522521.302460831
    15
    2024-12-11 00:00:00.0001384227187.249159683-14840.52497456512346.724185118
    16
    2025-01-04 00:00:00.0005069488685.449108409-73732.25995462914953.18915378
    17
    2024-12-19 00:00:00.0004174589470.833584847-44266.95009129645203.88349355
    18
    2024-11-10 00:00:00.00046181131092.965471433-113818.97291573317273.9925557
    19
    2025-01-02 00:00:00.0003378156125.052777383-55052.2044094411072.848367942
    20
    2024-11-07 00:00:00.0001997462704.384654614-83022.363554324-20317.97889971
    93
    8KB
    18s