SandeshNekodex Current token balances
Updated 2024-09-19
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
›
⌄
⌄
/*
This query calculates the current token balances for users on the Optimism blockchain.
It tracks both outgoing and incoming transfers, combines them, and then calculates the final balance for each user and token.
Key features:
1. Separates outgoing and incoming transfers
2. Includes transfers with positive USD value or for a specific token contract
3. Combines all transfers and calculates net balances
4. Filters for only positive balances in the final result
*/
with users as
(
WITH swappers AS
-- Identify 'trader' users based on specific contract interactions
(
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'
)
, only_claimers AS
(
-- Selecting 'claimer' users who received token transfers (based on event logs).
SELECT
block_number
, block_timestamp
, tx_hash
, ('0x' || substr(topics [2], -40)) AS user_address
QueryRunArchived: QueryRun has been archived