calculate fees paid in 12 month period

I have created a static calender table in Teradata

CREATE VOLATILE TABLE calender AS (
    SELECT DISTINCT Add_Months(Last_Day(calendar_date) + 1, - 12) AS Period_Start
        ,Last_Day(calendar_date) AS Period_End
    FROM sys_calendar.CALENDAR
    WHERE calendar_date BETWEEN DATE '2024-01-01' AND DATE '2024-06-30'

) WITH DATA PRIMARY INDEX (Period_End)
 ON COMMIT PRESERVE ROWS;

I am joining this with the transcation table.

SELECT m.Period_Start
        ,m.Period_End
        ,t.Account_Id
        ,Sum(t.Amount) AS Fees
        ,Max(t.process_Date) AS Last_Fee_Paid
FROM calender m 
LEFT JOIN Trans t
    ON t.process_Date BETWEEN m.Period_Start AND m.Period_End
    AND t.Transaction_Type IN (
            'One-off Fee'
            ,'On-going Fee'
            )
GROUP BY 1,2,3

I am trying to get when an account paid last fee for each of those periods. Now if they have not paid any fees in that period then the fee value should be 0 and the Last_Fee_Paid should be the value before where they have paid a fee.

Current output:

Expected output:

You can create a cartesian product of calendar and account ids:

WITH calendar_with_accounts AS (
  SELECT * FROM calender
  CROSS JOIN (SELECT DISTINCT Account_Id FROM Trans)
),

transactions AS (
  SELECT
    m.Period_Start
    , m.Period_End
    , m.Account_Id
    , SUM(t.Amount) AS Fees
    , MAX(t.process_Date) AS Last_Fee_Paid
  FROM calendar_with_accounts m 
  LEFT JOIN Trans t
      ON t.Account_Id = m.Account_Id
      AND t.process_Date BETWEEN m.Period_Start AND m.Period_End
      AND t.Transaction_Type IN (
              'One-off Fee'
              ,'On-going Fee'
              )
  GROUP BY 1,2,3
)

SELECT
    Period_Start
    , Period_End
    , Account_Id
    , Fees
    , COALESCE(Last_Fee_Paid, LAST_VALUE(Period_End IGNORE NULLS) OVER(PARTITION BY Account_Id ORDER BY Period_End)) AS Last_Fee_Paid
FROM transactions