WITH first_time_users AS (

SELECT

FROM_ADDRESS AS sender,

MIN(BLOCK_TIMESTAMP) AS first_tx_date

FROM base.core.fact_transactions

GROUP BY FROM_ADDRESS

SELECT

DATE_TRUNC('week', t.BLOCK_TIMESTAMP) AS week,

COUNT(DISTINCT CASE WHEN DATE_TRUNC('day', t.BLOCK_TIMESTAMP) = DATE_TRUNC('day', f.first_tx_date) THEN t.FROM_ADDRESS END) AS new_users,

COUNT(DISTINCT CASE WHEN DATE_TRUNC('day', t.BLOCK_TIMESTAMP) > DATE_TRUNC('day', f.first_tx_date) THEN t.FROM_ADDRESS END) AS returning_users

FROM base.core.fact_transactions t

LEFT JOIN first_time_users f ON t.FROM_ADDRESS = f.sender

GROUP BY week

ORDER BY week;