SELECT

tx_hash,

block_timestamp,

from_address AS user,

tx_fee

FROM kaia.core.fact_transactions

),

monthly_txns AS (

SELECT

DATE_TRUNC('MONTH', block_timestamp) AS month,

user,

COUNT(tx_hash) AS tx_count,

SUM(tx_fee) AS total_fee

FROM main

GROUP BY 1, 2

),

first_seen AS (

SELECT

user,

MIN(DATE_TRUNC('MONTH', block_timestamp)) AS first_month

FROM main

GROUP BY 1

),

monthly_metrics AS (

SELECT

mt.month,

SUM(mt.tx_count) AS total_txns,

SUM(mt.total_fee) AS total_fee,

COUNT(DISTINCT CASE WHEN fs.first_month = mt.month THEN mt.user END) AS new_users,

COUNT(DISTINCT CASE WHEN fs.first_month < mt.month THEN mt.user END) AS old_users

FROM monthly_txns mt

LEFT JOIN first_seen fs ON mt.user = fs.user

GROUP BY 1

),

cumulative_metrics AS (