SELECT

DATE_TRUNC('week', block_timestamp) AS weekly,

sender,

COUNT(*) AS tx_count

FROM movement.core.fact_transactions

WHERE TX_TYPE = 'user_transaction'

GROUP BY 1, 2

),

active_users AS (

-- Filter senders with 3+ transactions per week

SELECT

weekly,

sender

FROM transactions_per_user

WHERE tx_count >= 3

),

filtered_transactions AS (

-- Count transactions made by active users

SELECT

DATE_TRUNC('week', t.block_timestamp) AS weekly,

COUNT(*) AS "Transactions"

FROM movement.core.fact_transactions t

JOIN active_users a

ON DATE_TRUNC('week', t.block_timestamp) = a.weekly

AND t.sender = a.sender

WHERE t.TX_TYPE = 'user_transaction'

GROUP BY 1

),

active_users_count AS (

-- Count active users per week

SELECT

weekly,

COUNT(DISTINCT sender) AS "Active Addresses"

FROM active_users

GROUP BY 1