Rank functions

The different types of rank functions are as follows:

• RANK(): Rank of the current row within its partition, with gaps
• DENSE_RANK(): Rank of the current row within its partition, without gaps
• PERCENT_RANK(): Percentage rank value; it will always lie between 0 and 1
• ROW_NUMBER(): Assigns unique numeric values to each row, starting from 1

Rank function syntax: The syntax for the ‘rank’ function is as follows:

• RANK() OVER ( PARTITION BY <expression>[{,<expression>…}] ORDER BY <expression> [ASC|DESC], [{,<expression>…}] )

Order of SQL statements: The order in which the various SQL statements appear in a query is as follows:

1. SELECT
2. FROM
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. WINDOW
8. ORDER BY

Lead and lag functions: These functions are used to compare a row value with the next or the previous row value. The syntax for the ‘lead’ function is as follows:

• LEAD(expr[, offset[, default]]) OVER (Window_specification | Window_name)