hessNew Vs. Old
Updated 2024-08-15Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
›
⌄
with new as ( select min(block_timestamp) as min_date,
tx_signer as user
from near.core.fact_transactions a join near.core.dim_address_labels b on a.tx_receiver = b.address
where tx_signer not in (select address from near.core.dim_address_labels)
and TX_SUCCEEDED = 'TRUE'
group by 2)
,
new_user as ( select DISTINCT user
from new
where min_date::date >= '2024-01-01')
select trunc(block_timestamp,'week') as weekly,
case when tx_signer in (select user from new_user) then 'New Users' else 'Old Users'end as type,
count(DISTINCT tx_hash) as total_tx
from near.core.fact_transactions a join near.core.dim_address_labels b on a.tx_receiver = b.address
where tx_signer not in (select address from near.core.dim_address_labels)
and TX_SUCCEEDED = 'TRUE'
and block_timestamp::date >= '2024-01-01'
group by 1,2
QueryRunArchived: QueryRun has been archived