hessProjects Overview ( Sorted Based on Users )
Updated 2023-07-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
with call_contracts as ( select project_name, count(DISTINCT(block_timestamp::date)) as active_days,count(method_name) as call_contracts, count(DISTINCT(tx_hash)) as transactions, count(DISTINCT(signer_id)) as users
from near.core.fact_actions_events_function_call a join near.core.dim_address_labels b on a.receiver_id = b.address
where block_timestamp::date >= '{{Start_Date}}' and block_timestamp::date <= '{{End_Date}}'
group by 1)
,
volume as ( select c.project_name,sum(deposit/pow(10,24)) as near_amount, avg(deposit/pow(10,24)) as avg_amount, sum(TRANSACTION_FEE/pow(10,24)) as gas_amount
from NEAR.core.fact_transfers a join near.core.dim_address_labels c on a.tx_receiver = c.address
where block_timestamp::date >= '{{Start_Date}}' and block_timestamp::date <= '{{End_Date}}'
and tx_receiver != 'token.sweat'
and STATUS = 'true'
group by 1)
select a.project_name, active_days, users, transactions, call_contracts, near_amount, avg_amount, gas_amount
from volume a join call_contracts b on a.project_name = b.project_name
where a.project_name != 'cex'
order by 3 desc