BigQuery - inventory balance calculation on an ad-hoc basis

Question

I have an inventory transaction BigQuery table as follows (apologies for not being able to post images).

The balance calculation is required periodically and is part of the table.

Existing Data Table

Expected result is an additional column containing stock balance on the day the calculation is done.

Expected Result

Thanks in advance for any thoughts.

Answer 1

I think a better table structure is to have the stock balance as part of your default columns and just add another one that will hold the date/ timestamps of the period it was calculated.

stock_balance	date_time_calculated
0
3	2023-08-01 00:05:01
0
4	2023-09-01 22:30:41
0

What will happen is, whenever a calculation is made, the system will record its date/ timestamp. I believe this method needs lesser codes/ queries to write and will allow you to track the periods of your calculations.