title: Bigquery Grouping by rolling days
body: <p>Here is my attempt to group by rolling week and last week. I understand the error, but when I group by day the minimum becomes minimum of each row and thus not the minimum of the set.</p>
  
  <p>There must be a simple way to group days together in Bigquery.</p>
  
  <pre><code>select
      n_rtb_impressions,
      if(dayy between min(dayy) and (min(dayy) + 6) ,1, 0) as D
  from
      (SELECT
      ((dayofyear(datetime))) as dayy,
          sum(IF( c_priority IN (19,20,21), IFNULL(s_impressions,0)-IFNULL(hybrid_p_back,0), 0)) AS n_rtb_impressions,
          FROM
          TABLE_DATE_RANGE(hourly_stats.v1_,DATE_ADD(CURRENT_DATE(),-14,"day"),DATE_ADD(CURRENT_DATE(),-1,"day"))
          group by dayy)
  </code></pre>
  
  <p>EDIT with the help below and then some playing around I managed to expand it for multiple dimensions and then filter out results such that each group of dimensions only has two rows, one for sum 1-7 days and one for 8-14 days. I then used NTH to pivot the data so the grouping is now dimensions. I'll put it here for others:</p>
  
  <pre><code>select
  server,
  a_name,
  w_name,
  rtb_name,
  
  NTH(1, last_7_days_n_rtb_impressions_1) This_week, NTH(2, last_7_days_n_rtb_impressions_1) Last_Week,
  
  from(
      select
          [date],
          dw,
          server,
          a_name,
          w_name,
          rtb_name,
          last_7_days_n_rtb_impressions_1
      from(
          SELECT
              [date],
              dw,
              SUM(n_rtb_impressions) OVER(ORDER BY rtb_name, server, a_name, w_name,[date]
                ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS last_7_days_n_rtb_impressions_1,
              server,
              a_name,
              w_name,
              rtb_name,
          FROM (
            SELECT
                server,
                a_name,
                w_name,
                rtb_name,
                dayofweek(datetime) as dw,
                DATE(datetime) AS [date],
                SUM(IF( c_priority IN (19,20,21), IFNULL(s_impressions,0)-IFNULL(hybrid_p_back,0), 0)) AS n_rtb_impressions,
  
            FROM TABLE_DATE_RANGE(hourly_stats.v1_,DATE_ADD(CURRENT_DATE(),-14,"day"),
                 DATE_ADD(CURRENT_DATE(),-1,"day"))
  
            GROUP BY [date],dw,server,
                   a_name,
                   w_name,
                   rtb_name,
            order by rtb_name,server, a_name,w_name, [date])
  
            order by rtb_name,server, a_name,w_name, )
  
            where dw = dayofweek(DATE_ADD(CURRENT_DATE(),-1,"day"))
  
            order by rtb_name,server, a_name,w_name, [date])
  
            group by
            server,
            a_name,
            w_name,
            rtb_name,
  </code></pre>