amazon-redshiftspark-redshiftamazon-redshift-serverless

In Redshift SQL query for reducing years

i have data with fields as shown below

id grade grade_id year Diff
101 5 7 2022 9
105 k 2 2021 2
106 4 6 2020 5
110 pk 1 2022 1

i want to insert records for same id until we reaches grade = pk , Like shown below for every record in the table .

id grade grade_id year Diff
101 5 7 2022 9
101 4 6 2021 8
101 3 5 2020 7
101 2 4 2019 6
101 1 3 2018 5
101 k 2 2017 4
101 pk 1 2016 3

need help in sql code

Solution 
  • create table amish.cte_test
(id int, 
grade int,
year int,
diff int)

insert into amish.cte_test
values (101,5,2022,9)

with recursive temp1( id, grade, year, diff) as
(select id, grade  , year , diff  from amish.cte_test
union all
select id, grade-1, year-1,diff-1 from temp1
where grade-1 > -2) 
select * from temp1