In this session, you learnt about some of the advanced concepts in SQL. Let’s watch the upcoming video to revisit the topics that were covered in this session one by one.
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:
Named windows: A named window makes it easier to define and reuse multiple window functions. The syntax for a named window is as follows:
Order of SQL statements: The order in which the various SQL statements appear in a query is as follows:
- SELECT
- FROM
- JOIN
- WHERE
- GROUP BY
- HAVING
- WINDOW
- ORDER BY
Frames: Frames are used to subset a set of consecutive rows and calculate moving averages. A query using a frame has multiple components as shown in the diagram given below.
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: