title: Within a window function how to refer back to the column that is being defined, namely reference to itself?
body: <p>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.</p>
  <p>I have the following table, named <code>transaction_t</code>:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Date</th>
  <th>Action</th>
  <th>Quantity</th>
  <th>Price</th>
  <th>Commission</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-01-02</td>
  <td>buy</td>
  <td>150</td>
  <td>110.21</td>
  <td>5.95</td>
  </tr>
  <tr>
  <td>2021-01-21</td>
  <td>buy</td>
  <td>360</td>
  <td>106.87</td>
  <td>5.95</td>
  </tr>
  <tr>
  <td>2021-03-21</td>
  <td>sell</td>
  <td>360</td>
  <td>106.87</td>
  <td>5.95</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>For calculating a running &quot;adjusted cost base&quot;, I have the following query:</p>
  <pre><code>SELECT 
    SUM(CASE 
          WHEN T.Action in (&quot;buy&quot;, &quot;reinvest&quot;) THEN T.Quantity
          ​WHEN Action = &quot;sell&quot; THEN -T.Quantity
        END
    ) OVER (ORDER BY T.Date) AS quantity_balance,
    SUM(CASE 
          WHEN T.Action in (&quot;buy&quot;, &quot;reinvest&quot;) THEN T.Quantity * T.Price + T.Commission
          WHEN T.Action = &quot;sell&quot; THEN T.Quantity * ***(previous_total_acb / previous_quantity_balance)***[1]
        END
    ) OVER (ORDER BY T.Date) AS total_acb
    FROM transaction_t AS T;
  </code></pre>
  <p>This query doesn't work. Because the pseudo code <code>previous_total_acb / previous_quantity_balance</code> are refering to the column that are being defined in that window function.</p>
  <p>How to make this work in SQLite?</p>
  <p>Note [1]: Here the <code>previous_total_acb</code> is a pseuoudo code, I intend it to refer to the column itself, the <code>total_acb</code>. But sqlite doesn't seem to support such looping-back reference.
  The <code>previous_quantity_balance</code> is referring to the sibling column that is also created by a window function, the <code>quantity_balance</code>. This doesn't seem to work either.</p>