Trouble with recursive query in redshift

Considering following test data:

CREATE TABLE products_su ( country varchar(2), intprd varchar(20), period date, su int ); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-02-01', 7); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-03-01', 15); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-04-01', 35); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-05-01', 105); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-06-01', 140); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-07-01', 180); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-08-01', 261); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-09-01', 211); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-10-01', 187); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-11-01', 318); INSERT INTO products_su (country, intprd, "period", su) VALUES('GL', 'med', '2024-12-01', 208); COMMIT; 

I need to create a SQL query to obtain the results given within below spreadsheet, which also contains formulas that had to be translated into query:

https://docs.google.com/spreadsheets/d/13jYM_jVp9SR0Kc9putPNfIzc9uRpIr847FcYjJ426zQ/edit?gid=0#gid=0

The logic for some of the fields is changing after 6 months and stays for month 7+.

A recursive logic needs to be used in here.

My attempts are good for first 6 months, but then I fail:

WITH RECURSIVE su AS ( SELECT country, period, su::FLOAT AS su, ROW_NUMBER() OVER (PARTITION BY country ORDER BY period) - 1 AS rn FROM products_su ), roll ( country, rn, period, tsu, rep_pat, new_pat, tpe, peq, np_0, np_1, np_2, np_3, np_4, np_5, np_6, np_7, np_8, np_9, np_10, np_11, np_12, np_13 ) AS ( -- Anchor (first month) SELECT country, rn, period, su, CAST(0.0 AS FLOAT), ROUND(su / 4.0, 4), ROUND(su / 4.0, 4), CAST(NULL AS FLOAT), ROUND(su / 4.0, 4), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT) FROM su WHERE rn = 0 UNION ALL -- Recursive rows SELECT s.country, s.rn, s.period, s.su, CASE WHEN s.rn < 6 THEN 0.0 ELSE ROUND((r.np_1 + r.np_2 + r.np_3) / 3.0, 4) END, CASE WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4) ELSE ROUND((s.su - ((r.np_1 + r.np_2 + r.np_3) / 3.0 * 6.0)) / 6.0, 4) END, CASE WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4) ELSE ROUND((s.su - ((r.np_1 + r.np_2 + r.np_3) / 3.0 * 6.0)) / 6.0, 4) END, CASE WHEN s.rn < 13 THEN NULL ELSE ROUND(( CASE WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4) ELSE ROUND((s.su - ((r.np_1 + r.np_2 + r.np_3) / 3.0 * 6.0)) / 6.0, 4) END + r.np_0 + r.np_1 + r.np_2 + r.np_3 + r.np_4 + r.np_5 + r.np_6 + r.np_7 + r.np_8 + r.np_9 + r.np_10 + r.np_11 + r.np_12 ), 4) END, -- Shift new_pat history CASE WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4) ELSE ROUND((s.su - ((r.np_1 + r.np_2 + r.np_3) / 3.0 * 6.0)) / 6.0, 4) END, r.np_0, r.np_1, r.np_2, r.np_3, r.np_4, r.np_5, r.np_6, r.np_7, r.np_8, r.np_9, r.np_10, r.np_11, r.np_12 FROM roll r JOIN su s ON s.country = r.country AND s.rn = r.rn + 1 ) SELECT country, period, ROUND(tsu, 2) AS su, tpe, rep_pat, new_pat, peq FROM roll ORDER BY country, period; 

I will appreciate help on this one.