0xHaM-dNew and Returning Users APTOS[Last 30Days]
Updated 2025-01-07
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
›
⌄
-- forked from New and Returning Users APTOS @ https://flipsidecrypto.xyz/edit/queries/87b82907-93b1-4705-9a95-69eab48d35c2
with tb1 as (
select
c.block_timestamp,
gas_used,
INITCAP(LABEL_TYPE) as Type,
iff(INITCAP(LABEL)='Move Dollar','Thala',INITCAP(LABEL)) as LABEL,
address_name,
c.block_number,
c.tx_hash,
sender,
((gas_used * gas_unit_price)/1e8) as amount
from aptos.core.fact_events a
join aptos.core.dim_labels b on address = event_address
join aptos.core.fact_transactions c using(tx_hash)
where SUCCESS=true
and tx_type = 'user_transaction'
)
, active_users_u as (
SELECT
date_trunc('day', block_timestamp) as date,
count(DISTINCT sender) as active_users
FROM tb1
GROUP BY date
)
,new as (
SELECT
date_trunc('day', first_tx) as date,
count(DISTINCT user) as new_user
FROM (
SELECT
sender as user,
min(block_timestamp) as first_tx
FROM tb1
GROUP BY 1)
GROUP BY 1
QueryRunArchived: QueryRun has been archived