SandeshNekodex Current token balances total
Updated 2025-01-13
999
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
›
⌄
⌄
/*
Comprehensive Description:
This query calculates and analyzes current token balances for specific users across multiple blockchain networks
(Optimism, Arbitrum, Ethereum, and Base). It tracks both outgoing and incoming transfers, combines them, and then
calculates the final balance for each user and token. The query uses Common Table Expressions (CTEs) extensively
to modularize the logic and improve readability.
Key features:
1. Identifies specific users based on contract interactions and token claims
2. Tracks token transfers across multiple blockchains
3. Separates outgoing and incoming transfers
4. Includes transfers with positive USD value or for specific token contracts
5. Combines all transfers and calculates net balances
6. Joins with price data to calculate USD values of token balances
7. Filters for only positive balances in the final result
8. Calculates running total of USD balances
*/
-- CTE to identify specific users based on contract interactions and token claims
WITH users AS (
-- Subquery to identify 'trader' users based on specific contract interactions
WITH swappers AS (
SELECT
block_number
, block_timestamp
, tx_hash
, to_address AS user_address
, 'trader' AS type
FROM optimism.core.fact_traces
WHERE 1 = 1
AND block_timestamp >= '2024-04-12'
AND identifier = 'CREATE2_0_0_0_0'
AND from_address = '0x6723b44abeec4e71ebe3232bd5b455805badd22f' -- contract deployer
AND TX_STATUS = 'SUCCESS'
)
-- Subquery to identify 'claimer' users who received token transfers
QueryRunArchived: QueryRun has been archived