flipside

    05 Lorenz Curve of Staked Assets
    FatemeTheLady05 Lorenz Curve of Staked Assets
    Updated 7 days ago
    WITH user_stakes AS (
    SELECT
    decoded_log:owner::STRING AS user_address,
    (SUM(CASE WHEN EVENT_NAME = 'Deposit' THEN decoded_log:assets::NUMBER ELSE 0 END) -
    SUM(CASE WHEN EVENT_NAME = 'Withdraw' THEN decoded_log:assets::NUMBER ELSE 0 END)) / 1e18 AS net_staked
    FROM avalanche.core.fact_decoded_event_logs
    WHERE contract_address = LOWER('0x06d47f3fb376649c3a9dafe069b3d6e35572219e')
    AND EVENT_NAME IN ('Deposit', 'Withdraw')
    GROUP BY user_address
    HAVING net_staked > 0
    ),

    ordered_stakes AS (
    SELECT
    user_address,
    net_staked,
    SUM(net_staked) OVER () AS total_staked,
    ROW_NUMBER() OVER (ORDER BY net_staked) AS rank_asc,
    COUNT(*) OVER () AS total_users
    FROM user_stakes
    ),

    lorenz_curve AS (
    SELECT
    rank_asc * 100 / total_users AS cumulative_users_percent,
    100* SUM(net_staked) OVER (ORDER BY net_staked ROWS UNBOUNDED PRECEDING) / total_staked AS cumulative_staked_percent
    FROM ordered_stakes
    )

    SELECT * FROM lorenz_curve;

    Last run: 7 days ago
    CUMULATIVE_USERS_PERCENT
    CUMULATIVE_STAKED_PERCENT
    1
    		0.9174312.5128e-8
    2
    		0.4587165.0256e-8
    3
    		1.3761471.75895e-7
    4
    		2.2935783.01534e-7
    5
    		2.7522944.27174e-7
    6
    		3.2110095.52813e-7
    7
    		4.5871566.78453e-7
    8
    		3.6697258.04092e-7
    9
    		4.128449.29731e-7
    10
    		1.8348620.000001055371
    11
    		6.8807340.000001306649
    12
    		6.4220180.000001557928
    13
    		5.9633030.000001809207
    14
    		5.5045870.000002060485
    15
    		5.0458720.000002311764
    16
    		7.339450.000002624606
    17
    		7.7981650.000002954359
    18
    		8.2568810.000004156313
    19
    		9.1743120.000005412707
    20
    		8.7155960.0000066691
    ...
    218
    6KB
    3s