TRADE_DATE | new traders | returning traders | total traders | |
|---|---|---|---|---|
1 | 2024-09-01 00:00:00.000 | 6467 | 0 | 6467 |
2 | 2024-09-02 00:00:00.000 | 4282 | 2025 | 6307 |
3 | 2024-09-03 00:00:00.000 | 4642 | 2727 | 7369 |
4 | 2024-09-04 00:00:00.000 | 3953 | 3001 | 6954 |
5 | 2024-09-05 00:00:00.000 | 4603 | 3697 | 8300 |
6 | 2024-09-06 00:00:00.000 | 3341 | 3774 | 7115 |
7 | 2024-09-07 00:00:00.000 | 2727 | 3346 | 6073 |
8 | 2024-09-08 00:00:00.000 | 2729 | 3588 | 6317 |
9 | 2024-09-09 00:00:00.000 | 2529 | 3663 | 6192 |
10 | 2024-09-10 00:00:00.000 | 2780 | 3881 | 6661 |
11 | 2024-09-11 00:00:00.000 | 2653 | 3912 | 6565 |
12 | 2024-09-12 00:00:00.000 | 2453 | 3901 | 6354 |
13 | 2024-09-13 00:00:00.000 | 3551 | 4366 | 7917 |
14 | 2024-09-14 00:00:00.000 | 2690 | 4149 | 6839 |
15 | 2024-09-15 00:00:00.000 | 2935 | 4740 | 7675 |
16 | 2024-09-16 00:00:00.000 | 2874 | 4594 | 7468 |
17 | 2024-09-17 00:00:00.000 | 1843 | 4014 | 5857 |
18 | 2024-09-18 00:00:00.000 | 1827 | 3985 | 5812 |
19 | 2024-09-19 00:00:00.000 | 2211 | 4313 | 6524 |
20 | 2024-09-20 00:00:00.000 | 1897 | 4212 | 6109 |
datavortexNew Vs Returning Traders
Updated 2025-03-25
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 all_traders AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS trade_date,
seller AS trader
FROM
solana.nft.fact_nft_sales
WHERE
block_timestamp BETWEEN '2024-09-01' AND '2024-09-30'
UNION ALL
SELECT
DATE_TRUNC('day', block_timestamp) AS trade_date,
purchaser AS trader
FROM
solana.nft.fact_nft_sales
WHERE
block_timestamp BETWEEN '2024-09-01' AND '2024-09-30'
),
traders_history AS (
SELECT
trader,
MIN(trade_date) AS first_trade_date
FROM
all_traders
GROUP BY
trader
)
SELECT
a.trade_date,
COUNT(DISTINCT CASE WHEN a.trade_date = t.first_trade_date THEN a.trader END) AS "new traders",
COUNT(DISTINCT CASE WHEN a.trade_date > t.first_trade_date THEN a.trader END) AS "returning traders",
Last run: 3 months ago
29
1KB
6s