Calculating Bollinger Bands with QuestDB

I want to calculate Bollinger bands in QuestDB. I tried using directly the postgresql query seen at Preparing bollinger bands with postgresql - STDDEV and PARTITION BY, but I get an error. This is the query

with OHLC AS (
  SELECT
    timestamp, symbol, 
      first(price) AS open, 
      max(price) as high, 
      min(price) as low,
      last(price) AS close, 
      sum(amount) AS volume
 FROM trades
 SAMPLE BY 15m
)
SELECT symbol, timestamp, open, high, low, close, volume,        
    AVG(close) OVER
        (PARTITION BY symbol
            ORDER BY timestamp
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS SMA20,
    AVG(close) OVER
        (PARTITION BY symbol
            ORDER BY timestamp
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) +
        STDDEV_SAMP(close) OVER
            (PARTITION BY symbol
            ORDER BY timestamp
                ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) * 2 AS Upper_Bollinger_Band,
    AVG(close) OVER
        (PARTITION BY symbol
            ORDER BY timestamp
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) -
        STDDEV_SAMP(close) OVER
            (PARTITION BY symbol
            ORDER BY timestamp
                ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) * 2 AS Lower_Bollinger_Band
    FROM OHLC;

I an getting the error identifier should start with a letter or '_'

This seems to indicate I cannot use an operation after a window function in QuestDB, and it is interpreting the plus sign as an alias for the column, so I thought I could just calculate stddev and avg in a subquery, then do the computations in an outer query. But when I try to just use STDDEV_SAMP I get another error, as the function is available as an aggregation function, but not as a window function.

with OHLC AS (
  SELECT
    timestamp, symbol, 
      first(price) AS open, 
      max(price) as high, 
      min(price) as low,
      last(price) AS close, 
      sum(amount) AS volume
 FROM trades
 SAMPLE BY 15m
)
SELECT symbol, timestamp, open, high, low, close, volume,        
    AVG(close) OVER
        (PARTITION BY symbol
            ORDER BY timestamp
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS SMA20,
    STDDEV_SAMP(close) OVER
            (PARTITION BY symbol
            ORDER BY timestamp
                ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)        
    FROM OHLC;

At this point I am not sure how to calculate Bollinger Bands.

The intuition to use a subquery for the window functions, then another one on top is spot on. But since QuestDB does not implement STDDEV_SAMP as a window function, we need a workaround.

The query below will calculate the moving average of the square of the close for the same amount of rows than the average, and we can use that to calculate the stdev by doing the square root of the avg close square minus the square of the moving average.

with OHLC AS (
  SELECT
    timestamp, symbol, 
      first(price) AS open, 
      max(price) as high, 
      min(price) as low,
      last(price) AS close, 
      sum(amount) AS volume
 FROM btc_trades
 SAMPLE BY 15m
),    stats AS (
  SELECT
    timestamp,
    close,
    AVG(close) OVER (
      ORDER BY timestamp
      ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) AS sma20,
    AVG(close * close ) OVER (
      ORDER BY timestamp
      ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) AS avg_close_sq
  FROM OHLC
)
SELECT
  timestamp,
  sma20,
  sqrt(avg_close_sq - (sma20 * sma20)) as stdev20,
  sma20 + 2 * sqrt(avg_close_sq - (sma20 * sma20)) as upper_band,
  sma20 - 2 * sqrt(avg_close_sq - (sma20 * sma20)) as lower_band
FROM stats
ORDER BY timestamp;

When I plot this over some historical trading data, I get this