Alternative approach to correlated sub queries

Alternative approach to correlated sub queries

We are using Zoho Analytics with data from Xero, we wish to show the bank account balances per day, charted over 30 days.

To do this we need the sum of all account transactions to date grouped by account and day. How can I achieve this?
I would normally approach this in SQL with a correlated sub query, something like this:
  2. SELECT 
  3. a."Account ID",
  4.     a."Bank AccountType",
  5.     a."BankAccountNumber",
  6.     a."Name",
  7.     j."Journal Date",
  8.     j."Converted Net Amount",
  9. (SELECT SUM(JournalsInner."Converted Net Amount") FROM "Journals" as JournalsInner WHERE JournalsInner."Account ID" = a."Account ID" AND JournalsInner."Journal Date" <= j."Journal Date") as BalanceToDate 
  10. FROM 
  11. "Accounts" a
  12.     INNER JOIN "Journals" j ON a."Account ID" = j."Account ID"

I understand that correlated sub queries won't work for query tables so how can I achieve this?

Many thanks

Simon