Excel Quartile function in SQL Server SQL Query

Question

I have an set of values like this:

40
50
50
66
83
100
100
100
100
100
100
100
100
100
100
100
100

When I do the quartile function in excel, i get these four values for my first quartile (25), second quartile(50), third quartile(75) and max (100)

Quartile = 83.33, 100, 100, 100

So when I compare a sales rep who got 80% then they will fall in the bottom quartile according to excel calculation.

I need to redo same functionality in sql and have given my code below.

declare @sales table(
salesRepId int,
percentageSales int)

insert into @sales(salesRepId, percentageSales)
values(1,40)
,(2,50)
,(3,50)
,(4,66.7)
,(5,83.33)
,(6,100)
,(7,100)
,(8,100)
,(9,100)
,(10,100)
,(11,100)
,(12,100)
,(13,100)
,(14,100)
,(15,100)
,(16,100)
,(17,100);

with quintile as(
            select percentagesales, ntile(4) over(order by percentagesales) 
as quintile
            from (select distinct percentagesales from @sales) as s
            )
            select salesrepid, r.percentagesales, q.quintile
            from @sales r
            join quintile q on r.percentagesales = q.percentagesales
            order by q.quintile, percentagesales

When I run this i get the following result set: Query results

salesrepid  percentagesales quintile
1   40  1
2   50  1
3   50  1
4   66  2
5   83  3
6   100 4
7   100 4
8   100 4
9   100 4
10  100 4
11  100 4
12  100 4
13  100 4
14  100 4
15  100 4
16  100 4
17  100 4

Accoridng to sql, the 80% will fall in the medium quartile.

How can I get the four percentile values similar to excel in SQL query

Answer 1

Change int to decimal in percentageSales:

declare @sales table(
    salesRepId int,
    percentageSales decimal(8,2))
    
    insert into @sales(salesRepId, percentageSales)
    values(1,40)
    ,(2,50)
    ,(3,50)
    ,(4,66.7)
    ,(5,83.33)
    ,(6,100)
    ,(7,100)
    ,(8,100)
    ,(9,100)
    ,(10,100)
    ,(11,100)
    ,(12,100)
    ,(13,100)
    ,(14,100)
    ,(15,100)
    ,(16,100)
    ,(17,100);
    
    with quintile as(
                select percentagesales, ntile(4) over(order by percentagesales) 
    as quintile
                from (select distinct percentagesales from @sales) as s
                )
                select salesrepid, r.percentagesales, q.quintile
                from @sales r
                join quintile q on r.percentagesales = q.percentagesales
                order by q.quintile, percentagesales