SELECT

MIN(block_timestamp::date) AS day,

from_address

FROM

blast.core.fact_transactions

WHERE

status = 'SUCCESS'

GROUP BY

from_address

),

filtered_new_users AS (

SELECT

day,

COUNT(DISTINCT from_address) AS "New Users"

FROM

new_users

WHERE

day >= DATEADD(day, -30, CURRENT_DATE) -- Only include data from the last 30 days

GROUP BY

day

),

total_new_users AS (

SELECT

day,

"New Users",

SUM("New Users") OVER (ORDER BY day ASC) AS "Cumulative New Users"

FROM

filtered_new_users

SELECT

SUM("New Users") AS total_new_users,

AVG("New Users") AS avg_daily_new_users

FROM