title: How to calculate the sum of a column for last week in Google Big Query?
body: <p>Based off Mikhail's answer from (<a href="https://stackoverflow.com/questions/49900602/how-to-calculate-rolling-average-of-same-weekday-as-current-row-in-big-query">How to calculate rolling average of same weekday as current row in Big Query?</a>)</p>
  
  <p>I was hoping to replicate this but with week number instead. Below is my query. It appears since the weeknumber are the same for 7 rows at a time, it does not work as intended</p>
  
  <pre><code>SELECT id, 
         date, 
         weeknumber, 
         sales_total,
         SUM(net_sales_total) OVER(rolling_last_week) last_week
  FROM (
       SELECT id, 
              date,  
              sales_total,
              EXTRACT(WEEK FROM entry_date) weeknumber
       FROM `mydataset.mytable`   
  )
  WINDOW rolling_last_week AS (
     PARTITION BY id, weeknumber
     ORDER BY entry_date 
     ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
  )
  ORDER BY weeknumber, entry_date
  </code></pre>
  
  <p>Table_A</p>
  
  <pre><code>+-------+---------+----------+-----------+
  | id    |  date   |weeknumber|sales_total|
  +-------+---------+----------+-----------+
  | 1     | 01-01-17|    1     |     5     |
  | 1     | 01-02-17|    1     |     5     |
  | 1     | 01-03-17|    1     |     5     |
  | 1     | 01-04-17|    1     |     5     |
  | 1     | 01-05-17|    1     |     5     |
  | 1     | 01-06-17|    1     |     5     |
  | 1     | 01-07-17|    1     |     5     |
  | 1     | 01-08-17|    2     |     10    |
  | 1     | 01-09-17|    2     |     10    |
  | 1     | 01-10-17|    2     |     10    |
  | 1     | 01-11-17|    2     |     10    |
  | 1     | 01-12-17|    2     |     10    |
  | 1     | 01-13-17|    2     |     10    |
  | 1     | 01-14-17|    2     |     10    |
  +-------+---------+----------+-----------+
  </code></pre>
  
  <p>I am hoping the resulting query returns the sum of last week's sales for every single day in the current week</p>
  
  <pre><code>+-------+---------+----------+-----------+-----------+
  | id    |  date   |weeknumber|sales_total| last_week |
  +-------+---------+----------+-----------+-----------+
  | 1     | 01-01-17|    1     |     5     | .         |
  | 1     | 01-02-17|    1     |     5     | .         |
  | 1     | 01-03-17|    1     |     5     | .         |
  | 1     | 01-04-17|    1     |     5     | .         |
  | 1     | 01-05-17|    1     |     5     | .         |
  | 1     | 01-06-17|    1     |     5     | .         |
  | 1     | 01-07-17|    1     |     5     | .         |
  | 1     | 01-08-17|    2     |     10    | 35        |
  | 1     | 01-09-17|    2     |     10    | 35        |
  | 1     | 01-10-17|    2     |     10    | 35        |
  | 1     | 01-11-17|    2     |     10    | 35        |
  | 1     | 01-12-17|    2     |     10    | 35        |
  | 1     | 01-13-17|    2     |     10    | 35        |
  | 1     | 01-14-17|    2     |     10    | 35        |
  +-------+---------+----------+-----------+-----------+
  </code></pre>