FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE MARKETING_BUDGET_ALLOC (
    PRODUCT_GRP_ID    BIGINT,
    BUDGET_YEAR       BIGINT,
    CAMPAIGN_SEQ_NBR  BIGINT,
    CAMPAIGN_NAME     VARCHAR(100),
    CAMPAIGN_TYPE     VARCHAR(20), 
    CAMPAIGN_COUNT    BIGINT,       
    DIGITAL_CNT       BIGINT,       
    PRINT_CNT         BIGINT,       
    TOTAL_BUDGET      BIGINT,
    ALLOC_PCT         numeric(10,3),
    MAX_ALLOC         BIGINT
);

INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES(1, 2023, 1, 'VisitingCard Ad', 'Print', 1, 3, 0, 90);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES(1, 2023, 1, 'VisitingCard Ad', 'Print', 1, 3, 0, 90);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES(1, 2023, 1, 'VisitingCard Ad', 'Print', 1, 3, 0, 90);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES(2, 2028, 1, 'Paper Ad', 'Print', 3, 2, 0, 2);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (2, 2028, 1, 'Paper Ad', 'Print', 3, 2, 0, 2);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (2, 2028, 2, 'Paper Ad', 'Print', 3, 2, 0, 2);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (2, 2028, 2, 'Paper Ad', 'Print', 3, 2, 0, 2);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (2, 2028, 3, 'Paper Ad', 'Print', 3, 2, 0, 2);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (2, 2028, 3, 'Paper Ad', 'Print', 3, 2, 0, 2);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (3, 2024, 1, 'Paper Ad', 'Print', 1, 2, 0, 62);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (3, 2024, 1, 'Paper Ad', 'Print', 1, 2, 0, 62);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (4, 2024, 1, 'Paper Ad', 'Print', 2, 2, 0, 10209);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (4, 2024, 1, 'Paper Ad', 'Print', 2, 2, 0, 10209);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (4, 2024, 2, 'Paper Ad', 'Print', 2, 2, 0, 10209);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (4, 2024, 2, 'Paper Ad', 'Print', 2, 2, 0, 10209);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (5, 2022, 1, 'Paper Ad', 'Print', 3, 3, 0, 326);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (5, 2022, 1, 'Paper Ad', 'Print', 3, 3, 0, 326);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (5, 2022, 1, 'Paper Ad', 'Print', 3, 3, 0, 326);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (5, 2022, 2, 'Paper Ad', 'Print', 3, 2, 0, 326);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (5, 2022, 2, 'Paper Ad', 'Print', 3, 2, 0, 326);
INSERT INTO MARKETING_BUDGET_ALLOC (PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR, CAMPAIGN_NAME, CAMPAIGN_TYPE,CAMPAIGN_COUNT, DIGITAL_CNT, PRINT_CNT, TOTAL_BUDGET) VALUES (5, 2022, 3, 'Paper Ad', 'Print', 3, 1, 0, 326);

select * from MARKETING_BUDGET_ALLOC;

