how to get weekly unbound retention from bigquery?
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.