Unexpected Window Function Behavior

Community
1

Hello everyone,

I’m experiencing a strange issue when using a window funtion with nested CTE’s. In my query, a window function that should calculate the average of a true_range column appears to be returning the previous close value instead.

The atr column should contain the 14-period average of the true_range values, instead the atr column appears to be returning the previous close.

I’m running QuestDB 8.2.2 in docker. I’ve also tried this using a nightly build with commit id: 3f4358c.

Any insights or suggestions would be greatly appreciated.

Here’s the query:

WITH true_ranges AS (
    SELECT
        rn,
        ticker,
        timestamp,
        open,
        high,
        low,
        close,
        high-low AS day_range, 
        avg_14_bar_range,
        greatest(high-low, abs(high-prev_close), abs(low-prev_close)) as true_range,
    FROM (
        SELECT 
            timestamp, 
            ticker, 
            open, 
            high, 
            low, 
            close,
            row_number() OVER (PARTITION BY ticker ORDER BY timestamp) as rn,
            avg(high - low) OVER (PARTITION BY ticker ORDER BY timestamp ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) as avg_14_bar_range,
            LAG(close) OVER (PARTITION BY ticker ORDER BY timestamp) AS prev_close,
        FROM stocks_d1_ohlcv
        WHERE ticker = 'SPY'
    )
)

SELECT
    rn, 
    ticker, 
    timestamp, 
    open, 
    high, 
    low, 
    close,
    atr
FROM (
    SELECT 
        rn,
        ticker,
        timestamp,
        open,
        high,
        low,
        close,
        avg(true_range) OVER (PARTITION BY ticker ORDER BY timestamp ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS atr
    FROM true_ranges
)
ORDER BY ticker, timestamp DESC;

When I modify the window function to use a different column like so, it correctly calculates the moving average as expected

avg(avg_14_bar_range) OVER (PARTITION BY ticker ORDER BY timestamp ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS atr

Another strange thing that I noticed is that this query returns the correct true_range, but if I uncomment --atr, both true_range and atr show the previous close

SELECT
    rn, 
    ticker, 
    timestamp, 
    open, 
    high, 
    low, 
    close,
    true_range,
    -- atr
FROM (
    SELECT 
        rn,
        ticker,
        timestamp,
        open,
        high,
        low,
        close,
        true_range,
        avg(true_range) OVER (PARTITION BY ticker ORDER BY timestamp ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS atr
    FROM true_ranges
)
ORDER BY ticker, timestamp DESC; -- slows the query down, but useful for verification