Within a window function how to refer back to the column that is being defined, namely reference to itself?

In order to calculate adjusted cost base (ACB), it's a running sum of either Price * Quantity + Commission, or Previous ACB/Share * Quantity, depending on whether it's a sale or a purchase.

I have the following table, named transaction_t:

Date	Action	Quantity	Price	Commission
2021-01-02	buy	150	110.21	5.95
2021-01-21	buy	360	106.87	5.95
2021-03-21	sell	360	106.87	5.95

For calculating a running "adjusted cost base", I have the following query:

SELECT 
  SUM(CASE 
        WHEN T.Action in ("buy", "reinvest") THEN T.Quantity
        WHEN Action = "sell" THEN -T.Quantity
      END
  ) OVER (ORDER BY T.Date) AS quantity_balance,
  SUM(CASE 
        WHEN T.Action in ("buy", "reinvest") THEN T.Quantity * T.Price + T.Commission
        WHEN T.Action = "sell" THEN T.Quantity * ***(previous_total_acb / previous_quantity_balance)***[1]
      END
  ) OVER (ORDER BY T.Date) AS total_acb
  FROM transaction_t AS T;

This query doesn't work. Because the pseudo code previous_total_acb / previous_quantity_balance are refering to the column that are being defined in that window function.

How to make this work in SQLite?

Note [1]: Here the previous_total_acb is a pseuoudo code, I intend it to refer to the column itself, the total_acb. But sqlite doesn't seem to support such looping-back reference. The previous_quantity_balance is referring to the sibling column that is also created by a window function, the quantity_balance. This doesn't seem to work either.

Solution

You would use a CTE (or subquery) to prepare your lookback calculations, and then reference them in your final query. I don't understand your actual sql. Here is a simplified version that might not make sense but simply demonstrates the structure:

This first CTE is just to give some values, which in your case comes from your table:

WITH TBL(d, a, q, p)
AS
(
   SELECT * FROM (
              VALUES (20210102, 'buy',  150, 110.21),
                     (20210121, 'buy',  380, 106.87),
                     (20210321, 'sell', 360, 104.33)
                 )
),

so in reality you would start here with WITH

SRC AS
(
    SELECT d,
           a,
           q,
           p,
           q*p AS cost,
           lag(q*p) OVER (ORDER BY d) AS prev_cost
      FROM TBL
)

Which yields this:

+--------+----+---+------+-------+---------+
|d       |a   |q  |p     |cost   |prev_cost|
+--------+----+---+------+-------+---------+
|20210102|buy |150|110.21|16531.5|NULL     |
|20210121|buy |380|106.87|40610.6|16531.5  |
|20210321|sell|360|104.33|37558.8|40610.6  |
+--------+----+---+------+-------+---------+

and from there you could use the current and previous values to produce a calculated field

SELECT d, cost, cost-prev_cost AS diff FROM SRC ORDER BY d;

which (as I said is just for demonstration and has no real meaning here) yields

+--------+-------+----------+
|d       |cost   |diff      |
+--------+-------+----------+
|20210102|16531.5|NULL      |
|20210121|40610.6|24079.1   |
|20210321|37558.8|-3051.79  |
+--------+-------+----------+

So putting that together and assuming your data is stored in TBL it would look like

WITH SRC AS
(
    SELECT d,
           a,
           q,
           p,
           q*p AS cost,
           lag(q*p) OVER (ORDER BY d) AS prev_cost
      FROM TBL
)
SELECT d, cost, cost-prev_cost AS diff FROM SRC ORDER BY d;

You can have as many CTE's as needed to prep your 'lookback' data. Not sure, but seems you might need at least one more middle prep CTE in your case to calculate previous_total_acb before you can use it in your final query.