COHORT | Txns Week 0 | Txns Week 1 | Txns Week 2 | Txns Week 3 | Txns Week 4 | Txns Week 5 | Contract % Week 0 | Contract % Week 1 | Contract % Week 2 | Contract % Week 3 | Contract % Week 4 | Contract % Week 5 | Success % Week 0 | Success % Week 1 | Success % Week 2 | Success % Week 3 | Success % Week 4 | Success % Week 5 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 2025-02-17 | 20.425648 | 64.305289 | 49.52591 | 82.825982 | 47.744729 | 85.827659 | 91.611326 | 92.156387 | 94.523425 | 94.650442 | 98.449008 | 97.194871 | 96.177913 | 95.677016 | 95.850018 | |||
2 | 2025-02-24 | 15.392542 | 20.286952 | 30.272536 | 21.307402 | 93.1141 | 91.557938 | 95.549339 | 96.74229 | 96.790831 | 96.370583 | 96.685258 | 94.144505 | ||||||
3 | 2025-03-03 | 5.107841 | 16.341696 | 12.142118 | 85.491231 | 92.461393 | 90.65102 | 96.340135 | 95.10692 | 96.169967 | |||||||||
4 | 2025-03-10 | 3.952138 | 6.587592 | 68.998434 | 78.288365 | 99.141324 | 97.428199 | ||||||||||||
5 | 2025-03-17 | 1.421853 | 29.929039 | 99.421768 |
m3jiconscious-fuchsia
Updated 2025-03-21
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
›
⌄
-- Step 1: Create a base table with each user's first activity date
WITH first_activity AS (
SELECT
FROM_ADDRESS,
DATE_TRUNC('week', MIN(BLOCK_TIMESTAMP)) AS cohort_week
FROM monad.testnet.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2025-02-19'::timestamp
GROUP BY FROM_ADDRESS
),
-- Step 2: Get all transactions with cohort information
transactions_with_cohort AS (
SELECT
t.FROM_ADDRESS,
t.TO_ADDRESS,
t.BLOCK_TIMESTAMP,
t.TX_SUCCEEDED,
t.GAS_USED,
t.TX_FEE,
t.INPUT_DATA,
CASE WHEN t.INPUT_DATA != '0x' THEN TRUE ELSE FALSE END AS is_contract_interaction,
fa.cohort_week,
DATEDIFF('week', fa.cohort_week, DATE_TRUNC('week', t.BLOCK_TIMESTAMP)) AS week_number
FROM monad.testnet.fact_transactions t
JOIN first_activity fa ON t.FROM_ADDRESS = fa.FROM_ADDRESS
WHERE t.BLOCK_TIMESTAMP >= '2025-02-19'
),
-- Step 3: Aggregate behavior metrics by cohort and week number
cohort_metrics AS (
SELECT
cohort_week,
week_number,
COUNT(DISTINCT FROM_ADDRESS) AS active_users,
COUNT(*) AS transaction_count,
SUM(CASE WHEN TX_SUCCEEDED = true THEN 1 ELSE 0 END) AS successful_transactions,
Last run: about 1 month ago
5
741B
66s