Cumulative product for a random walk

Community
,
2

QuestDB doesn’t have a direct cumulative product function, but as you can see, there is a nice trick (maths to the rescue!) where you can use a window function and get the exponent of the sum of the logarithms for each day, and that would be the same as a cumulative product.

The (it does not work, but keep reading) SQL would be:

 SELECT 
        date,
        return,
        exp(sum(ln(1 + return)) OVER (ORDER BY date)) AS StockPrice
    FROM daily_returns

Problem is that QuestDB does not allow to use any functions on top of a Window Function result, so you need to do that in a query with two steps.

WITH ln_values AS (
    SELECT 
        date,
        return,
        SUM(ln(1 + return)) OVER (ORDER BY date) AS ln_value
    FROM daily_returns
)
SELECT 
    date,
    return,
    100 * exp(ln_value) AS "StockPrice"
FROM ln_values;

Which results in the expected outcome

image