TOTAL_TRANSACTIONS | TOTAL_UNIQUE_USERS | TOTAL_CONTRACTS_DEPLOYED | AVG_DAILY_TRANSACTIONS | PEAK_DAILY_TRANSACTIONS | AVG_DAILY_ACTIVE_USERS | AVG_D1_RETENTION_RATE | AVG_D7_RETENTION_RATE | AVG_D30_RETENTION_RATE | AVG_FEE_PER_TRANSACTION | TOTAL_FEES_PAID | AVG_TX_SUCCESS_RATE | TX_GROWTH_RATE | USER_GROWTH_RATE | AVG_TXS_PER_ACTIVE_USER | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 1268603 | 1482 | 188 | 45307.25 | 47286 | 133.285714 | 21.072703214286 | 13.143787071429 | 0 | 3.931103033e-7 | 0.4987009101 | 99.662157607143 | 6.576812 | 149.038462 | 349.880521892857 |
picasofebarary
Updated 8 days ago
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_data AS (
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day,
COUNT(DISTINCT TX_HASH) AS daily_transactions,
COUNT(DISTINCT FROM_ADDRESS) AS daily_active_users,
SUM(TX_FEE) AS daily_fees
FROM
boba.core.fact_transactions
WHERE block_timestamp::date >= '2025-02-01' AND block_timestamp::date < '2025-03-01'
GROUP BY
1
),
first_seen AS (
SELECT
FROM_ADDRESS,
MIN(BLOCK_TIMESTAMP) AS first_tx_timestamp
FROM
boba.core.fact_transactions
WHERE block_timestamp::date >= '2025-02-01' AND block_timestamp::date < '2025-03-01'
GROUP BY
1
),
user_retention AS (
SELECT
fs.FROM_ADDRESS,
DATE_TRUNC('day', fs.first_tx_timestamp) AS cohort_day,
COUNT(DISTINCT DATE_TRUNC('day', t.BLOCK_TIMESTAMP)) AS active_days,
MAX(DATEDIFF('day', fs.first_tx_timestamp, t.BLOCK_TIMESTAMP)) AS days_retained
FROM
first_seen fs
JOIN boba.core.fact_transactions t ON fs.FROM_ADDRESS = t.FROM_ADDRESS
WHERE block_timestamp::date >= '2025-02-01' AND block_timestamp::date < '2025-03-01'
GROUP BY
1, 2
),
cohort_retention AS (
Last run: 8 days ago
1
161B
11s