TOKEN_ADDRESS | SYMBOL | POOLED_BALANCE | TOKEN_PRICE | USD_VALUE | PRICE_TIMESTAMP | |
|---|---|---|---|---|---|---|
1 | 0xc891eb4cbdeff6e073e859e987815ed1505c2acd | EUROC | 1220968.891636 | 1.11 | 1355275.47 | 2025-05-13 00:00:00.000 |
gigiokobainclined-amethyst
Updated 2025-05-13
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 pool_transfers AS (
SELECT
contract_address,
symbol,
SUM(CASE
WHEN to_address = '0x3e230575674855f161983e8ddc08e82c9d6f9c45' THEN amount
WHEN from_address = '0x3e230575674855f161983e8ddc08e82c9d6f9c45' THEN -amount
ELSE 0
END) AS current_balance
FROM avalanche.core.ez_token_transfers
WHERE (to_address = '0x3e230575674855f161983e8ddc08e82c9d6f9c45'
OR from_address = '0x3e230575674855f161983e8ddc08e82c9d6f9c45')
AND block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP())
AND contract_address != '0xd19f0fd139d78a56d4d427061af5f7929cdc0a3b' -- Exclude the token
GROUP BY contract_address, symbol
)
SELECT
pt.contract_address AS token_address,
pt.symbol,
pt.current_balance AS pooled_balance,
p.price AS token_price,
ROUND(pt.current_balance * p.price, 2) AS usd_value,
p.hour AS price_timestamp
FROM pool_transfers pt
LEFT JOIN avalanche.price.ez_prices_hourly p
ON LOWER(pt.contract_address) = LOWER(p.token_address)
AND p.hour = (
SELECT MAX(hour)
FROM avalanche.price.ez_prices_hourly
WHERE LOWER(token_address) = LOWER(pt.contract_address)
)
WHERE pt.current_balance > 0
ORDER BY usd_value DESC;
Last run: 21 days ago
1
113B
3s