USER_ADDRESS | G3_BALANCE | NFTS_HELD | LEADERBOARD_RANK | |
|---|---|---|---|---|
1 | 0xfa98bcb727c284897f4fd75beabc9201011d0e93 | 743977.904257471 | 1 | 29 |
2 | 0x2afdc799d2b6f5032b02f4555a11700ff85a12b0 | 323451.517304633 | 1 | 39 |
3 | 0xe11c6aaa0413512345dea2e4aa34c297c3349602 | 169153.363208813 | 1 | 52 |
4 | 0x7f6f2be1228cd0fe2bcff4d945c5b9c5efd430f1 | 162100.553457959 | 1 | 54 |
5 | 0x4face332000c084a4cc5db267abdb9dc90b99fb4 | 142541.608204133 | 1 | 59 |
6 | 0xa75b063bd4f6c15eac9746d648dc0479218b5ff7 | 141648.76 | 1 | 60 |
7 | 0x4de0b45ae609135b8c80010b962e67703e8375e8 | 113010.182018396 | 1 | 71 |
8 | 0x24c8dba65047c0784285b53f9765a7f20276b099 | 102872.826038458 | 3 | 76 |
9 | 0x7405fe24003a50e4f4117d35e9b5a9f5e512fede | 87086.790998476 | 1 | 93 |
10 | 0xb58b41a84a997570f03fd2bb83564146c87d9ad0 | 85759.627874441 | 1 | 96 |
11 | 0xafc30f8cb2c88177a8716b905859c01b283ad79e | 73192.175040855 | 1 | 107 |
12 | 0x0616a625c7c2d1707e873a6e4c7f68f512de952a | 69628.194471023 | 1 | 111 |
13 | 0x7ce38f46ba89178cc8772346b49e1d68dfafd946 | 69611.155706973 | 1 | 112 |
14 | 0x52a75ad5d449eefebd3a3bbee698eafd861b734b | 68949.1225206 | 1 | 113 |
15 | 0xa6af6da8be9c78b41693fe3b404208c36258b233 | 67889.951057949 | 1 | 114 |
16 | 0xc00667d8b00f35b3565a5c4458dff1cd718e3527 | 54613.039930617 | 1 | 135 |
17 | 0xe7d38db2c9b28878a64d5de53bf01d679a9865c4 | 52069.876113265 | 2 | 142 |
18 | 0x77eba8affecffb77f57c9e7f72bc28d913e69f21 | 42445 | 1 | 168 |
19 | 0xbd6602f9f5bbc850200b1460a57aa5d8ade18224 | 40252.945810208 | 1 | 173 |
20 | 0x6a80983a06faeb3a4e4c6b2f7ab99407d3fec8cb | 35683.864685287 | 1 | 184 |
SandeshG3 NFT + G3 token holders
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 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
Last run: 3 months ago
...
313
19KB
124s