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
>Solution :
use this ROWS UNBOUNDED PRECEDING
SELECT DATE,USER_ID,DEPOST,
SUM(DEPOST) OVER (ORDER BY [DATE] ROWS UNBOUNDED PRECEDING) AS TRACKING_DEPOSIT
FROM TABLENAME