Bigquery Grouping by rolling days
<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>