SELECT contract_address, project, LABEL_TYPE

FROM $query('85327cc9-a796-46c4-a6a5-daf42d48dd23')

),

base_txns AS (

SELECT

DATE_TRUNC('month', BLOCK_TIMESTAMP) AS month,

ORIGIN_FROM_ADDRESS as user_address,

COUNT(DISTINCT tx_hash) as txn_count

FROM kaia.core.fact_event_logs

WHERE contract_address IN (SELECT contract_address FROM contracts)

GROUP BY 1, 2

),

user_first_tx AS (

SELECT

user_address,

MIN(month) as first_month

FROM base_txns

GROUP BY 1

),

monthly_metrics AS (

SELECT

b.month,

COUNT(DISTINCT b.user_address) as active_users,

COUNT(DISTINCT CASE WHEN b.month = f.first_month THEN b.user_address END) as new_users,

COUNT(DISTINCT CASE WHEN b.month > f.first_month THEN b.user_address END) as returning_users

FROM base_txns b

LEFT JOIN user_first_tx f ON b.user_address = f.user_address

GROUP BY 1

),

cumulative_users AS (

SELECT