https://www.youtube.com/watch?v=LBWXjtLNauQ&list=PLavw5C92dz9Hxz0YhttDniNgKejQlPoAn&index=11

drop table if exists hotel_ratings;
create table hotel_ratings
(
	hotel 		varchar(30),
	year		int,
	rating 		decimal
);
insert into hotel_ratings values('Radisson Blu', 2020, 4.8);
insert into hotel_ratings values('Radisson Blu', 2021, 3.5);
insert into hotel_ratings values('Radisson Blu', 2022, 3.2);
insert into hotel_ratings values('Radisson Blu', 2023, 3.8);
insert into hotel_ratings values('InterContinental', 2020, 4.2);
insert into hotel_ratings values('InterContinental', 2021, 4.5);
insert into hotel_ratings values('InterContinental', 2022, 1.5);
insert into hotel_ratings values('InterContinental', 2023, 3.8);

select * from hotel_ratings;

WITH
Window Frame
RANK()

-- By default the window frame is [UNBOUNDED PRECEDING AND CURRENT ROW] To change it, it has to be given in OVER() as given below.
WITH CTE AS (
SELECT *,
CAST(AVG(rating) OVER (PARTITION BY hotel Order by year RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DECIMAL(3,1)) AS avg_rating
FROM hotel_ratings
)
, calc AS (
SELECT *,
ABS(rating-avg_rating) AS abs,
RANK() OVER (PARTITION BY hotel ORDER BY ABS(rating-avg_rating) DESC) AS rn
FROM CTE
)

SELECT hotel,year,rating FROM calc WHERE rn > 1
ORDER BY hotel DESC;