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-01-01' AND block_timestamp::date < '2025-02-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-01-01' AND block_timestamp::date < '2025-02-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-01-01' AND block_timestamp::date < '2025-02-01'

GROUP BY

1, 2

),

cohort_retention AS (