Quartile Number for group

Question

 Hi, I am trying to get quartile target each member, can you please help me with the logic?

 

 

 

create table sc

(

Name varchar(30),

Score int

)

insert into sc values ('abc',100)

insert into sc values ('xyz',200)

insert into sc values ('xyz2',90)

insert into sc values ('abc2',80)

insert into sc values ('abc3',300)

insert into sc values ('ahz',50)

insert into sc values ('Sai',70)

insert into sc values ('Raj',0)

;with test as (

select Name,Score,row_number() over(order by score) rank from sc

)

select Name,Score,rank,

(

case when rank<=(select count(1)*.25 from test) then 1

when rank<=(select count(1)*.50 from test) then 2

when rank<=(select count(1)*.75 from test) then 3

else 4 end

) Quartile from test