javascript stored procedure in snowflake logic

Question

I have a snowflake table like this.

i want to have a SCD type 2 table based on the table shown above. The logic is, when

1.) ID's are the same

2.) data in the table on a given day is always the date for that day and that day - 1 (today and yesterday). At any given time, there is only data for 2 days in the table.

if a owner has been added between today and yesterday, then i need to populate the table in snowflake below like this.

How do I do this using a javascript stored procedure in snowflake? It would be very helpful if you can provide a solution for this.

This is my procedure. I have a problem when I convert result.getColumnValue(2).split(',').map(sub => sub.trim()); into and array since I am unable to insert this into the concatsubadvisoriter temporary table i created in the SP below with the "concat_subadvisor" column type == "ARRAY".I want this column type to be array since I need to do further manipulations.

CREATE OR REPLACE PROCEDURE SUBADVISOR_CHANGES()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
// Drop the temporary table if it exists
var dropSqlQuery = `
    DROP TABLE IF EXISTS DATAHUB_CORE_DEV_DB.SIL_STG_SCHEMA.CONCATSUBADVISOR;
`;
try {
    snowflake.execute({sqlText: dropSqlQuery});

    // Create temporary table DATAHUB_CORE_DEV_DB.SIL_STG_SCHEMA.CONCATSUBADVISOR
    var concatSqlQuery = `
        CREATE TEMPORARY TABLE DATAHUB_CORE_DEV_DB.SIL_STG_SCHEMA.CONCATSUBADVISOR AS (
            SELECT
                CUSIP,
                LISTAGG(SUB_ADVISOR, ',') AS CONCAT_SUBADVISOR,
                TO_VARCHAR(JOB_RUN_DATE, 'YYYY-MM-DD') AS JOB_RUN_DATE
            FROM
                DATAHUB_STG_DEV_DB.FTP_STG_SCHEMA.FP_SUB_ADVISOR_STAGING
            GROUP BY
                CUSIP, JOB_RUN_DATE
        );
    `;
    snowflake.execute({sqlText: concatSqlQuery});

    // Retrieve data from CONCATSUBADVISOR and insert into temporary table CONCATSUBADVISORITER
    var result = snowflake.execute({sqlText: `
        SELECT CUSIP, CONCAT_SUBADVISOR, JOB_RUN_DATE
        FROM DATAHUB_CORE_DEV_DB.SIL_STG_SCHEMA.CONCATSUBADVISOR`
    });
   
    // Drop the temporary table if it exists
    var dropSqlQueryAdvisor = `DROP TABLE IF EXISTS DATAHUB_CORE_DEV_DB.SIL_STG_SCHEMA.CONCATSUBADVISORITER;`;
    snowflake.execute({sqlText: dropSqlQueryAdvisor});
   
    // Create temporary table DATAHUB_CORE_DEV_DB.SIL_STG_SCHEMA.CONCATSUBADVISORITER
    snowflake.execute({sqlText: `
        CREATE TEMPORARY TABLE DATAHUB_CORE_DEV_DB.SIL_STG_SCHEMA.CONCATSUBADVISORITER (
            CUSIP VARCHAR(255),
            CONCAT_SUBADVISOR ARRAY,
            JOB_RUN_DATE DATE
        )
    `});

    // Insert data into CONCATSUBADVISORITER table
    while (result.next()) {
        var cusipStr = result.getColumnValue(1);
        var concatSubadvisorStr = result.getColumnValue(2).split(',').map(sub => sub.trim()); // Split the string into an array and trim whitespace
        var jobRunDate = result.getColumnValue(3);

        snowflake.execute({sqlText: `
            INSERT INTO DATAHUB_CORE_DEV_DB.SIL_STG_SCHEMA.CONCATSUBADVISORITER
            VALUES ('${cusipStr}', ARRAY[${concatSubadvisorStr.map(sub => "'" + sub + "'").join(',')}], '${jobRunDate}')`
        });
    }

    return "Success";
} 
$$;