gigiokobaDaily New and Returning Holders
Updated 2024-06-19
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
›
⌄
WITH FirstTransactions AS (
SELECT
swapper,
MIN(DATE(block_timestamp)) AS first_transaction_date
FROM
solana.defi.fact_swaps
WHERE
swap_from_mint = '3S8qX1MsMqRbiwKg2cQyx7nis1oHMgaCuc9c4VfvVdPN'
AND swapper IS NOT NULL
GROUP BY
swapper
),
DailyTransactions AS (
SELECT
DATE(block_timestamp) AS transaction_date,
swapper
FROM
solana.defi.fact_swaps
WHERE
swap_from_mint = '3S8qX1MsMqRbiwKg2cQyx7nis1oHMgaCuc9c4VfvVdPN'
AND swapper IS NOT NULL
)
SELECT
dt.transaction_date,
COUNT(DISTINCT CASE WHEN dt.transaction_date = ft.first_transaction_date THEN dt.swapper ELSE NULL END) AS new_holders,
COUNT(DISTINCT CASE WHEN dt.transaction_date != ft.first_transaction_date THEN dt.swapper ELSE NULL END) AS returning_holders
FROM
DailyTransactions dt
LEFT JOIN
FirstTransactions ft ON dt.swapper = ft.swapper
GROUP BY
dt.transaction_date
ORDER BY
dt.transaction_date;
QueryRunArchived: QueryRun has been archived