hessAverage Transaction
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
›
⌄
with project as ( select trunc(block_timestamp,'month') as monthly, project_name , count(DISTINCT(signer_id)) as users,
count(DISTINCT(tx_hash)) as total_tx
from near.core.fact_actions_events_function_call a join near.core.dim_address_labels b on a.receiver_id = b.address
where signer_id not in (select address from near.core.dim_address_labels)
and block_timestamp::date >= current_date - 365
and label_type != 'chadmin' and label_type != 'fungible_token'
and project_name is not null
group by 1,2)
,
final as ( select project_name,count(DISTINCT(monthly)) as month, avg(users) as avg_users, avg(total_tx) as avg_tx
from project
group by 1
having avg_users > 1000)
,
active_projects as ( select DISTINCT project_name
from final
where month > 3)
,
final_2 as ( select trunc(block_timestamp,'month') as monthly, case when project_name in (select project_name from active_projects)
then 'Active Projects' else 'Non-Active Projects' end as type , label_type, signer_id,
count(DISTINCT(tx_hash)) as total_txs
from near.core.fact_actions_events_function_call a join near.core.dim_address_labels b on a.receiver_id = b.address
where signer_id not in (select address from near.core.dim_address_labels)
and block_timestamp::date >= current_date - 365
and label_type != 'chadmin' and label_type != 'fungible_token'
group by 1,2,3,4)
select type, label_type, count(DISTINCT(signer_id)) as users, avg(total_txs) as avg_tx_per_user,
sum(total_txs) as total_tx
from final_2
group by 1,2
Run a query to Download Data