Bigquery query to find 2-week retention

Question

I have made a query to find weekly retention of users. I want to change it to find 2-week retention of users.

Below is the query to find weekly retention.

First I bucket users into different cohort by their first use week, and store into cohort_items and build user_activities which return all the pairs of (user_id, week_number) that indicates if a user is active in that week after their original use date.

Cohort Size: is simply how many users are in each group And finally, putting them together with the below:

first_use_dt as (
        SELECT user_id,
            dt
        FROM (
                SELECT user_id,
                    dt
                    row_number() over (
                        partition by user_id
                        order by dt
                    ) as rn
                FROM activity_table
            )
        where rn = 1
    ),
    cohort_items as (
        select date_trunc(dt, WEEK) as cohort_week,
            user_id
        from first_use_dt
    ),
    user_activities as (
        select a.user_id,
            date_diff(date_trunc(a.dt, WEEK), c.cohort_week, week) as week_number
        from adot_applog_prd_all_callbrief a
            left join cohort_items c on a.user_id = c.user_id
        group by user_id,
            week_number
    ),
    cohort_size as (
        select cohort_week,
            count(distinct user_id) as num_users
        from cohort_items
        group by cohort_week,
        order by cohort_week
    ),
    retention_table as (
        SELECT c.cohort_week,
            a.week_number,
            count(1) as num_users
        from user_activities a
            left join cohort_items c on a.user_id = c.user_id
        group by cohort_week,
            week_number
    )
select b.cohort_week as dt,
    s.num_users as total_users,
    b.week_number as week_num,
    s.num_users * 100 / s.num_users as percentage
from retention_table b
    left join cohort_size s on b.cohort_week = s.cohort_week
where b.cohort_week is not null
order by b.cohort_week,
    b.week_number

Now, I have tried something like this to get 2 week interval, but the output is not as desired.

first_use_dt as (
        SELECT user_id,
            dt
        FROM (
                SELECT user_id,
                    dt
                    row_number() over (
                        partition by user_id
                        order by dt
                    ) as rn
                FROM activity_table
            )
        where rn = 1
    ),
    cohort_items as (
        select DATE_TRUNC(DATE_ADD(dt, INTERVAL 2 WEEK), WEEK) as cohort_week,
            --------tried to get 2 week
            user_id
        from first_use_dt
    ),
    user_activities as (
        select a.user_id
            date_diff(
                DATE_TRUNC(DATE_ADD(dt, INTERVAL 2 WEEK), WEEK),
                c.cohort_week,
                WEEK
            ) * 2 as week_number --------tried  to get 2 week
        from adot_applog_prd_all_callbrief a
            left join cohort_items c on a.user_id = c.user_id
        group by user_id,
            week_number
    ),
    cohort_size as (
        select cohort_week,
            count(1) as num_users
        from cohort_items
        group by cohort_week,
        order by cohort_week
    ),
    retention_table as (
        SELECT c.cohort_week,
            a.week_number,
            count(1) as num_users
        from user_activities a
            left join cohort_items c on a.user_id = c.user_id
        group by cohort_week,
            week_number
    )
select b.cohort_week as dt,
    s.num_users as total_users,
    b.week_number as week_num,
    b.num_users * 100 / s.num_users as percentage
from retention_table b
    left join cohort_size s on b.cohort_week = s.cohort_week
where b.cohort_week is not null
order by b.cohort_week,
    b.week_number;

If anyone can help me it would be much appreciated