Running cumulative return in sql

Question

Looking to have a running cumulative return for a series of daily returns? I know this can be solved using exp and sum, but my return series is not calculated using LN.

Hoping to solve this without using loops, as they are very inefficient in sql. Its important to make this run fast.

Dataset:

desired result

Answer 1

Is this what you want?

select t.*,
       (select exp(sum(log(1 + return))) - 1
        from table t2
        where t2.date <= t.date
       ) as cumereturn
from table t;

The functions for exp() and log() may be different in the database you are using. In many databases, you can also use:

select t.*, exp(sum(log(1 + return) over (order by date)) - 1
from table t;

I don't think any database has a built in product() aggregation function. Alas.

Answer 2

converting (1+r)(1+r)(1+r) to

exp(log(1+r) + log(1+r) + log(1+r))

does not work because 1+r could be negative. Negative log is undefined. i.e. the return is less than -100%