ArioKaia - Users Stats by Types
Updated 2024-10-02Copy Reference Fork
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 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('month', block_timestamp) as date
, iff(date_trunc(month, first_timestamp) = date_trunc(month, 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
category,
min(users) as minimum,
round(avg(users), 0) as average,
median(users) as median,
max(users) as maximum
from
aggregated
QueryRunArchived: QueryRun has been archived