SELECT

DATE_TRUNC('week', block_timestamp) AS week,

COUNT(TX_HASH) AS transaction_count,

COUNT(DISTINCT from_address) AS unique_users_count,

COUNT(DISTINCT CASE WHEN first_week = week THEN from_address END) AS new_users_count

FROM (

SELECT

block_timestamp,

TX_HASH,

from_address,

MIN(DATE_TRUNC('week', block_timestamp)) OVER (PARTITION BY from_address) AS first_week

FROM base.core.fact_transactions where STATUS = 'SUCCESS'

) sub

where block_timestamp >= '{{Start_date}}' and Block_timestamp <= '{{Target_date}}' GROUP BY week

),

weekly_changes AS (

SELECT

week,

transaction_count,

unique_users_count,

new_users_count,

LAG(transaction_count) OVER (ORDER BY week) AS prev_transaction_count,

LAG(unique_users_count) OVER (ORDER BY week) AS prev_unique_users_count,

LAG(new_users_count) OVER (ORDER BY week) AS prev_new_users_count

FROM weekly_data

week,

transaction_count,

unique_users_count,

new_users_count,

ROUND((transaction_count - prev_transaction_count) * 100.0 / prev_transaction_count, 2) AS transaction_count_change_pct,

ROUND((unique_users_count - prev_unique_users_count) * 100.0 / prev_unique_users_count, 2) AS unique_users_count_change_pct,

ROUND((new_users_count - prev_new_users_count) * 100.0 / prev_new_users_count, 2) AS new_users_count_change_pct

FROM weekly_changes;