TRANSACTION_DAY | DAILY_TX_COUNT | DAILY_USER_COUNT | DAILY_FEE_TOTAL | AVG_TX_FEE | TX_GROWTH_RATE | USER_GROWTH_RATE | TXS_PER_USER | |
|---|---|---|---|---|---|---|---|---|
1 | 2025-01-01 00:00:00.000 | 46407 | 512 | 0.03278356588 | 7.064357937e-7 | 90.638672 | ||
2 | 2025-01-02 00:00:00.000 | 46612 | 366 | 0.0667702995 | 0.000001432470169 | 0.441744 | -28.515625 | 127.355191 |
3 | 2025-01-03 00:00:00.000 | 49461 | 705 | 0.09111743994 | 0.000001842207799 | 6.11216 | 92.622951 | 70.157447 |
4 | 2025-01-04 00:00:00.000 | 47492 | 640 | 0.03860146807 | 8.127993781e-7 | -3.980914 | -9.219858 | 74.20625 |
5 | 2025-01-05 00:00:00.000 | 48225 | 676 | 0.04723729367 | 9.795187905e-7 | 1.543418 | 5.625 | 71.338757 |
6 | 2025-01-06 00:00:00.000 | 46211 | 324 | 0.04759766831 | 0.000001030007321 | -4.176257 | -52.071006 | 142.626543 |
7 | 2025-01-07 00:00:00.000 | 48063 | 681 | 0.05147927069 | 0.000001071079015 | 4.007704 | 110.185185 | 70.577093 |
8 | 2025-01-08 00:00:00.000 | 46926 | 524 | 0.04558913398 | 9.715111873e-7 | -2.365645 | -23.054332 | 89.553435 |
9 | 2025-01-09 00:00:00.000 | 47315 | 380 | 0.04941808287 | 0.000001044448544 | 0.828965 | -27.480916 | 124.513158 |
10 | 2025-01-10 00:00:00.000 | 45179 | 370 | 0.02466410898 | 5.459197631e-7 | -4.514425 | -2.631579 | 122.105405 |
11 | 2025-01-11 00:00:00.000 | 44533 | 210 | 0.007630763547 | 1.713507634e-7 | -1.429868 | -43.243243 | 212.061905 |
12 | 2025-01-12 00:00:00.000 | 44478 | 149 | 0.007820397431 | 1.758261934e-7 | -0.123504 | -29.047619 | 298.510067 |
13 | 2025-01-13 00:00:00.000 | 47730 | 206 | 0.06102968911 | 0.00000127864423 | 7.31148 | 38.255034 | 231.699029 |
14 | 2025-01-14 00:00:00.000 | 45391 | 294 | 0.01567127497 | 3.452507099e-7 | -4.900482 | 42.718447 | 154.391156 |
15 | 2025-01-15 00:00:00.000 | 45775 | 300 | 0.02951769102 | 6.448430588e-7 | 0.845983 | 2.040816 | 152.583333 |
16 | 2025-01-16 00:00:00.000 | 45170 | 254 | 0.02799431279 | 6.197545449e-7 | -1.321682 | -15.333333 | 177.834646 |
17 | 2025-01-17 00:00:00.000 | 45190 | 208 | 0.03845502396 | 8.509631326e-7 | 0.044277 | -18.110236 | 217.259615 |
18 | 2025-01-18 00:00:00.000 | 45215 | 164 | 0.07982698586 | 0.000001765497863 | 0.055322 | -21.153846 | 275.70122 |
19 | 2025-01-19 00:00:00.000 | 47559 | 112 | 0.2233814254 | 0.000004696932767 | 5.18412 | -31.707317 | 424.633929 |
20 | 2025-01-20 00:00:00.000 | 46252 | 126 | 0.1527616557 | 0.000003302811893 | -2.748165 | 12.5 | 367.079365 |
HadisehDaily
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
WHERE BLOCK_TIMESTAMP >= '2025-01-01'
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
WHERE BLOCK_TIMESTAMP >= '2025-01-01'
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
WHERE t.BLOCK_TIMESTAMP >= '2025-01-01'
GROUP BY 1, 2
),
cohort_analysis AS (
SELECT
cohort_period,
COUNT(DISTINCT user_address) AS cohort_user_count,
SUM(CASE WHEN retention_days >= 1 THEN 1 ELSE 0 END) AS day1_retained
FROM user_activity
Last run: 17 days ago
58
6KB
4s