title: BigQuery SQL for 28-day sliding window aggregate (without writing 28 lines of SQL)
body: <p>I'm trying to compute a 28 day moving sum in BigQuery using the LAG function.</p>
  
  <p>The top answer to this question</p>
  
  <p><a href="https://stackoverflow.com/questions/22415647/bigquery-sql-for-sliding-window-aggregate">Bigquery SQL for sliding window aggregate</a></p>
  
  <p>from Felipe Hoffa indicates that that you can use the LAG function. An example of this would be:</p>
  
  <pre><code>SELECT
      spend + spend_lagged_1day + spend_lagged_2day + spend_lagged_3day + ... +  spend_lagged_27day as spend_28_day_sum,
      user,
      date
  FROM (
    SELECT spend,
           LAG(spend, 1) OVER (PARTITION BY user ORDER BY date) spend_lagged_1day,
           LAG(spend, 2) OVER (PARTITION BY user ORDER BY date) spend_lagged_2day,
           LAG(spend, 3) OVER (PARTITION BY user ORDER BY date) spend_lagged_3day,
           ...
           LAG(spend, 28) OVER (PARTITION BY user ORDER BY date) spend_lagged_day,
           user,
           date
    FROM user_spend
  )
  </code></pre>
  
  <p>Is there a way to do this without having to write out 28 lines of SQL!</p>