For loop with output arrays

Question

In snowflake : I have two tables available:

• "SEG_HISTO": This is a segmentation run once a month. columns: Client ID /date (1st of each month) /segment.
• "TCK": a table that contains the tickets with the columns: Ticket ID / Customer ID / Date / Amount.

For each customer ID in the "SEG_HISTO" table, I searched for all the customer's tickets over a rolling year and associated the sum of the amount spent:

SELECT SEG_OMNI.*, TCK_12M.TOTAL_AMOUNT_HT
FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_OMNI" SEG_OMNI
LEFT OUTER JOIN
(
SELECT DISTINCT PR_ID_BU,
    SUM(TOTAL_AMOUNT_HT) AS "TOTAL_AMOUNT_HT",
    COUNT(*) "NB_ACHAT"
    
    FROM 
        (
        SELECT * FROM "SHARE"."RAW_BDC"."TCK"
        WHERE TO_DATE(DT_SALE) >= DATEADD(YEAR, -1, '2022-07-01') -- <<<=====  date add manually
        )
    GROUP BY PR_ID_BU
) TCK_12M
ON SEG_OMNI."pr_id_bu" = TCK_12M.PR_ID_BU

Now I need to create a for loop that iterates this for each date in the SEG_OMNI table (SELECT DISTINCT TO_DATE(DT_MAJ) DT FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_HISTO") and stack the output in a view.

And it is at this level where I block

Thank you for your help in advance

Answer 1

As Dave said in the comments, it would be better if you could figure out how to run all this in one query, instead of running the same query multiple times.

But as you are asking how to output the results of multiple queries out of one stored procedure I'm going to give you the pattern for that here. I'm also assuming you want this in a SQL script (we could use Python/Java/JS instead):

declare
    your_var string;
    all_dates cursor for (
        select dates
        from your_table
    );
begin
    -- create a table to store results
    create or replace temp table discovery_results(x string, y string, z int);
    for record in all_dates do
        -- for each date run the query an insert results into the table created
        insert into discovery_results  
            select x, y, z
            from the_query
            where (:dates_cursor_data)
        ;
    end for;
    return 'run [select * from discovery_results] to find the results';
end;


select *
from discovery_results