I'm looking to providing rank based on two columns ordered by date column, not sure where'-CodePudding

The table structure I've is like this:

The result I want is to have the same rank where account and score are the same for different dates.

query I wrote:

with result as 
(
  select account,score, dense_rank() over(Partition by account,score order by load_date asc) as ranking
  from trans
  )
  select * from trans

the result I got is this:

can anyone help in this, adding table creation and insert statements as well

create table trans
(
  account varchar(500),
  score integer,
  load_date date 
)

insert into trans values('A1',0,'01-Jan-21');
insert into trans values('A1',0,'02-Jan-21');
insert into trans values('A1',0,'03-Jan-21');
insert into trans values('A1',5,'04-Jan-21');
insert into trans values('A1',5,'05-Jan-21');
insert into trans values('A1',5,'06-Jan-21');
insert into trans values('A1',10,'08-Jan-21');
insert into trans values('A1',10,'09-Jan-21');
insert into trans values('A2',10,'05-Jan-21');
insert into trans values('A2',10,'06-Jan-21');
insert into trans values('A2',0,'07-Jan-21');
insert into trans values('A2',0,'08-Jan-21');
insert into trans values('A2',0,'09-Jan-21');
insert into trans values('A2',0,'10-Jan-21');

expected output

account  score  rank  load_date
A1      0      1      2021-01-01
A1      0      1      2021-01-02
A1      0      1      2021-01-03
A1      5      2      2021-01-04
A1      5      2      2021-01-05
A1      5      2      2021-01-06
A1      10     3      2021-01-07
A1      10     3      2021-01-08
A1      10     3      2021-01-09
A2      10     1      2021-01-05
A2      10     1      2021-01-06
A2      0      2      2021-01-07
A2      0      2      2021-01-08

... So on

CodePudding user response：

This feels like a gaps and islands problem. We can use the difference in row numbers method to generate pseudo groups for each island of records having the same score within a date sequence. Then use DENSE_RANK to generate the rank values you want.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY account ORDER BY load_date) rn1,
              ROW_NUMBER() OVER (PARTITION BY account, score ORDER BY load_date) rn2
    FROM trans
),
cte2 AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY account ORDER BY rn1 - rn2) dr
    FROM cte
)

SELECT account, score, load_date, dr
FROM cte2
ORDER BY account, load_date;

Demo