big query rolling average ARPU

1.sql

WITH
  mydate AS(
  SELECT
    day
  FROM
    UNNEST( GENERATE_DATE_ARRAY( DATE_TRUNC(DATE_SUB(CURRENT_DATE,INTERVAL 1 YEAR),MONTH), CURRENT_DATE(), INTERVAL 1 DAY) ) AS day ),
  randomdata AS(
  SELECT
    t1.*,
    ROUND(t1.revenue*rand()) AS user
  FROM (
    SELECT
      *,
      ROUND(rand()*1000) AS revenue
    FROM
      mydate
    WHERE
      rand()<= 300/377) t1 ),
  randomdata_withnull AS(
  SELECT
    mydate.day,
    revenue,
    user
  FROM
    randomdata
  RIGHT JOIN
    mydate
  ON
    randomdata.day=mydate.day
  ORDER BY
    day )
SELECT
  day,
  revenue,
  user,
  SUM(revenue) OVER (ORDER BY day ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)/SUM(user) OVER (ORDER BY day ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_avg_ARPU
FROM
  randomdata_withnull