BigQuery sum previous days value-CodePudding

I have a BigQuery or SQL query question, not sure if anyone could help with?

For example,

Transactions

    Date  | user_id | depost
    2021-02-01 | a123 | 200
    2021-02-02 | a123 | 300
    2021-02-03 | a123 | 200
    
    2021-02-01 | a124 | 100
    2021-02-02 | a124 | 100

What I'm expecting is that could have another column that could keep sum current day's value the previous day's value. Thank you so much

Expecting output 

Date  | user_id | depost | tracking_deposit
2021-02-01 | a123 | 200 | 200 
2021-02-02 | a123 | 300 | 500
2021-02-03 | a123 | 200 | 700

2021-02-01 | a124 | 100 | 100
2021-02-02 | a124 | 100 | 200

CodePudding user response：

use this ROWS UNBOUNDED PRECEDING

SELECT DATE,USER_ID,DEPOST,
SUM(DEPOST) OVER (ORDER BY [DATE] ROWS UNBOUNDED PRECEDING) AS TRACKING_DEPOSIT
FROM TABLENAME

CodePudding user response：

Use window function for calculating running total per user wise.

SELECT date
     , user_id
     , depost
     , SUM(depost) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) tracking_deposit
FROM table_name