SELECT

payer AS user,

COUNT(DISTINCT tx_id) AS total_transactions

FROM

flow.core.fact_transactions

WHERE

block_timestamp BETWEEN DATEADD(day, -30, CURRENT_DATE()) AND CURRENT_DATE()

GROUP BY

payer

),

active_users AS (

SELECT

user

FROM

user_activity

WHERE

total_transactions > 1

),

new_users AS (

SELECT

user

FROM

user_activity

WHERE

total_transactions = 1

),

metrics AS (

SELECT

COUNT(DISTINCT ua.user) AS total_users,

COUNT(DISTINCT au.user) AS total_active_users,

COUNT(DISTINCT nu.user) AS total_new_users

FROM

user_activity ua

LEFT JOIN active_users au ON ua.user = au.user

LEFT JOIN new_users nu ON ua.user = nu.user