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.

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;