-- Создание таблиц
if object_id('tempdb..#player') is not null
	drop table #player;
go
if object_id('tempdb..#change_rating') is not null
	drop table #change_rating;
go
create table #player(id_user varchar(50), score int);
go
create table #change_rating(id_rating int identity, id_user varchar(50), rt_dt datetime, rt_current int, rt_prev int);
go
-- Заполнение данными
insert into #player(id_user, score) values ('player1', 20);
insert into #player(id_user, score) values ('player2', 10);
insert into #player(id_user, score) values ('player3', 30);
go
select * from #player order by score desc;
go
-- Добавление позиций рейтинга
insert into #change_rating(id_user, rt_current, rt_dt, rt_prev)
select id_user, ROW_NUMBER() OVER(ORDER BY score desc) AS rt, getdate() as dt,
	(select max(rt_current) from #change_rating r where r.id_user = a.id_user and r.rt_dt = (select max(rt_dt) from #change_rating r2 where r2.id_user = r.id_user)) as prev_rt
from #player a;
go
-- Меняем количество очков
update #player
	set score = 100
where id_user = 'player2';
select * from #player order by score desc;
go
-- Добавление позиций рейтинга
insert into #change_rating(id_user, rt_current, rt_dt, rt_prev)
select id_user, ROW_NUMBER() OVER(ORDER BY score desc) AS rt, getdate() as dt,
	(select max(rt_current) from #change_rating r where r.id_user = a.id_user and r.rt_dt = (select max(rt_dt) from #change_rating r2 where r2.id_user = r.id_user)) as prev_rt
from #player a;
go
-- История изменения рейтинга
select r.*, (r.rt_current - r.rt_prev) change_rt 
from #change_rating r
order by r.rt_dt desc, r.rt_current asc;
go