Calculate the compound running total of a calculation

Question

I am trying to achieve the following results

enter image description here

Formula for Compound running total is (value + 1) * (previous_value + 1) - 1

I am trying to execute the following query in synapse

WITH cte AS (
    SELECT date, value, ROW_NUMBER() OVER (ORDER BY date) AS seqnum
    FROM table_name
), cte2 AS (
    SELECT cte.date, cte.value, cte.seqnum
    FROM cte
    WHERE seqnum = 1
    UNION ALL
    SELECT cte.date, (cte.value + 1) * (cte2.value + 1) - 1, cte.seqnum
    FROM cte2
    JOIN cte ON cte.seqnum = cte2.seqnum + 1
)
SELECT cte2.date, cte2.value
FROM cte2;

I am getting the following error:

Recursive common table expressions are not supported in this version.

Answer 1

You may not need recursion. A compound interest calculation is essentially a running product calculation. It would be nice if DBMSs would implement aggregate prod() function similar to sum(), but most (all?) do not. The good news is that you can change your product operation into a sum operation using logarithms.

(Anyone who remembers mechanical slide rules or is familiar with the inner workings of WWII-era battleship targeting computers knows that.)

The equivalent of a hypothetical prod(value) function is exp(sum(log(value))). When dealing with relative percentages or fractions (offset by 100% or 1.0), the equivalent is exp(sum(log(value + 1))) - 1.

Your query might then become something relatively simple like:

SELECT
    D.*,
    EXP(SUM(LOG(D.Interest + 1)) OVER(ORDER BY D.Id)) - 1 AS CummulativeInterest
FROM Data D
ORDER BY D.Id

I am not sure if this will work as written in Synapse, but your original posted query seemed to support windowed aggregates.

Results:

Id	Interest	Cummulative Interest (Rounded)	Cummulative Interest (Raw)
1	0	0	0
2	0.0606	0.0606	0.0606
3	0.0031	0.0639	0.0638878600000001
4	0.0002	0.0641	0.0641006375720001
5	0.0006	0.0647	0.0647390979545432
6	0	0.0647	0.0647390979545432
7	0	0.0647	0.0647390979545432
8	0.0439	0.1115	0.111481144354748
9	0	0.1115	0.111481144354748
10	0	0.1115	0.111481144354748
11	0.0001	0.1116	0.111592292469183
12	0	0.1116	0.111592292469183

See this db<>fiddle for a demo.