freemartianDaily Active Users Versus New Users
Updated 2022-12-06
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 source as (
select
tx_sender,
min(block_timestamp::date) as first_day
from terra.core.fact_transactions
where TX_SUCCEEDED = 'TRUE'
group by tx_sender),
actives as (
select
TX_SENDER,
count(tx_id) as count
from terra.core.fact_transactions
group by 1
having count >= 5
)
select
'New_Users' as label,
date_trunc('week', first_day) as TIME,
count(tx_sender) as count
from source
where first_day >= CURRENT_DATE - {{Past_X_Days}}
group by label, time
UNION
select
'Active_Users' as label,
date_trunc('week', block_timestamp::date) as TIME,
count(distinct tx_sender) as count
from terra.core.fact_transactions
where TX_SUCCEEDED = 'TRUE'
and block_timestamp >= CURRENT_DATE - {{Past_X_Days}}
and tx_sender in (select TX_SENDER from actives)
group by label, TIME
Run a query to Download Data