ShapeShiftTHORChain : Total Current Users, New Users, Returning User (FRM 2024-01-01 ) copy
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
›
⌄
-- forked from Terrius / THORChain : Total Current Users, New Users, Returning User (FRM 2024-01-01 ) @ https://flipsidecrypto.xyz/Terrius/q/dN2cHL539Fdm/thorchain-total-current-users-new-users-returning-user-frm-2024-01-01
WITH first_time_users AS (
SELECT
DISTINCT from_address AS Users,
MIN(block_timestamp) AS first_date
FROM thorchain.defi.fact_swaps
WHERE block_timestamp >= '2024-01-01'
GROUP BY 1
),
new_users AS (
SELECT
DISTINCT Users AS new_users
FROM first_time_users
),
returning_users AS (
SELECT
DISTINCT t.from_address AS returning_users
FROM thorchain.defi.fact_swaps t
JOIN first_time_users f ON t.from_address = f.Users
WHERE t.block_timestamp > f.first_date
),
usersb AS (
SELECT
DISTINCT from_address AS current_users
FROM thorchain.defi.fact_swaps
)
SELECT
(SELECT new_users FROM new_users) AS total_new_users,
(SELECT returning_users FROM returning_users) AS total_returning_users,
(SELECT current_users FROM usersb) AS total_current_users;
Run a query to Download Data