SandeshG3 Balance Individual current snapshot
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
›
⌄
⌄
/*
This query analyzes token balances and categorizes users based on their holdings and rank. It combines data from staking, buying/selling on Arbitrum, Base and Ethereum, and NFT ownership to create a leaderboard and classify users.
Key CTEs:
1. `staked_balance`: Tracks staking and unstaking transactions on Arbitrum.
2. `balance_table`: Monitors token purchases/sales on Arbitrum and Ethereum.
3. `complete_balance`: Combines `staked_balance` and `balance_table`.
4. `results_table`: Aggregates balances and assigns user ranks.
5. `holders_table`: Identifies current NFT holders on Ethereum.
6. `temp`: Merges leaderboard data with NFT ownership, categorizing users.
*/
WITH staked_balance AS (
WITH dates AS (
SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date
FROM arbitrum.core.fact_transactions
WHERE date >= '2024-04-09'
AND date <= CURRENT_DATE
GROUP BY 1
),
stake AS (
SELECT *, 'stake' AS action, amount AS staked_amount
FROM arbitrum.core.ez_token_transfers
WHERE block_timestamp >= '2024-04-09 00:00:00.000'
AND origin_function_signature = '0xa694fc3a'
AND contract_address = '0xc24a365a870821eb83fd216c9596edd89479d8d7'
),
unstake AS (
SELECT *, 'unstake' AS action, -amount AS staked_amount
FROM arbitrum.core.ez_token_transfers
WHERE block_timestamp >= '2024-04-09 00:00:00.000'
AND origin_function_signature = '0x2e1a7d4d'
AND contract_address = '0xc24a365a870821eb83fd216c9596edd89479d8d7'
),
combined AS (
SELECT block_number, block_timestamp, tx_hash, origin_from_address, staked_amount FROM stake
QueryRunArchived: QueryRun has been archived