pb-query.sql

SELECT 
  AVG(s.totalSale)
    OVER (ORDER BY orderDay
          ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as runningAverage,
  p.name as productName,
  s.orderDay as orderDay,
FROM ( 
    SELECT SUM(salesOrderLines.totalSale) as totalSale, 
    DATE(orderDate) as orderDay, salesOrderLines.productKey as productKey
    FROM [google.com:bigquery-petabyte:retail_petabyte.sales_partitioned]
    WHERE _PARTITIONTIME 
      BETWEEN TIMESTAMP("2011-01-01") AND  TIMESTAMP("2011-01-31")
    and REGEXP_MATCH(salesOrderLines.productKey, r'^1104537600000\-1[0-9]{2}$')
    GROUP BY orderDay, productKey
) s
JOIN [google.com:bigquery-petabyte:retail_petabyte.products] p
ON s.productKey = p.productKey
ORDER BY orderDay, productName