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
    TVL
    1
    2025-01-10 00:00:00.0005398134711.888423835-33336.9904922621374.8979315734885711.89855919
    2
    2024-10-29 00:00:00.0002653
    3
    2024-11-25 00:00:00.0001804946825.973538369-41034.4764921435791.4970462261911298.0707462
    4
    2024-12-20 00:00:00.00033454105647.864766148-108890.94697834-3243.0822121923464718.31560896
    5
    2024-11-30 00:00:00.0001058107457463.975861436-105611.805313958351852.1705474783168580.48758139
    6
    2025-01-19 00:00:00.0002468242288.178740296-46806.365223694-4518.1864833984847483.42255206
    7
    2024-11-27 00:00:00.0001363733746.511369939-91841.985772972-58095.4744030331800891.59490122
    8
    2024-11-01 00:00:00.00054497255012.570727038-101222.801346646153789.7693803931781003.46987897
    9
    2024-11-22 00:00:00.00020010741350.545733759-118512.863795351-77162.3180615921937156.34363069
    10
    2024-11-23 00:00:00.00038813198975.560145316-169429.072826578-70453.5126812621866702.83094943
    11
    2024-11-09 00:00:00.0001723755269.932891629-25894.38319491829375.5496967121960883.56766456
    12
    2024-12-04 00:00:00.0002419242563.124478261-68259.383883823-25696.2594055623267697.21043798
    13
    2024-12-10 00:00:00.0001833842927.983900726-17749.86243967225178.1214610543273178.98256272
    14
    2024-12-07 00:00:00.0003626063207.16372537-42337.28497626220869.8787491083330824.79947586
    15
    2024-10-30 00:00:00.00020942581822276.38407476-362090.4556629921460185.928411771460185.92841177
    16
    2024-10-31 00:00:00.000575101256160.263882009-89132.491795201167027.7720868081627213.70049858
    17
    2024-11-14 00:00:00.0001064336711.984742208-28222.7130792668489.2716629422059320.86893621
    18
    2025-01-25 00:00:00.0002135337213.278336573-36775.935312804437.3430237694716547.73807328
    19
    2025-01-17 00:00:00.0003477238097.485166683-35576.1827058522521.3024608314879940.86576979
    20
    2024-12-11 00:00:00.0001384227187.249159683-14840.52497456512346.7241851183285525.70674784
    93
    9KB
    18s