TX_DATE | NEW_USERS | ACTIVE_USERS | TOTAL_USERS | |
|---|---|---|---|---|
1 | 2024-02-18 00:00:00.000 | 69837 | 69837 | 69837 |
2 | 2024-02-19 00:00:00.000 | 13449 | 70301 | 70301 |
3 | 2024-02-20 00:00:00.000 | 11879 | 71899 | 71899 |
4 | 2024-02-21 00:00:00.000 | 8745 | 65036 | 65036 |
5 | 2024-02-22 00:00:00.000 | 7951 | 66516 | 66516 |
6 | 2024-02-23 00:00:00.000 | 7175 | 68763 | 68763 |
7 | 2024-02-24 00:00:00.000 | 6302 | 66907 | 66907 |
8 | 2024-02-25 00:00:00.000 | 7589 | 67329 | 67329 |
9 | 2024-02-26 00:00:00.000 | 8628 | 70512 | 70512 |
10 | 2024-02-27 00:00:00.000 | 12810 | 78456 | 78456 |
11 | 2024-02-28 00:00:00.000 | 15218 | 79782 | 79782 |
12 | 2024-02-29 00:00:00.000 | 8209 | 72287 | 72287 |
13 | 2024-03-01 00:00:00.000 | 7749 | 72460 | 72460 |
14 | 2024-03-02 00:00:00.000 | 8493 | 75425 | 75425 |
15 | 2024-03-03 00:00:00.000 | 6774 | 73764 | 73764 |
16 | 2024-03-04 00:00:00.000 | 7650 | 74475 | 74475 |
17 | 2024-03-05 00:00:00.000 | 6921 | 75664 | 75664 |
18 | 2024-03-06 00:00:00.000 | 8874 | 76616 | 76616 |
19 | 2024-03-07 00:00:00.000 | 6212 | 80202 | 80202 |
20 | 2024-03-08 00:00:00.000 | 5871 | 79225 | 79225 |
picasototal_users
Updated 2025-02-18
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
WITH user_activity AS (
SELECT
DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS tx_date,
ACCOUNT
FROM stellar.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= DATEADD(YEAR, -1, CURRENT_DATE)
),
first_tx AS (
SELECT
ACCOUNT,
MIN(tx_date) AS first_tx_date
FROM user_activity
GROUP BY ACCOUNT
)
SELECT
ua.tx_date,
COUNT(DISTINCT CASE WHEN ft.first_tx_date = ua.tx_date THEN ua.ACCOUNT END) AS new_users,
COUNT(DISTINCT ua.ACCOUNT) AS active_users,
COUNT(DISTINCT ft.ACCOUNT) AS total_users
FROM user_activity ua
LEFT JOIN first_tx ft ON ua.ACCOUNT = ft.ACCOUNT
GROUP BY ua.tx_date
ORDER BY ua.tx_date;
Last run: 27 days ago
...
367
16KB
509s