take sum of last 7 days from the observed date in BigQuery

Question

I have a table on which I want to compute the sum of revenue on last 7 days from the observed day. Here is my table -

with temp as 
(
select DATE('2019-06-29') as transaction_date, "x"as id, 0 as revenue
union all
select DATE('2019-06-30') as transaction_date, "x"as id,  80 as revenue
union all
select DATE('2019-07-04') as transaction_date, "x"as id, 64 as revenue
union all
select DATE('2019-07-06') as transaction_date, "x"as id, 64 as revenue
union all
select DATE('2019-07-11') as transaction_date, "x"as id, 75 as revenue
union all
select DATE('2019-07-12') as transaction_date, "x"as id, 0 as revenue
)


select * from temp

I want to take a sum of last 7 days for each transaction_date. For instance for the last record which has transaction_date = 2019-07-12, I would like to add another column which adds up revenue for last 7 days from 2019-07-12 (which is until 2019-07-05), hence the value of new rollup_revenue column would be 0 + 75 + 64 = 139. Likewise, I need to compute the rollup for all the dates for every ID.

Note - the ID may or may not appear daily.

I have tried self join but I am unable to figure it out.

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT *, 
  SUM(revenue) OVER(
    PARTITION BY id ORDER BY UNIX_DATE(transaction_date) 
    RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
  ) rollup_revenue 
FROM `project.dataset.temp`

You can test, play with above using sample data from your question as in example below

#standardSQL
WITH `project.dataset.temp` AS (
  SELECT DATE '2019-06-29' AS transaction_date, 'x' AS id, 0 AS revenue UNION ALL
  SELECT '2019-06-30', 'x', 80 UNION ALL
  SELECT '2019-07-04', 'x', 64 UNION ALL
  SELECT '2019-07-06', 'x', 64 UNION ALL
  SELECT '2019-07-11', 'x', 75 UNION ALL
  SELECT '2019-07-12', 'x', 0
)
SELECT *, 
  SUM(revenue) OVER(
    PARTITION BY id ORDER BY UNIX_DATE(transaction_date) 
    RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
  ) rollup_revenue 
FROM `project.dataset.temp`
-- ORDER BY transaction_date   

with result

Row transaction_date    id  revenue rollup_revenue   
1   2019-06-29          x   0       0    
2   2019-06-30          x   80      80   
3   2019-07-04          x   64      144  
4   2019-07-06          x   64      208  
5   2019-07-11          x   75      139  
6   2019-07-12          x   0       139