TOTAL_TX_COUNT | TOTAL_USER_COUNT | AVG_DAILY_TX | AVG_MONTHLY_TX | PEAK_DAILY_TX | AVG_DAILY_USERS | AVG_MONTHLY_USERS | AVG_TX_FEE | TOTAL_FEES | TX_GROWTH_RATE | USER_GROWTH_RATE | AVG_TX_PER_USER | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 15287146 | 37710 | 12540.726825 | 372857.219512 | 63465 | 159.965546 | 1755.04878 | 0.00007247813143 | 1107.983849438 | 91878.26087 | 39257.142857 | 124.038491513536 |
HadisehTotal
Updated 2025-02-28
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 daily_metrics AS (
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transaction_day,
COUNT(DISTINCT TX_HASH) AS daily_tx_count,
COUNT(DISTINCT FROM_ADDRESS) AS daily_user_count,
SUM(TX_FEE) AS daily_fee_total
FROM boba.core.fact_transactions
GROUP BY 1
),
monthly_metrics AS (
SELECT
DATE_TRUNC('month', BLOCK_TIMESTAMP) AS transaction_month,
COUNT(DISTINCT TX_HASH) AS monthly_tx_count,
COUNT(DISTINCT FROM_ADDRESS) AS monthly_user_count,
SUM(TX_FEE) AS monthly_fee_total
FROM boba.core.fact_transactions
GROUP BY 1
),
user_first_tx AS (
SELECT
FROM_ADDRESS AS user_address,
MIN(BLOCK_TIMESTAMP) AS first_transaction_date
FROM boba.core.fact_transactions
GROUP BY 1
),
user_activity AS (
SELECT
uft.user_address,
DATE_TRUNC('day', uft.first_transaction_date) AS cohort_period,
COUNT(DISTINCT DATE_TRUNC('day', t.BLOCK_TIMESTAMP)) AS active_day_count,
MAX(DATEDIFF('day', uft.first_transaction_date, t.BLOCK_TIMESTAMP)) AS retention_days
FROM user_first_tx uft
JOIN boba.core.fact_transactions t
ON uft.user_address = t.FROM_ADDRESS
GROUP BY 1, 2
),
Last run: 21 days ago
1
147B
6s