SELECT

DATE_TRUNC('week', tx_2024.block_timestamp) AS activity_week,

tx_2024.sender AS user,

tx_2024.tx_hash AS tx_hash

FROM

aptos.core.fact_transactions tx_2024

WHERE

tx_2024.block_timestamp >= '2024-01-01'

AND tx_2024.block_timestamp < '2025-01-01'

),

NewUsersWeekly AS (

SELECT

activity_week,

COUNT(DISTINCT wa.user) AS new_users

FROM

WeeklyActivity wa

LEFT JOIN aptos.core.fact_transactions tx_prior

ON wa.user = tx_prior.sender

AND tx_prior.block_timestamp < '2024-01-01'

WHERE

tx_prior.sender IS NULL

GROUP BY

activity_week

),

ReturningUsersWeekly AS (

SELECT

activity_week,

COUNT(DISTINCT wa.user) AS returning_users

FROM

WeeklyActivity wa

JOIN aptos.core.fact_transactions tx_prior

ON wa.user = tx_prior.sender

AND tx_prior.block_timestamp < '2024-01-01'

GROUP BY

activity_week