Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function.

After days, I've boiled the function down to (pseudocode)
where I have N and B, and need to generate E:


En = Bn * (1 - SUM(E1, E2, ... En-1))

Examples:

╔═══╦═════════════╦═════════════╗
║ N ║ B           ║ E           ║
╠═══╬═════════════╬═════════════╣
║ 0 ║ 0.142857143 ║ 0.142857143 ║
║ 1 ║ 0.285714286 ║ 0.244897959 ║
║ 2 ║ 0.285714286 ║ 0.174927114 ║
║ 3 ║ 0.285714286 ║ 0.124947938 ║
║ 4 ║ 0.285714286 ║ 0.089248527 ║
║ 5 ║ 0.4         ║ 0.089248527 ║
║ 6 ║ 0.666666667 ║ 0.089248527 ║
║ 7 ║ 1           ║ 0.044624264 ║
╚═══╩═════════════╩═════════════╝

E0 = 0.143 * (1 - 0) = 0.143

E1 = 0.286 * (1 - 0.143) = 0.245

E2 = 0.286 * (1 - (0.143 + 0.245)) = 0.175

E3 = 0.286 * (1 - (0.143 + 0.245 + 0.175)) = 0.125

E4 = 0.286 * (1 - (0.143 + 0.245 + 0.175 + 0.125)) = 0.089

E5 = 0.400 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089)) = 0.089

E6 = 0.667 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089)) = 0.089

E7 = 1.000 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089 + 0.089)) = 0.044

If the table above was in Excel, C2 = B2 (1 - 0) (base) and C3 = B3 (1 - SUM(C$2:C2)) (recursive)

What I've tried:

Windowed Functions

Tried SUM(...) OVER(ORDER BY [N] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), but can't reference the column recursively.

Recursive CTE

Tried several iterations of:

WITH B AS ([Num], [Best], [Effective Rate]) AS (
SELECT *
, [Best]
FROM A
WHERE [Num] = 0
UNION ALL
SELECT A.*
, (1 - [Effective Rate]) * A.[Best]
FROM B
JOIN A ON A.[Num] = B.[Num] + 1
)


and some with an extra column in the CTE, but it only covers 1 previous row and results after 2nd row are wrong.

Recursive CTE with Windowed Function

From all that I've tried, it seems that the recursive segment of the CTE is calculated independently of the other results, and SUM(...) OVER(...) only w

You need an extra column to carry along the running total (fiddle).

In the recursive part of the CTE below R refers to the "previous" row and A the current one so referencing the column from R is your SUM(E1, E2, ... En-1).

WITH R
     AS (SELECT N,
                B,
                E = B,
                RunningTotalE = B
         FROM   A
         WHERE  N = 0
         UNION ALL
         SELECT A.N,
                A.B,
                E = A.B * ( 1 - R.RunningTotalE ),
                RunningTotalE = A.B * ( 1 - R.RunningTotalE ) + R.RunningTotalE
         FROM   R
                JOIN A
                  ON A.N = R.N + 1)
SELECT N,
       B,
       E = CAST(E AS DECIMAL(10,9))
FROM   R

StackExchange Database Administrators Q#268697, answer score: 13