ArioKaia - New Users in last 30 days
Updated 2024-10-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
›
⌄
with all_txs as (
select
block_timestamp
,from_address as user_address
from kaia.core.fact_transactions
where tx_succeeded = 'TRUE'
group by 1,2
),
first_txs as (
select
user_address
, min(block_timestamp) as first_timestamp
from kaia.core.fact_transactions
inner join (select distinct user_address from all_txs) users
on fact_transactions.from_address = users.user_address
where tx_succeeded = 'TRUE'
group by 1
),
aggregated as (
select
date_trunc('day', block_timestamp) as date
, iff(date_trunc(day, first_timestamp) = date_trunc(day, block_timestamp), 'New', 'Old') as category
, count(distinct user_address) as users
from all_txs
left join first_txs
using(user_address)
group by 1,2
)
select
sum(users) as N_New_users
from
aggregated
where date >= current_timestamp - interval '30 days'
and category = 'New'
QueryRunArchived: QueryRun has been archived