title: Calculate 7, 14 and 30 day moving average in bigquery
body: <p>I am playing around with bigquery. I have IoT uptime recordings as input:</p>
  
  <pre><code>+---------------+-------------+----------+------------+
  |   device_id   |  reference  |  uptime  | timestamp  |
  +---------------+-------------+----------+------------+
  | 1             | 1000-5      |  0.7     | 2019-02-12 |
  | 2             | 1000-6      |  0.9     | 2019-02-12 |
  | 1             | 1000-5      |  0.8     | 2019-02-11 |
  | 2             | 1000-6      |  0.95    | 2019-02-11 |
  +---------------+-------------+----------+------------+
  </code></pre>
  
  <p>I want to calculate the 7, 14 and 30 day moving average of the uptime grouped by device. The output should look as follows:</p>
  
  <pre><code>+---------------+-------------+---------+--------+--------+
  |   device_id   |  reference  |  avg_7  | avg_14 | avg_30 |
  +---------------+-------------+---------+--------+--------+
  | 1             | 1000-5      |  0.7    | ..     | ..     |
  | 2             | 1000-6      |  0.9    | ..     | ..     |
  +---------------+-------------+---------+--------+--------+
  </code></pre>
  
  <p>What I have tried:</p>
  
  <pre><code>SELECT
      device_id,
      AVG(uptime) OVER (ORDER BY day RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
  FROM (
    SELECT device_id, uptime, UNIX_DATE(DATE(timestamp)) as day FROM `uptime_recordings`
  )
  GROUP BY device_id, uptime, day
  </code></pre>
  
  <p>I have recordings for 1000 distinct devices and 200k readings. The grouping does not work and the query returns 200k records instead of 1000. Any ideas whats wrong?</p>