Cipher009-CpUJ5k2024-03-02 04:17 AM
    WITH address_balances AS (
    SELECT
    CASE
    WHEN direction = 'to' THEN "to"
    ELSE "from"
    END AS address,
    SUM(CASE
    WHEN direction = 'to' THEN CAST(value AS DECIMAL(38, 0))
    ELSE -CAST(value AS DECIMAL(38, 0))
    END) AS total_balance
    FROM
    {{chain}},
    LATERAL (
    VALUES ('to'), ('from')
    ) AS tx_direction(direction)
    WHERE
    contract_address = {{contract_address}}
    AND (direction = 'to' OR direction = 'from')
    GROUP BY address
    HAVING SUM(CASE
    WHEN direction = 'to' THEN value
    ELSE -value
    END) > 0
    ),
    ens_agg AS (
    SELECT
    address AS ens_address,
    MAX(name) AS ens_name
    FROM ens.resolver_latest
    WHERE address IS NOT NULL
    GROUP BY address
    )
    SELECT
    ROW_NUMBER() OVER (ORDER BY ab.total_balance DESC) AS holder_rank,
    ab.address AS user_address,
    COALESCE(ea.ens_name, CAST(ab.address AS VARCHAR)) AS display_address,