flipside

    breakable-plum
    feyikemibreakable-plum
    Updated 2025-03-24
    WITH staked AS (
    -- Get total staked amount per staker
    SELECT
    DECODED_LOG:"sender" AS staker,
    SUM(COALESCE(DECODED_LOG:"assets", 0) / 1e18) AS total_staked
    FROM avalanche.core.ez_decoded_event_logs
    WHERE contract_address = '0x06d47f3fb376649c3a9dafe069b3d6e35572219e'
    AND EVENT_NAME = 'Deposit'
    AND TX_SUCCEEDED = 'TRUE'
    GROUP BY 1
    ),
    unstaked AS (
    -- Get total unstaked amount per staker
    SELECT
    DECODED_LOG:"sender" AS staker,
    SUM(COALESCE(DECODED_LOG:"assets", 0) / 1e18) AS total_unstaked
    FROM avalanche.core.ez_decoded_event_logs
    WHERE contract_address = '0x06d47f3fb376649c3a9dafe069b3d6e35572219e'
    AND EVENT_NAME = 'Withdraw'
    AND TX_SUCCEEDED = 'TRUE'
    GROUP BY 1
    )
    SELECT
    COALESCE(s.staker, u.staker) AS staker,
    TO_VARCHAR(ROUND(COALESCE(s.total_staked, 0), 2), '999,999,999,999') AS total_staked,
    TO_VARCHAR(ROUND(COALESCE(u.total_unstaked, 0), 2), '999,999,999,999') AS total_unstaked,
    TO_VARCHAR(ROUND(COALESCE(s.total_staked, 0) - COALESCE(u.total_unstaked, 0), 2), '999,999,999,999') AS net_stake_volume
    FROM staked s
    FULL JOIN unstaked u ON s.staker = u.staker
    ORDER BY s.total_staked DESC NULLS LAST
    Last run: 2 months ago
    STAKER
    TOTAL_STAKED
    TOTAL_UNSTAKED
    NET_STAKE_VOLUME
    1
    0x2cd78ad719a8c74898c5283f5bc70920d8a061fd
    		 1,754,005 5 1,754,000
    2
    0x187b6ab771482ccfee7cd2c35176080631082368
    		 1,500,454 0 1,500,454
    3
    0x5020c6eb0fe5321071942847b56349a68c7342dd
    		 966,812 0 966,812
    4
    0xf7121a3616752e29ced0a04ae5df6d76335ada0c
    		 850,000 508,466 341,534
    5
    0xbcade668402fbce9af08ebd5012d2a729fbbb9fe
    		 752,000 501,401 250,599
    6
    0x23439166feec267f1a66e2815369a02f7b7920c4
    		 500,000 0 500,000
    7
    0x63e7549ea16f52bcb0415b308f17f3fee6415138
    		 318,166 0 318,166
    8
    0xc638fc731ffd77b17c486f5ae4a85d40ad9db4fc
    		 202,021 0 202,021
    9
    0x0d6a0385a012261d6dcc1742786c8ae0910861e1
    		 200,000 0 200,000
    10
    0xa2e06c19ee14255889f0ec0ca37f6d0778d06754
    		 200,000 0 200,000
    11
    0x4fbdef91e2c63b5fc4bf3ec002b805b70c79843a
    		 199,167 0 199,167
    12
    0xeb75f366a6ffe07c0ff12cf5ea01087bddb05dfb
    		 149,850 0 149,850
    13
    0xb5360072a6252bba9844280b24cc00337ed2c551
    		 146,839 0 146,839
    14
    0xd3d835243dd15ee1396e14437a56b672e5baf07b
    		 104,181 0 104,181
    15
    0x247c7b87bfa5c6f1638f83fb988f03b036c0dc55
    		 80,171 0 80,171
    16
    0x0e3359c346553aa1304420424f9251c4928f21b4
    		 67,723 0 67,723
    17
    0xdbcb594b62f5aba69a9575da938a93286836228b
    		 51,631 0 51,631
    18
    0x898170d98cba3213399a72b266a39fc93984b54c
    		 50,105 0 50,105
    19
    0x91eb12839003631e3a1550f0b4b59148be822dca
    		 50,000 0 50,000
    20
    0x7afab8a8b8c6b6c5c2bb1035e64c2d4f7f84e299
    		 49,616 10,020 39,596
    ...
    255
    26KB
    5s