How to take the preceding Value based on condition in teradata sql

Question

I meet some issues when trying to get the preceding GRADE based on The BEGINING_DATE (when it change )

Here is my data :

ID	GRADE	BEGINNING_DATE	ENDING_DATE
1	12	14/06/2021	14/06/2022
1	12	14/06/2021	14/06/2022
1	14	22/03/2022	22/03/2023
1	14	22/03/2022	22/03/2023
1	15	22/03/2023	22/03/2024
1	15	22/03/2023	22/03/2024
2	13	15/01/2022	15/01/2023
2	13	15/01/2022	15/01/2023
2	17	01/09/2023	01/09/2024
2	17	01/09/2023	01/09/2024

What i want is to get the preceding GRADE by ID when the BEGINING_DATE Change , here is what i want to get :

ID	GRADE	BEGINNING_DATE	ENDING_DATE	LAG_GRADE
1	12	14/06/2021	14/06/2022	?
1	12	14/06/2021	14/06/2022	?
1	14	22/03/2022	22/03/2023	12
1	14	22/03/2022	22/03/2023	12
1	15	22/03/2023	22/03/2024	14
1	15	22/03/2023	22/03/2024	14
2	13	15/01/2022	15/01/2023	?
2	13	15/01/2022	15/01/2023	?
2	17	01/09/2023	01/09/2024	13
2	17	01/09/2023	01/09/2024	13

I try to use LAG Function but i can't get the result i want to

SELECT
A.*,
LAG(GRADE) OVER (PARTITION BY ID,BEGINING_DATE ORDER BY ENDING_DATE) AS LAG_GRADE
FROM 
DATA A

Can you help me please

Kind Regards

SOLUTION :

With the information of @Fred I find the solution and the query is :

WITH DATA AS (
  SELECT DISTINCT
   ID,
   GRADE,
   BEGINNING_DATE
FROM
    grade_data
),
LAG_DATA AS (
SELECT
  D.*,
  LAG(D.GRADE) OVER (PARTITION BY D.ID ORDER BY D.BEGINNING_DATE ) AS LAG_GRADE
FROM
DATA D
)
SELECT 
  GRADE.*,
  LD.LAG_GRADE
FROM
grade_data GRADE
LEFT JOIN LAG_DATA LD ON GRADE.ID=LD.ID AND GRADE.BEGINNING_DATE = LD.BEGINNING_DATE

Thanks you guys

Answer 1

Here is the solution in SQL server. I think the same logic could be implemented in Teradata SQL.

with prepared_data as
(
    select * , 
           lag(grade , 1) over(partition by id order by convert(date , beginning_date, 103)) as lagged_grade from
    (
        select * 
        from grade_data
        group by id , grade , beginning_date , ending_date
    ) as a
)
select a.* , b.lagged_grade
from grade_data as a inner join prepared_data as b 
on a.grade = b.grade and a.id = b.id and a.beginning_date = b.beginning_date and a.ending_date = b.ending_date;