how to get weekly unbound retention from bigquery? I Need to get the weekly retention users.
If user has made the transaction in week 4, that user is there in Week 0, week 1, week 2, week 3, week 4.
If User made transaction in week 0, and week 3 then user will be there in Week 0, week 1, week 2, week 3
Thanks @bbaird,
I am updating the question with precise information and question
Here is my table data




TxnId
userid
transactedWeek




T1
U1
0


T2
U2
0


T3
U3
0


T4
U4
0


T5
U1
0


T6
U2
0


T7
U6
0


T8
U1
1


T9
U8
1


T10
U12
1


T11
U3
1


T12
U5
1


T13
U9
2


T14
U15
2


T15
U20
3


T16
U15
3


T17
U15
3


T18
U8
3


T19
U9
4


T20
U20
5




The Result I am looking for




Week
active_users_count




0
11


1
8


2
4


3
4


4
2


5
1




Users count is kinda cumulative but unique users.
For example if user-1 has performed transaction 3 times in week-0, then count for week-0 is 1
If User-1 has performed 3 transactions in week-0, 2 transaction in week-2, 1 transaction in week-3,
then count will be like this
week-0 -&gt; 1  (i.e. he has performed transaction &gt;= week 0)
week-1 -&gt; 1  (i.e. he has performed transaction &gt;= week 1)
week-2 -&gt; 1  (i.e. he has performed transaction &gt;= week 2)
week-3 -&gt; 1  (i.e. he has performed transaction &gt;= week 3)
There can be any number of weeks. It is not fixed to 4 weeks or 8 weeks.
This should be a relatively straightforward aggregation by Week, but you will need to join all records greater than or equal to the current week and take your distinct count from there.
However, that's going to be a lot of rows for Bigquery to work through, so this will probably work better for the platform:
SELECT
  TransactedWeek AS Week
 ,SUM(SUM(LastWeekInd))
    OVER
      (
        PARTITION BY 
          1 
        ORDER BY 
          TransactedWeek
        ROWS BETWEEN 
          CURRENT ROW
            AND
          UNBOUNDED FOLLOWING
      ) AS active_users_count
FROM
  (
    SELECT DISTINCT
      UserId
     ,TransactedWeek
     ,CASE
        WHEN TransactedWeek = MAX(TransactedWeek) OVER (PARTITION BY UserId) THEN 1
        ELSE 0
      END AS LastWeekInd
    FROM
      &lt;Your Table&gt;
  ) x
GROUP BY
  TransactedWeek

You can add logic to limit the ranges in the inner query.
Note: if you have any weeks missing from your data, they will be missing in your final result (although the counts will still be accurate).  If you have weeks with no transactions, you may need to generate a list of weeks and left join these results to that.