Creating an amortization schedule in snowflake

I have a view in snowflake that gives me the following:

• loan date
• loan amount
• maturity date
• payment frequency (weekly, biweekly, semimonthly, monthly)
• payment amount

I want to generate a sort of amortization schedule off of this, if you will. So if i have a loan with a date of 1/1/2022, and a maturity date of 3/9/2022 and a payment frequency of biweekly @ $50 per payment, i would want to see an output like:

I'm assuming i need some sort of loop while payment date < maturity date and sum(payment amount) < loan amount, but i'm not sure how to set that up properly for a view with thousands of loans. ANY help you all can provide would be incredible and i'm very grateful!

You can get this by writing a Recursive CTE, just remember that the default is limited to 100 iterations, if you need more loops then check this MAX_RECURSIONS parameter.

This is just an example of code, you should extend it to include some extreme data protection;

Sample data:

CREATE OR REPLACE TABLE LoanTable (
    LoanID STRING,
    Loan_date DATE,
    Loan_amount NUMERIC(12,2),
    Maturity_date DATE,
    Payment_frequency STRING,
    Payment_amount NUMERIC(12,2)
);

INSERT INTO LoanTable
VALUES ('abc123', '1/1/2022', 250, '3/9/2022', 'biweekly', 50);

Query:

WITH Recursive_CTE AS (
  SELECT LoanID, 
         CASE Payment_frequency WHEN 'weekly'      THEN DATEADD(WEEK, 1, Loan_date)
                                WHEN 'biweekly'    THEN DATEADD(WEEK, 2, Loan_date)
                                WHEN 'semimonthly' THEN DATEADD(DAY, 15, Loan_date) -- I don't know how the semimonthly value is determined??
                                WHEN 'monthly'     THEN DATEADD(MONTH, 1, Loan_date) END AS Payment_Date,
         Payment_amount,
         Loan_amount - Payment_amount AS Left_to_pay,
         Payment_frequency,
         Maturity_date
    FROM LoanTable
   UNION ALL
  SELECT LoanID, 
         CASE Payment_frequency WHEN 'weekly'      THEN DATEADD(WEEK, 1, Payment_Date)
                                WHEN 'biweekly'    THEN DATEADD(WEEK, 2, Payment_Date)
                                WHEN 'semimonthly' THEN DATEADD(DAY, 15, Payment_Date) -- I don't know how the semimonthly value is determined??
                                WHEN 'monthly'     THEN DATEADD(MONTH, 1, Payment_Date) END AS Payment_Date,
         Payment_amount,
         IFF(Left_to_pay - Payment_amount < 0, Left_to_pay, Left_to_pay - Payment_amount) AS Left_to_pay,
         Payment_frequency,
         Maturity_date
    FROM Recursive_CTE  
   WHERE Left_to_pay > 0
)
SELECT LoanID, IFF(Payment_Date > Maturity_date, Maturity_date, Payment_Date) AS Payment_Date, Payment_amount, Left_to_pay, Payment_frequency
  FROM Recursive_CTE
 ORDER BY LoanID, Payment_Date;