t1.block_timestamp,

project_name,

label_type,

tx_signer,

transaction_fee,

gas_used

near.core.fact_transactions t1

LEFT OUTER JOIN near.core.dim_address_labels t2 on t1.tx_receiver = t2.address

block_timestamp > current_date - {{ days }}

AND TX_SUCCEEDED= 'TRUE'

and label_type not in ('token','cex','chadmin','operator')

)

SELECT

project_name,

count(*) as transactions_count,

count(DISTINCT tx_signer) AS users_count,

sum(transaction_fee / power(10, 24)) AS tx_fee_near,

sum(gas_used/ pow(10,12)) AS gas_usd,

transactions_count / users_count AS avg_txs_per_user

FROM

tbl1

WHERE

project_name is not null

and project_name!='usdc'

GROUP BY 1

ORDER BY 3 desc

limit 10