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
    origin_from_address AS unstaker,
    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 = '0x2e1a7d4d'
    AND tx_hash NOT IN (
    SELECT
    DISTINCT tx_hash
    FROM
    avalanche.core.ez_decoded_event_logs
    WHERE
    origin_to_address = '0xeffb809d99142ce3b51c1796c096f5b01b4aaec4'
    AND event_name = 'Transfer'
    AND origin_function_signature = '0x2e1a7d4d'
    AND contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c'
    AND decoded_log:value / 1e18 = '0'
    Last run: 25 days ago
    UNSTAKER
    USERS
    VOLUME
    VOLUME_USD
    1
    0x117f1f13209de72ae7a3ff6d75397117f39a9d3e125135638205818.680922321
    2
    0x2189bde18d13745724f34ab1d1c7d5042f7b156d112869628.7214666198764.367900476
    3
    0xc042a6d1f61df1763b7251ba18c0a3861ccf7cb7118251194.34195052.361565481
    4
    0x2ee647714bf12c5b085b9aed44f559825a57b9df117597550.7417161184113.128987464
    5
    0xa3123bf411468cb503bfb2641dae5c5a44d24315129999999.9999684166887.999999824
    6
    0x90a11de52bafefa425a039fac00a137952e6a350117492756.509589147062.53984713
    7
    0x6277a59660f9e98fc614890b883b29add4a565dd19232262.9184137986903.56041866
    8
    0xf2b865689693d5f60164a4180d4c307b90c3e68619288364.3600805984072.434090825
    9
    0xc4891831df9637609e2881fd9f8937aa1a795ae019056371.3611088880764.752128475
    10
    0xff873cc9fd4e21878d6308eb7b2d38c90924aaf914913090.0331308171387.202829321
    11
    0xd1a5c9a345eacb7d266ccaeec637249bce00e3681775000071024.1781875
    12
    0x0000ad5c7e96192a1528dc5cc64229eac48f0000111747827.976182169392.334922968
    13
    0xc4bfda35a5057b6979cc787425fc05b108ecd37017473698.639166631.153838206
    14
    0x9b81e1d39037e6191c4f7cad62371fa16131a70316296889.2617820857467.273912163
    15
    0xb896ee17898b35f0b71f151fcc6b02a4713973b816427300.1747273956130.347907872
    16
    0x3efda2796a232d4515dff4bbcf3095a13737385e16989656.0283840955480.254040267
    17
    0xf8b31563ea65091a1b247e4338b150c1d6feec7a15843938.6214933848679.233122388
    18
    0x82fdb64647c70f8dff1532cc312a97f63d64bb1b13814451.564717547857.72431341
    19
    0xd0e8a4171a7b24d5d467a09fb78ea8b8da888c9416414778.6095792847293.618921551
    20
    0x9473925d10e9a2ee880cdf6f90c96243bf275a3213291407.8116286143260.464030403
    ...
    2601
    191KB
    22s