SandeshG3 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
›
⌄
-- Description:
-- This query calculates token balances on both the Arbitrum, Base and Ethereum chains, combines them,
-- and returns the number of unique token holders per day, along with their classification based on labels.
WITH balances_table AS (
-- Arbitrum chain balance calculation
WITH arb AS (
-- CTE for all unique dates within the specified date range
WITH dates AS (
SELECT
date_trunc('day', date_day) AS date
FROM ethereum.core.dim_dates
WHERE date_day >= '2024-04-03'
AND date_day <= date_day
GROUP BY date
),
-- CTE to calculate daily buy and sell amounts on Arbitrum
buy_sell_table AS (
(
SELECT
date_trunc('day', block_timestamp) AS date,
to_address AS address,
amount
FROM arbitrum.core.ez_token_transfers
WHERE 1=1 -- Placeholder for easy addition of conditions
AND block_number >= '197169618' -- date : Apr-03-2024
AND contract_address = LOWER('0xc24a365a870821eb83fd216c9596edd89479d8d7') -- token address of G3
)
UNION ALL
(
SELECT
date_trunc('day', block_timestamp) AS date,
from_address AS address,
-1 * amount -- Negative amount for sell transactions
FROM arbitrum.core.ez_token_transfers