title: How do I calculate the Rolling Average for the difference of days between events on BigQuery?
body: <p>I have a table of events that goes like this:</p>
  
  <pre><code>date                  event_category     event_planner
  
  2019-09-22T00:00:00   soccer_night       Marcus
  2019-09-25T00:00:00   comedy_night       John
  2019-09-28T00:00:00   dance_party        John
  2019-10-02T00:00:00   soccer_night       Marcus
  </code></pre>
  
  <p>The idea here is to get the rolling average of the difference between dates for every planner.
  So far I have the distance in days for each planner separated by category with the following:
  <code>DATE_DIFF(SAFE_CAST(date AS date),LAG(SAFE_CAST(date AS date)) OVER (PARTITION BY event_category, event_planner ORDER BY date), day) AS result</code> </p>
  
  <p>What I expect is something like this:</p>
  
  <pre><code>date                  event_category     event_planner     rolling_avg
  
  2019-09-22T00:00:00   soccer_night       Marcus            0
  2019-09-25T00:00:00   comedy_night       John              0
  2019-09-28T00:00:00   comedy_night       John              3
  2019-10-02T00:00:00   soccer_night       Marcus            10
  2019-10-10T00:00:00   comedy_night       John              7
  </code></pre>