Cipher009-CpUJ5k2024-03-02 04:17 AM
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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,