SELECT

from_address,

MIN(block_timestamp) AS first_transaction

FROM

blast.core.fact_transactions

GROUP BY

from_address

),

BaseTable AS (

SELECT

from_address,

date_trunc('week', block_timestamp) AS transaction_week,

MIN(date_trunc('week', block_timestamp)) OVER (PARTITION BY from_address) AS earliest_date,

DATEDIFF('week', MIN(date_trunc('week', block_timestamp)) OVER (PARTITION BY from_address), date_trunc('week', block_timestamp)) AS difference

FROM

blast.core.fact_transactions

),

CountNewUsers AS (

SELECT

earliest_date,

COUNT(DISTINCT from_address) AS new_users

FROM

BaseTable

GROUP BY

earliest_date

),

CountReturningUsers AS (

SELECT

earliest_date,

difference,

COUNT(DISTINCT from_address) AS existing_users