Running Sum of Last 30 days by day

user_12561547 0
Viewed 568

I am looking for a query that sums all the leads from the last 30 days (including that day) by each day.

For example...

if date=8/1/18 then I want a sum of the leads from 7/2/18 to 8/1/18
if date=3/17/20 then I want a sum of the leads from 2/17/20 to 3/17/20

Here is a link to a sheet where the concept is layed out

Thank you in advance for any assistance!

google-bigquery
1 Answers

Below is for BigQuery Standard SQL 

#standardSQL
SELECT *, 
  SUM(leads) OVER(last_30_days) AS running_sum
FROM `project.dataset.table`
WINDOW last_30_days AS (
  ORDER BY UNIX_DATE(date) RANGE BETWEEN 29 PRECEDING AND CURRENT ROW
)
user_52219440
Related