Trouble with recursive query in redshift

Question

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.

Bill Weiner · Accepted Answer · 2025-04-05 14:50:39Z

0

Only your first CTE can be recursive. Your select to make "su" needs to be a subquery in the roll recursive cte. Also you have no termination condition - a WHERE clause that will end the recursive looping.

You should clearly state the question and define what the expected result it. Based on what you have posted it doesn't look like you have tried much debug / reducing the issue to its core. Overly complex "do my job for me" question are going to get a low response rate. I also suggest that when posting for help that you include all the needed information in the post - people don't have time to hunt down your google sheets and figure out what you are trying to do.

answered Apr 5 at 14:50

Bill Weiner

11.4k2 gold badges12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trouble with recursive query in redshift

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related