Query for cumulative Volume Weighted Average Price

I want to calculate the cumulative VWAP using QuestDB. When I use the built-in vwap function I get the VWAP for the whole table (if I don't use SAMPLE BY), or for each interval when I use SAMPLE BY:

 SELECT 
          timestamp,  symbol, 
          vwap(price, amount)
     FROM trades
     WHERE timestamp IN yesterday()
     AND symbol = 'BTC-USDT'
     SAMPLE BY 10m;

The query gives me the same result as if I do the calculation for each interval myself:

  SELECT 
          timestamp,  symbol, 
          SUM(amount * price) / 
          SUM( amount) 
     FROM trades
     WHERE timestamp IN yesterday()
     AND symbol = 'BTC-USDT'
     SAMPLE BY 10m;

In both versions results are identical and are limited to each interval, without taking into account all the trades in the day so far. I would rather have a cumulative VWAP in which each row uses the values seen since the beginning of the day until that specific row, as this gives me a better trend indicator.

Solution

Window Functions to the rescue!

When using a window function we can define a window frame that can go from the beginning of the rows in the query until the current row. While QuestDB does not have a window version of the vwap function, it offers the SUM function OVER a window of rows, and that's all we need

WITH btc_usdt AS (
    SELECT 
          timestamp,  symbol, 
          SUM(amount) AS volume, 
          SUM(price * amount) AS traded_value
     FROM trades
     WHERE timestamp IN yesterday()
     AND symbol = 'BTC-USDT'
     SAMPLE BY 10m
), cumulative AS ( 
     SELECT timestamp, symbol, 
           SUM(traded_value)
                OVER (ORDER BY timestamp) AS cumulative_value,
           SUM(volume)
                OVER (ORDER BY timestamp) AS cumulative_volume
     FROM btc_usdt
)
SELECT *, cumulative_value/cumulative_volume AS vwap FROM cumulative;

Here I am first doing a regular SUM of the volume and value of the trades in 10 minutes intervals, but we can adjust to any time resolution we prefer. Then on the next CTE I am calculating the cumulative SUM of both volume and value for each 10 minutes intervals but taking into consideration all the rows before, so we get the running total for both. In the outer query we can just divide cumulative_value by comulative_volume, and we get the cumulative VWAP as expected.