SELECT

DATE_TRUNC('day', block_timestamp) AS day,

COUNT(DISTINCT TRIM(SPLIT_PART(SPLIT_PART(fee_msg['transition']['outputs'][0]['value'], '[', 2), ',', 1))) as daily_active_users

FROM aleo.core.fact_transactions

WHERE

tx_succeeded = TRUE

AND block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

GROUP BY day

SELECT

ROUND(AVG(daily_active_users), 0) AS avg_daily_active_users,

ROUND(AVG(daily_active_users) * 30, 0) AS estimated_monthly_active_users,

MIN(day) as period_start,

MAX(day) as period_end,

COUNT(DISTINCT day) as days_counted

FROM daily_active

WHERE daily_active_users > 0;