TOTAL_TRANSACTIONS | TOTAL_USERS | TOTAL_VOLUME_FEE | OVERALL_AVG_FEE | TOTAL_DAYS | AVG_DAILY_TRANSACTIONS | AVG_DAILY_USERS | AVG_DAILY_VOLUME | TOTAL_TRANSACTION_CHANGE | TOTAL_USER_CHANGE | TRANSACTIONS_PER_USER | AVG_VALUE_PER_TRANSACTION | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 200578364 | 7860217 | 390131.135895261 | 0.001945031 | 169 | 1186854.224852 | 46510.159763 | 2308.468259735 | 25.518171 | 0.001945031 |
superflyuncertain-jade
Updated 2025-03-18
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
›
⌄
WITH total_metrics AS (
SELECT
COUNT(DISTINCT tx_hash) as total_transactions,
COUNT(DISTINCT from_address) as total_users,
SUM(tx_fee) as total_volume_fee,
AVG(tx_fee) as overall_avg_fee,
COUNT(DISTINCT block_timestamp::date) as total_days,
COUNT(DISTINCT tx_hash) / COUNT(DISTINCT block_timestamp::date) as avg_daily_transactions,
COUNT(DISTINCT from_address) / COUNT(DISTINCT block_timestamp::date) as avg_daily_users,
SUM(tx_fee) / COUNT(DISTINCT block_timestamp::date) as avg_daily_volume
FROM
ronin.core.fact_transactions
WHERE
tx_succeeded
AND block_timestamp >= DATEADD(day, -365, CURRENT_DATE())
)
SELECT
total_transactions,
total_users,
total_volume_fee,
overall_avg_fee,
total_days,
avg_daily_transactions,
avg_daily_users,
avg_daily_volume,
(total_transactions - LAG(total_transactions) OVER (ORDER BY total_days)) / NULLIF(LAG(total_transactions) OVER (ORDER BY total_days), 0) * 100 as total_transaction_change,
(total_users - LAG(total_users) OVER (ORDER BY total_days)) / NULLIF(LAG(total_users) OVER (ORDER BY total_days), 0) * 100 as total_user_change,
total_transactions / NULLIF(total_users, 0) as transactions_per_user,
total_volume_fee / NULLIF(total_transactions, 0) as avg_value_per_transaction
FROM
total_metrics;
Last run: about 1 month ago
1
129B
12s