PERCENT_RANK is a new function introduced in SQL Server 2012. The function calculates the relative rank of a row within a subset of rows or in simpler terms  it shows the percentage of values that are less than or equal to the current value.

The syntax for the function is as follows

 PERCENT_RANK( )
 OVER ( [ partition_by_clause ] order_by_clause )

Let us quickly implement PERCENT_RANK and understand how it works.

Use the following code to create a database with values in it.

USE [Master]
GO
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'PERCENTRANK')
BEGIN
DROP DATABASE PERCENTRANK;
END
GO
CREATE DATABASE PERCENTRANK;
GO
USE PERCENTRANK
GO
CREATE TABLE PERCENTRANKDEMO
(
[ProductId] int,
[SalesQty] int,
[Year] int
);
insert into PERCENTRANKDEMO
VALUES
(10,1000,2005),(20,1215,2005),(30,3327,2005),
(10,2000,2006),(20,2415,2006),(30,3429,2006),
(10,3050,2007),(20,3216,2007),(30,3737,2007),
(10,3026,2008),(20,1618,2008),(30,3452,2008),
(10,2004,2009),(20,4315,2009),(30,5435,2009),
(10,3040,2010),(20,4015,2010),(30,4343,2010),
(10,3060,2011),(20,4345,2011),(30,3237,2011),
(10,3075,2012),(20,4035,2012),(30,7450,2012),
(10,3055,2013),(20,5012,2013),(30,3321,2013),
(10,3059,2014),(20,6019,2014),(30,3245,2014),
(10,3150,2015),(20,6815,2015),(30,4000,2015);

Now lets use the PERCENT_RANK function on the above database created and view the outcome. We will use the following code below.

SELECT ProductId,SalesQty,Year,
PERCENT_RANK() OVER ( ORDER BY [SalesQty] )
AS PercentRank
FROM PERCENTRANKDEMO
WHERE productid = 30;

The outcome would be as below. The column PercentRank shows the percent of values that are less or equal to the current value of the SalesQty column. The first row in any set has a PERCENT_RANK of 0. That’s how it has been designed by default. In the result set 0.1 denotes 10 percent while 1 denotes 100 percent.