SELECT

CASE WHEN num = 0

THEN DATE_TRUNC('week', CURRENT_DATE) -- Start of current week

ELSE dateadd('week', -num, current_date)

END as start_date,

CASE WHEN num = 0

THEN CURRENT_TIMESTAMP -- Use current timestamp for ongoing week

ELSE dateadd('week', -num+1, current_date)

END as end_date,

num as weeks_ago,

TO_CHAR(CASE WHEN num = 0

THEN DATE_TRUNC('week', CURRENT_DATE)

ELSE dateadd('week', -num, current_date)

END, 'YYYY-MM-DD') || ' to ' ||

TO_CHAR(CASE WHEN num = 0

THEN CURRENT_DATE

ELSE dateadd('week', -num+1, current_date)

END, 'YYYY-MM-DD') as date_range

FROM (SELECT row_number() over (order by seq4()) - 1 AS num

FROM table(generator(rowcount => 12))) -- Increased to 12 weeks

),

cadence_accounts AS (

SELECT

wp.weeks_ago,

wp.date_range,

COUNT(DISTINCT first_tx.value) AS new_accounts

FROM weekly_periods wp

LEFT JOIN (

SELECT

a.value,

MIN(block_timestamp) as first_tx_time

FROM flow.core.ez_transaction_actors,

LATERAL FLATTEN(INPUT => actors) a

GROUP BY a.value