title: Rolling 90 days active users in BigQuery, improving preformance (DAU/MAU/WAU)
body: <p>I'm trying to get the number of unique events on a specific date, rolling 90/30/7 days back. I've got this working on a limited number of rows with the query bellow but for large data sets I get memory errors from the aggregated string which becomes massive. </p>
  
  <p>I'm looking for a more effective way of achieving the same result. </p>
  
  <p>Table looks something like this: </p>
  
  <pre><code>+---+------------+-------------+
  |   |     date   |     userid  |
  +---+------------+-------------+
  | 1 | 2013-05-14 | xxxxx       |
  | 2 | 2017-03-14 | xxxxx       |
  | 3 | 2018-01-24 | xxxxx       |
  | 4 | 2013-03-21 | xxxxx       |
  | 5 | 2014-03-19 | xxxxx       |
  | 6 | 2015-09-03 | xxxxx       |
  | 7 | 2014-02-06 | xxxxx       |
  | 8 | 2014-10-30 | xxxxx       |
  | ..| ...        | ...         |
  +---+------------+-------------+
  </code></pre>
  
  <p>Format of the desired result: </p>
  
  <pre><code>+---+------------+---------------------------------------------+
  |   |     date   | active_users_7_days | active_users_90_days  |
  +---+------------+---------------------------------------------+
  | 1 | 2013-05-14 | 1240                | 34339                 |
  | 2 | 2017-03-14 | 4334                | 54343                 |
  | 3 | 2018-01-24 | .....               | .....                 |
  | 4 | 2013-03-21 | .....               | .....                 |
  | 5 | 2014-03-19 | .....               | .....                 |
  | 6 | 2015-09-03 | .....               | .....                 |
  | 7 | 2014-02-06 | .....               | .....                 |
  | 8 | 2014-10-30 | .....               | .....                 |
  | ..| ...        | .....               | .....                 |
  +---+------------+---------------------------------------------+
  </code></pre>
  
  <p>My query looks like this:</p>
  
  <pre><code>#standardSQL
      WITH
        T1 AS(
        SELECT
          date,
          STRING_AGG(DISTINCT userid) AS IDs
        FROM
          `consumer.events`
        GROUP BY
          date ),
        T2 AS(
        SELECT
          date,
          STRING_AGG(IDs) OVER(ORDER BY UNIX_DATE(date) RANGE BETWEEN 90 PRECEDING
            AND CURRENT ROW) AS IDs
        FROM
          T1 )
      SELECT
        date,
        (
        SELECT
          COUNT(DISTINCT (userid))
        FROM
          UNNEST(SPLIT(IDs)) AS userid) AS NinetyDays
      FROM
        T2
  </code></pre>