title: Google BigQuery: Rolling Count Distinct
body: <p>I have a table with is simply a list of dates and user IDs (not aggregated). </p>
  
  <p>We define a metric called <em>active users</em> for a given date by counting the distinct number of IDs that appear in the previous 45 days.</p>
  
  <p>I am trying to run a query in BigQuery that, for each day, returns the day plus the number of active users for that day (count distinct user from 45 days ago until today).</p>
  
  <p>I have experimented with window functions, but can't figure out how to define a range based on the date values in a column. Instead, I believe the following query would work in a database like MySQL, but does not in BigQuery.</p>
  
  <pre><code>SELECT 
    day,
    (SELECT 
      COUNT(DISTINCT visid) 
     FROM daily_users
     WHERE day BETWEEN DATE_ADD(t.day, -45, "DAY") AND t.day
     ) AS active_users
  FROM daily_users AS t
  GROUP BY 1
  </code></pre>
  
  <p>This doesn't work in BigQuery: "Subselect not allowed in SELECT clause."</p>
  
  <p>How to do this in BigQuery?</p>