WINDOW FUNCTIONS

What are Window Functions?
It is a function that performs calculations across a set of rows related to the current one and returns the result of its computation for each row. Unlike GROUP BY, Window functions in PostgreSQL do not aggregate values and therefore do not reduce the number of rows in the output dataset. All rows from the input table are present in the output, and computations are attached to the corresponding rows.

In other words, you can think of a window function as a computation that for each row requests a value that is the result of some calculation applied to a certain set of rows (a window).

In our previous article we have 'Postgresql functions' we had gone through aggregate functions,therefore we will not repeat aggregate functions . A Window function has the following syntax

1.Value
1.1 FIRST_VALUE()
The FIRST_VALUE() function allows the retrieval of the value of a column from the first row within a partition.

The FIRST_VALUE() function is applied to the First_name column and ordered by Start_date. It returns the First_name from the
first row as First_in_dept.

1.2 LAST_VALUE()
The LAST_VALUE() function allows the retrieval of the value of a column from the last row within a window frame.

The LAST_VALUE() function is applied to the First_name column
and ordered by Start_date. It returns the First_name from the
first row as Last_employee.

1.3 LEAD()
The LEAD(column, n) function allows access of a value within a column from the following nth-row relative to the current row. It is the counterpart of the LAG() function.

The LEAD(Salary, 1) function is applied to the Salary column and
ordered by Date_started. It returns the salary from the previous
row, since n = 1, as the column Next_salary.

1.4 LAG()
The LAG(column, n) function allows access of a value within a column from the previous nth-row relative to the current row.

The LAG(Salary, 1) function is applied to the Salary column and
ordered by Date_started. It returns the salary from the previous
row, since n = 1, as the column Previous_salary.

2.0 Ranking()
Ranking window functions assign a rank or row number to each row within a specified window or subset of rows. Ranking window functions typically need an ORDER BY clause in order to work as intended. They include

2.1 RANK()
The RANK() function assigns a rank to each row based on the order specified within the window. Rows with the same values receive the same rank, and the next rank is skipped.

2.2 DENSE_RANK()
The DENSE_RANK() function operates similarly to the RANK() function except it does not skip any ranks even if rows have the same values.

2.3 ROW_NUMBER()
The ROW_NUMBER() function assigns a unique sequential number to each row within a partition, regardless of the column values. It makes sure that no two rows can have the same row number within a division.

2.4 NTILE()
NTILE() divides sorted partitions into n-number of equal groups. Each row in a partition is assigned a group number.

I would advise you to read and practice these functions for you to master them.