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'

)

SELECT

trunc(block_timestamp,'week') as weekly,

label_type,

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,

sum(users_count) over (partition BY label_type ORDER BY weekly) as cum_users

FROM

tbl1

WHERE

label_type is not null

GROUP BY 1,2

ORDER BY 1 desc