feyikemiwitty-teal copy
Updated 2024-08-02
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 New_Users_stats AS (
SELECT
Authorizers[0] AS user,
MIN(block_timestamp) AS first_tx_timestamp
FROM
flow.core.fact_transactions
WHERE block_timestamp::DATE >= '2024-01-01'
AND Tx_succeeded = 'True'
GROUP BY 1
)
--All_users AS(
SELECT
DATE_TRUNC('day', first_tx_timestamp) AS Date,
'New_users' AS Type,
COUNT(DISTINCT user) AS users
FROM New_Users_stats
GROUP BY 1
UNION ALL
SELECT
DATE_TRUNC('day', block_timestamp) AS Date,
'Active_Users' AS Type,
COUNT(DISTINCT Authorizers[0]) AS Users
FROM
flow.core.fact_transactions
WHERE block_timestamp::DATE >= '2024-01-01'
AND Tx_succeeded = 'True'
GROUP BY 1
-- )
-- SELECT
-- (SELECT COUNT(DISTINCT users) FROM All_Users WHERE type = 'Active_Users') AS total_users,
-- (SELECT COUNT(DISTINCT users) FROM All_Users WHERE type = 'New_Users') AS total_new_users,
-- FROM All_Users
QueryRunArchived: QueryRun has been archived