Rolling NEW active users in SQL (BigQuery)

Question

I have already computed rolling active users (on a weekly basis) as follow:

    SELECT
      DATE_TRUNC(EXTRACT(DATE FROM tracks.timestamp), WEEK),
      COUNT(DISTINCT tracks.user_id)
    FROM `company.dataset.tracks` AS tracks
    WHERE tracks.timestamp > TIMESTAMP('2020-01-01')
    AND tracks.event = 'activation_event'
    GROUP BY 1
    ORDER BY 1

I am interested in knowing the number of distinct users who performed the activation event for the 1st time on a rolling weekly basis.

Answer 1

If I follow you correctly, you can use two levels of aggrgation:

select 
    date_trunc(date(activation_timestamp), week) activation_week, 
    count(*) cnt_active_users
from (
    select min(timestamp) activation_timestamp
    from `company.dataset.tracks` t
    where event = 'activation_event'
    group by user_id
) t
where activation_timestamp > timestamp('2020-01-01

The subquery comptes the date of the first activation event per user, then the outer query counts the number of such events per week.