Week | New Traders | Returning Traders | Weekly Traders | |
|---|---|---|---|---|
1 | 2025-03-17 00:00:00.000 | 2 | 69 | 71 |
2 | 2025-03-10 00:00:00.000 | 119 | 340 | 459 |
3 | 2025-03-03 00:00:00.000 | 212 | 377 | 589 |
4 | 2025-02-24 00:00:00.000 | 164 | 360 | 524 |
5 | 2025-02-17 00:00:00.000 | 193 | 316 | 509 |
6 | 2025-02-10 00:00:00.000 | 216 | 312 | 528 |
7 | 2025-02-03 00:00:00.000 | 206 | 347 | 553 |
8 | 2025-01-27 00:00:00.000 | 403 | 413 | 816 |
9 | 2025-01-20 00:00:00.000 | 377 | 340 | 717 |
10 | 2025-01-13 00:00:00.000 | 326 | 318 | 644 |
11 | 2025-01-06 00:00:00.000 | 109 | 236 | 345 |
12 | 2024-12-30 00:00:00.000 | 52 | 196 | 248 |
13 | 2024-12-23 00:00:00.000 | 83 | 195 | 278 |
14 | 2024-12-16 00:00:00.000 | 260 | 196 | 456 |
15 | 2024-12-09 00:00:00.000 | 175 | 132 | 307 |
16 | 2024-12-02 00:00:00.000 | 119 | 80 | 199 |
17 | 2024-11-25 00:00:00.000 | 166 | 87 | 253 |
18 | 2024-11-18 00:00:00.000 | 122 | 95 | 217 |
19 | 2024-11-11 00:00:00.000 | 95 | 85 | 180 |
20 | 2024-11-04 00:00:00.000 | 48 | 83 | 131 |
i_dan$XSGD: New vs returning traders
Updated 2025-03-17
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 first_tx AS (
SELECT
origin_from_address
, MIN(block_timestamp) AS first_week
FROM avalanche.defi.ez_dex_swaps
WHERE token_in = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E')
OR token_out = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E')
GROUP BY 1
),
user_activity AS (
SELECT
t.origin_from_address
, DATE_TRUNC('week', t.block_timestamp) AS activity_week
, DATE_TRUNC('week', f.first_week) AS first_tx_week
FROM avalanche.defi.ez_dex_swaps t
JOIN first_tx f ON t.origin_from_address = f.origin_from_address
WHERE token_in = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E')
OR token_out = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E')
)
SELECT
activity_week AS "Week"
, COUNT(DISTINCT CASE WHEN activity_week = first_tx_week THEN origin_from_address END) AS "New Traders"
, COUNT(DISTINCT CASE WHEN activity_week > first_tx_week THEN origin_from_address END) AS "Returning Traders"
, COUNT(DISTINCT origin_from_address) AS "Weekly Traders"
FROM user_activity
GROUP BY 1
ORDER BY 1 DESC
-- XSGD - 0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E
Last run: about 1 month ago
28
1KB
3s