Issues with calculating running total in BigQuery

Question

Not sure what the error here is but the returned result won't give the running total. I keep getting the same numbers returned for both ad_rev and running_total_ad_rev. Maybe someone could point out what the issue is? Thank you!

SELECT
days,
sum(ad_revenue) as ad_rev,
sum(sum(ad_revenue)) over (partition by days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total_ad_rev

FROM(
SELECT
DATE_DIFF(activity_date,creation_date,DAY) AS days,
ad_revenue

FROM
 table1 INNER JOIN table2 
USING (id)
WHERE
creation_date >= *somedate* 
and
activity_date = *somedate*
GROUP BY 1,2
ORDER BY 1)
GROUP BY 1
ORDER BY 1

Answer 1

You can't need partition by days if you want have running sum. Also you need to calculate daily_revenue step earlier. Feels like this is what you trying to achieve.

SELECT
  days,
  daily_revenue,
  SUM(ad_revenue) OVER ( ORDER BY days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as running_total_ad_rev
FROM(
  SELECT
    DATE_DIFF(activity_date,creation_date,DAY) AS days,
    SUM(ad_revenue) AS daily_revenue
  FROM
    table1 
  INNER JOIN table2 
    USING (id)
  WHERE
    creation_date >= *somedate* 
    and
    activity_date = *somedate*
  GROUP BY 1
  ORDER BY 1)
ORDER BY 1