Tobi_1unknown-amaranth
Updated 2024-09-28
99
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
›
⌄
WITH token_transfers AS (
SELECT
BLOCK_TIMESTAMP::DATE AS transfer_date,
TO_ADDRESS AS holder_address
FROM
base.core.ez_token_transfers
WHERE
CONTRACT_ADDRESS = '0x532f27101965dd16442e59d40670faf5ebb142e4'
AND RAW_AMOUNT > 0 -- Only positive transfers
),
-- Step 1: Find the first time each address appears as a holder
first_time_holders AS (
SELECT
holder_address,
MIN(transfer_date) AS first_transfer_date
FROM
token_transfers
GROUP BY
holder_address
),
-- Step 2: Calculate cumulative unique holders by counting new holders each day
cumulative_holders AS (
SELECT
first_transfer_date AS transfer_date,
COUNT(*) AS new_holders
FROM
first_time_holders
GROUP BY
first_transfer_date
),
-- Step 3: Calculate the cumulative total holders
daily_cumulative_holders AS (
SELECT
QueryRunArchived: QueryRun has been archived