SQL Puzzle: Calculating engagement with a decay function

<aside> 💡 Update! This has now been solved, and it's a very cool solution — we've included it at the bottom, as well as a peer's evaluation of the two solutions.

</aside>

The challenge:

Each week, a person is given a number of points. These points cumulate, but they also decay — points earned from few weeks ago are worth less as time goes on.

Huh? What does this mean? Let's take a look at an example.

In week 0 (2021-10-04), I earn 3 points. My score for that week is 3.
In week 1 (2021-10-11), I earn 1 point. My score is 3 * 0.9 + 1 = 3.7 — the 3 from last week is decaying.
In week 2 (2021-10-18), I earn 2 points. My score is 3 * 0.9² + 1 * 0.9 + 2 = 5.33 — the 3 from last week continues to decay, and now is the 1
And in week 3 (2021-10-25), I earn 0 points, but my score continued to decay. 3 * 0 .9³ + 1 * 0.9² + 2 * 0.9 = 4.797

How do you implement this sort of equation in Redshift?

Alternate forms of the challenge:

And for those who like equation notation, I think this is right, but it has been a minute since I used this notation.

I can't remember how LaTeX works, so this is a screenshot from Microsoft Word. My bad.

Here it is in table format too:

| name    | week       | points_this_week | score_this_week | calculation                                                    | 
|---------|------------|------------------|-----------------|----------------------------------------------------------------|
| claire  | 2021-10-04 | 3                | 3.00            | (3 * (0.9^0))                                                  |
| claire  | 2021-10-11 | 1                | 3.70            | (3 * (0.9^1)) + (1 * (0.9^0))                                  |
| claire  | 2021-10-18 | 2                | 5.33            | (3 * (0.9^2)) + (1 * (0.9^1)) + (2 * (0.9^0))                  |
| claire  | 2021-10-25 | 0                | 4.797           | (3 * (0.9^3)) + (1 * (0.9^2)) + (2 * (0.9^1)) +  (0 * (0.9^0)) |

You can recreate this table for yourself with this SQL:

with data as (
    select 'claire' as name, '2021-10-04'::date as week, 3 as points_this_week, 3.00 as score_this_week
    union all
    select 'claire' as name, '2021-10-11'::date as week, 1 as points_this_week, 3.70 as score_this_week
    union all
    select 'claire' as name, '2021-10-18'::date as week, 2 as points_this_week, 5.33 as score_this_week
    union all
    select 'claire' as name, '2021-10-25'::date as week, 0 as points_this_week, 4.797 as score_this_week
)
select * from data

In Python, I can calculate use a nested for loop to calculate this — this script works, but we'd prefer to keep the calculation within our warehouse.

points = [3, 1, 2, 0]
number_of_weeks = len(points)

for n in range(number_of_weeks):
    print(f"Week: {n}")
    print(f"Points this week: {points[n]}")
    sum = 0
		# Sigma notation (above) is inclusive, python ranges are not.
		# Need to +1 to handle this
    for i in range(n+1):
        sum += points[i] * pow(0.9, n-i)
    print(f"Score: {sum}")

The challenge:

Alternate forms of the challenge:

Solution 1: Using joins