Calculate cumulative product value

Tags: common-table-expression, sql, sql-server, tsql

I have the following database table:

```Date        Return  Index
01-01-2020  0.1     Null
01-02-2020  0.2     Null
01-03-2020  0.3     Null
```

I would like to update the Index value using the following formula:

```Index = (Previous_Month_Index * Return) + Previous_Month_Index (Use 100 for Previous_Month_Index for the first month)
```

Expected Result: (Index to be calculated order by Date asc)

```Date        Return  Index
01-01-2020  0.1     110  -- (100 + 10)
01-02-2020  0.2     132  -- (110 + (110 * 0.20)) = 110 + 22 = 132
01-03-2020  0.3     171.6  -- (132 + (132 * 0.30)) = 132 + 39.6 = 171.6
```

How can I do this using SQL? I tried the following query but getting an error:

Windowed functions cannot be used in the context of another windowed function or aggregate.

```--first, load the sample data to a temp table
select *
into #t
from
(
values
('2020-01-01', 0.10),
('2020-02-01', 0.20),
('2020-03-01', 0.30)
) d ([Date], [Return]);

--next, calculate cumulative product
select *, CumFactor = cast(exp(sum(log(case when ROW_NUMBER() OVER(order by [Date] ASC)  = 1 then 100 * [Return] else [Return] end)) over (order by [Date])) as float) from #t;

drop table #t
```

Thinking mathematically, the result that you want is equivalent to this product:

```100 * (1 + a1) * (1 + a2) * (1 + a3) * ....
```

where a1, a2, a3 are the values of the column `[Return]`.

This product can be obtained by:

```100 * EXP(SUM(LOG(1 + [Return])))
```

and you can do this in sql like this:

```SELECT *,
100 * EXP(SUM(LOG(1 + [Return])) OVER (ORDER BY [Date])) [Index]
FROM #t
```

See the demo.

Source: stackoverflow