SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table my_table (
  session_date	date,
  price int
);

insert into my_table values
('2022-12-31',	4),
('2022-12-30',	4),
('2022-12-29',	9),
('2022-12-28',	7),
('2022-12-27',	2),
('2022-12-26',	4),
('2022-12-25',	3),
('2022-12-24',	1);
8 rows affected

select price, max(session_date) as session_date
  from my_table t
  group by price


price
session_date


1
2022-12-24

2
2022-12-27

3
2022-12-25

4
2022-12-31

7
2022-12-28

9
2022-12-29


with cte as (
  select session_date
  , price
  , max(price) over (order by session_date rows between 3 preceding and current row) as rolling_max  from my_table
)
select c.*, t.session_date as rolling_last_date
from cte c
inner join (
  select price, min(session_date) as session_date
  from my_table t
  group by price
) t on t.price = c.rolling_max 
order by session_date desc


session_date
price
rolling_max
rolling_last_date


2022-12-31
4
9
2022-12-29

2022-12-30
4
9
2022-12-29

2022-12-29
9
9
2022-12-29

2022-12-28
7
7
2022-12-28

2022-12-27
2
4
2022-12-26

2022-12-26
4
4
2022-12-26

2022-12-25
3
3
2022-12-25

2022-12-24
1
1
2022-12-24

price	session_date
1	2022-12-24
2	2022-12-27
3	2022-12-25
4	2022-12-31
7	2022-12-28
9	2022-12-29

session_date	price	rolling_max	rolling_last_date
2022-12-31	4	9	2022-12-29
2022-12-30	4	9	2022-12-29
2022-12-29	9	9	2022-12-29
2022-12-28	7	7	2022-12-28
2022-12-27	2	4	2022-12-26
2022-12-26	4	4	2022-12-26
2022-12-25	3	3	2022-12-25
2022-12-24	1	1	2022-12-24