SniperTotal transaction fess by project name copy
Updated 2024-08-06
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
36
›
⌄
With tbl1 AS (
SELECT
t1.block_timestamp,
project_name,
address,
label_type,
tx_signer,
transaction_fee,
gas_used
FROM
near.core.fact_transactions t1
LEFT OUTER JOIN near.core.dim_address_labels t2 on t1.tx_receiver = t2.address
WHERE
block_timestamp > current_date - {{ days }}
AND TX_SUCCEEDED= 'TRUE'
)
SELECT
trunc(block_timestamp,'week') as weekly,
project_name,
address,
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
label_type LIKE '{{project_type}}'
AND
project_name is not null
GROUP BY 1,2,3
-- limit 10
-- ORDER BY 4 desc
QueryRunArchived: QueryRun has been archived