This article describes how to use the T-SQL Analytic functions in SQL Server database.
The Analytic functions perform a calculation on aggregate value based on a group of rows.
The SQL Server analytic functions are: CUME_DIST, FIRST_VALUE, LAST_VALUE, LAG, LEAD, PERCENT_RANK, PERCENTILE_CONT and PERCENTILE_DISC.

The benefits of using analytic functions in SQL Server include the ability to calculate aggregate values over a partition of data, calculate the total or cumulative sum of a set of data, calculate the average or mean of a set of data, and calculate the standard deviation of a set of data.

These functions can be used to quickly and easily get the information that you need from your data. Additionally, these functions can be used to calculate the aggregate values for a whole table, or for a partition of data within a table.

CUME_DIST

SQL Server CUME_DIST function calculates the relative position of a specified value in a group of values.

select c.*,
CUME_DIST () OVER (ORDER BY c.price) AS CumeDist
from courses c;

FIRST_VALUE

SQL Server FIRST_VALUE function returns the first value in an ordered set of values.

select c.*,
FIRST_VALUE(c.name) OVER (ORDER BY c.price ASC) AS FirstValue_Asc
from courses c order by c.price;

select c.*,
FIRST_VALUE(c.name) OVER (ORDER BY c.price DESC) AS FirstValue_Asc
from courses c order by c.price DESC;

LAST_VALUE

SQL Server LAST_VALUE function returns the last value in an ordered set of values.

select c.price, c.name, rc.id as reg_id,
FIRST_VALUE(c.name) OVER (ORDER BY c.price) AS FirstValue,
LAST_VALUE(c.name) OVER (ORDER BY c.price) AS LastValue
from courses c, register_course rc
where c.id=rc.course_id
order by rc.id;

LAG

SQL Server LAG function returns data from a previous row in the same result. First data value is null.

select c.*,
LAG(c.name) OVER (ORDER BY price) AS PrevCourse
from courses c;

LEAD

SQL Server LEAD function returns data from a next row in the same result. Last data value is null.

select c.*,
LEAD(c.name) OVER (ORDER BY price) AS NextCourse
from courses c;

PERCENT_RANK

SQL Server PERCENT_RANK function calculates the relative rank of a row within a group of rows.

select c.*,
PERCENT_RANK() OVER (ORDER BY c.price ) AS PctRank
from courses c;

PERCENTILE_CONT

SQL Server PERCENTILE_CONT function calculates a percentile based on a continuous distribution of the column value.

select distinct c.*,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY c.id)
OVER (PARTITION BY c.price) AS PercentCount
from courses c, register_course rc
where c.id=rc.course_id;

PERCENTILE_DISC

SQL Server PERCENTILE_DISC function calculates a specific percentile for sorted values in an entire rowset.

select distinct c.*,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY c.id)
OVER (PARTITION BY c.price) AS PercentDisc
from courses c, register_course rc
where c.id=rc.course_id;