WITH base_data AS (
        SELECT
            PRODUCT_GRP_ID,
            BUDGET_YEAR,
            CAMPAIGN_SEQ_NBR,
            CAMPAIGN_NAME,
            CAMPAIGN_TYPE,
            CAMPAIGN_COUNT,
            DIGITAL_CNT,
            PRINT_CNT,
            TOTAL_BUDGET
        FROM MARKETING_BUDGET_ALLOC
    )
    ,group_stats AS (
        SELECT
            PRODUCT_GRP_ID,
            BUDGET_YEAR,
            COUNT(*) AS grp_row_count,
            MAX(CAMPAIGN_COUNT) AS campaign_count,
            SUM(DIGITAL_CNT) AS total_digital,
            SUM(PRINT_CNT) AS total_print,
            MAX(TOTAL_BUDGET) AS year_budget
        FROM base_data
        GROUP BY PRODUCT_GRP_ID, BUDGET_YEAR
    ),
    alloc_base AS (
        SELECT
            bd.PRODUCT_GRP_ID,
            bd.BUDGET_YEAR,
            bd.CAMPAIGN_SEQ_NBR,
            bd.CAMPAIGN_NAME,
            bd.CAMPAIGN_TYPE,
            bd.DIGITAL_CNT,
            bd.PRINT_CNT,
            bd.TOTAL_BUDGET,
            gs.year_budget,
            gs.campaign_count,
            (gs.total_digital + gs.total_print) AS total_types,
            ROW_NUMBER() OVER (PARTITION BY bd.PRODUCT_GRP_ID, bd.BUDGET_YEAR ORDER BY bd.CAMPAIGN_SEQ_NBR) AS alloc_seq,
            gs.grp_row_count
        FROM base_data bd
        JOIN group_stats gs
        ON bd.PRODUCT_GRP_ID = gs.PRODUCT_GRP_ID
        AND bd.BUDGET_YEAR = gs.BUDGET_YEAR
    )
    ,alloc_calc AS (
        SELECT
            ab.*,
            -- Base allocation per campaign
            ROUND(year_budget::numeric(10,3) / campaign_count::numeric(10,3)) AS base_alloc,
            -- Calculate the remainder after base allocation
            MOD(year_budget::numeric(10,3), campaign_count::numeric(10,3)) AS remainder
        FROM alloc_base ab
    )
    ,final_alloc AS (
        SELECT
            ac.*,
            -- Distribute the remainder to the first 'remainder' campaigns
            CASE
                WHEN alloc_seq <= remainder THEN ROUND(year_budget::numeric(10,3) / campaign_count::numeric(10,3)) + 1
                ELSE ROUND(year_budget::numeric(10,3) / campaign_count::numeric(10,3))
            END AS max_alloc1,
            TRUNC(
                CASE
                    WHEN campaign_count > 0 THEN ROUND(year_budget::numeric(10,3) / campaign_count::numeric(10,3)) / year_budget::numeric(10,3)
                    ELSE 0
                END, 3
            ) AS alloc_pct1
        FROM alloc_calc ac
    )
    SELECT
        PRODUCT_GRP_ID,
        CAMPAIGN_SEQ_NBR,
        BUDGET_YEAR,
        CAMPAIGN_COUNT,
        DIGITAL_CNT,
        PRINT_CNT,
        TOTAL_BUDGET,
        alloc_pct1 as alloc_pct,
        max_alloc1 as max_alloc
    FROM final_alloc
    ORDER BY PRODUCT_GRP_ID, BUDGET_YEAR, CAMPAIGN_SEQ_NBR;

product_grp_id	budget_year	campaign_seq_nbr	campaign_name	campaign_type	campaign_count	digital_cnt	total_budget
1	2023	1	VisitingCard Ad	Print	1	3	90
1	2023	1	VisitingCard Ad	Print	1	3	90
1	2023	1	VisitingCard Ad	Print	1	3	90
2	2028	1	Paper Ad	Print	3	2	2
2	2028	1	Paper Ad	Print	3	2	2
2	2028	2	Paper Ad	Print	3	2	2
2	2028	2	Paper Ad	Print	3	2	2
2	2028	3	Paper Ad	Print	3	2	2
2	2028	3	Paper Ad	Print	3	2	2
3	2024	1	Paper Ad	Print	1	2	62
3	2024	1	Paper Ad	Print	1	2	62
4	2024	1	Paper Ad	Print	2	2	10209
4	2024	1	Paper Ad	Print	2	2	10209
4	2024	2	Paper Ad	Print	2	2	10209
4	2024	2	Paper Ad	Print	2	2	10209
5	2022	1	Paper Ad	Print	3	3	326
5	2022	1	Paper Ad	Print	3	3	326
5	2022	1	Paper Ad	Print	3	3	326
5	2022	2	Paper Ad	Print	3	2	326
5	2022	2	Paper Ad	Print	3	2	326
5	2022	3	Paper Ad	Print	3	1	326

product_grp_id	campaign_seq_nbr	budget_year	campaign_count	digital_cnt	total_budget	alloc_pct	max_alloc
1	1	2023	1	3	90	1.000	90
1	1	2023	1	3	90	1.000	90
1	1	2023	1	3	90	1.000	90
2	1	2028	3	2	2	0.500	2
2	1	2028	3	2	2	0.500	2
2	2	2028	3	2	2	0.500	1
2	2	2028	3	2	2	0.500	1
2	3	2028	3	2	2	0.500	1
2	3	2028	3	2	2	0.500	1
3	1	2024	1	2	62	1.000	62
3	1	2024	1	2	62	1.000	62
4	1	2024	2	2	10209	0.500	5106
4	1	2024	2	2	10209	0.500	5105
4	2	2024	2	2	10209	0.500	5105
4	2	2024	2	2	10209	0.500	5105
5	1	2022	3	3	326	0.334	110
5	1	2022	3	3	326	0.334	110
5	1	2022	3	3	326	0.334	109
5	2	2022	3	2	326	0.334	109
5	2	2022	3	2	326	0.334	109
5	3	2022	3	1	326	0.334	109

CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1