3

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!

Michael Kuisle
  • 31
  • 1
  • Your answer is here: https://stackoverflow.com/questions/27557919/bigquery-sql-for-28-day-sliding-window-aggregate-without-writing-28-lines-of-sq – Sabri Karagönen Mar 18 '20 at 17:46

1 Answers1

0

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
)
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230