BigQuery - How to make a transactions report with opening and closing balances?

Question

I am trying to create a tabular report using two tables in BQ. One table is a list of contracts with contract start dates, names and opening balances. The other is a list of transactions that occurred in those contracts (Many-to-one relationship with contracts t.contract_name = c.name).

The issue I am having is based around getting opening and closing balances PER quarter (financial quarters preferred).

I have a fiddle that I am using here in mysql as BQ isn't much different: https://www.db-fiddle.com/f/dXV1oLCgsS8ptAGYrjFmCX/0

Essentially, what I need is: the contract name, date (aggregated in the form of FY23-24Q1 e.g.), an opening balance for that quarter, total advances, total payments, total royalties, a closing balance for that quarter.

I've assumed that I need to use the contract opening balance as the first possible balance and then every subsequent opening balance is the closing balance of the previous quarter.

I'd like to be able to load this data into something like Tableau so that I can provide the user with filters rather than performing contract/date filtering on the data itself in BQ. It seems simple but every time I attempt it I end up in loops where opening balance relies on closing balance or creating endless CTEs or situations where I am summing all opening balances in a given quarter and not the first one.

This also needs to work when there are multiple transactions on the same date in a quarter (as I struggled with that too).

Answer 1

In this query I think it gives you what you want. The problem here is, since I don't have the contract creation date, I just used an arbitrary value, "2023-01-01" to generate the date array for each quarter.

with
contract as
(
  select "Robin" as name, 50 as opening_balance union all
  select "James" as name, 150 as opening_balance
),
transactions as 
(
  select "James" as contract_name, -50 amount, "Advance" as type, date '2023-01-04' as _date union all
  select "James" as contract_name, -50 amount, "Payment" as type, date '2023-03-04' as _date union all
  select "James" as contract_name, -50 amount, "Payment" as type, date '2023-05-04' as _date union all
  select "James" as contract_name, 50 amount, "Royalties" as type, date '2023-09-04' as _date union all
  select "James" as contract_name, 50 amount, "Royalties" as type, date '2023-11-04' as _date
),
trx_quarter as 
(
  select
      contract_name as name,
      date_trunc(_date, quarter) as quarter,
      sum(amount) as amount,
  from transactions
  group by 1,2
)
select 
  name,
  quarter,
  opening_balance as initial_balance,
  opening_balance + coalesce(sum(coalesce(amount, 0)) over (partition by name order by quarter rows between unbounded preceding and 1 preceding), 0) as opening_balance,
  coalesce(amount, 0) as change_amount,
  opening_balance + sum(coalesce(amount, 0)) over (partition by name order by quarter) as end_balance,
from contract as c
join unnest(generate_date_array("2023-01-01", current_date, interval 3 month)) as quarter
left join trx_quarter
  using(name, quarter)