Date | New Users | Returning Users | Daily Users | |
|---|---|---|---|---|
1 | 2024-11-28 00:00:00.000 | 2 | 0 | 2 |
2 | 2024-11-29 00:00:00.000 | 1 | 2 | 3 |
3 | 2024-12-01 00:00:00.000 | 1 | 0 | 1 |
4 | 2024-12-02 00:00:00.000 | 6 | 3 | 9 |
5 | 2024-12-03 00:00:00.000 | 208 | 3 | 211 |
6 | 2024-12-04 00:00:00.000 | 7 | 20 | 27 |
7 | 2024-12-05 00:00:00.000 | 8 | 16 | 24 |
8 | 2024-12-06 00:00:00.000 | 13 | 13 | 26 |
9 | 2024-12-07 00:00:00.000 | 2 | 6 | 8 |
10 | 2024-12-08 00:00:00.000 | 6 | 4 | 10 |
11 | 2024-12-09 00:00:00.000 | 13 | 16 | 29 |
12 | 2024-12-10 00:00:00.000 | 10 | 21 | 31 |
13 | 2024-12-11 00:00:00.000 | 19 | 24 | 43 |
14 | 2024-12-12 00:00:00.000 | 9 | 28 | 37 |
15 | 2024-12-13 00:00:00.000 | 9 | 22 | 31 |
16 | 2024-12-14 00:00:00.000 | 9 | 7 | 16 |
17 | 2024-12-15 00:00:00.000 | 6 | 10 | 16 |
18 | 2024-12-16 00:00:00.000 | 11 | 32 | 43 |
19 | 2024-12-17 00:00:00.000 | 16 | 33 | 49 |
20 | 2024-12-18 00:00:00.000 | 16 | 36 | 52 |
i_danSwellChain: New Vs Returning Users
Updated 8 days ago
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
›
⌄
WITH first_tx AS (
SELECT
from_address
, MIN(block_timestamp) AS first_date
FROM swell.core.fact_transactions
WHERE from_address != '0xdeaddeaddeaddeaddeaddeaddeaddeaddead0001'
AND from_address != '0x339d413ccefd986b1b3647a9cfa9cbbe70a30749'
GROUP BY 1
),
user_activity AS (
SELECT
t.from_address
, DATE_TRUNC('day', t.block_timestamp) AS activity_date
, DATE_TRUNC('day', f.first_date) AS first_tx_date
FROM swell.core.fact_transactions t
JOIN first_tx f ON t.from_address = f.from_address
)
SELECT
activity_date AS "Date"
, COUNT(DISTINCT CASE WHEN activity_date = first_tx_date THEN from_address END) AS "New Users"
, COUNT(DISTINCT CASE WHEN activity_date > first_tx_date THEN from_address END) AS "Returning Users"
, COUNT(DISTINCT from_address) AS "Daily Users" --
FROM user_activity
GROUP BY 1
ORDER BY 1
Last run: 8 days ago
...
125
5KB
5s