One Pager Cheat Sheet

• Window functions allow for the calculation of values over multiple rows while preserving individual row identities, with the "OVER" clause allowing for division of multiple rows into a set and ordering them sequentially.
• Aggregate window functions are a set of aggregate functions such as _SUM(), COUNT(), AVG(), MAX(),_ and _MIN()_ which can be applied to a window of data and compute statistical values such as _average age_, _order by number of orders_ and more.
• Ranking Window functions such as **RANK()**, **DENSE_RANK()**, and **ROW_NUMBER()** can be used to assign ranks and row numbers to partitions depending on the specified criteria, and the resulting table can then be used for further analysis.
• The RANK() function can be used to assign each row a rank, where the first row is given a rank of 1 and any subsequent rows with the same value have the same rank, skipping one rank value when two identical rank values occur.
• With the use of NTILE(), LEAD(), and LAG() functions, you can easily extract values from other rows to compare data over time, and allocate them into percentile, quartiles, or other divisions.
• Yes, it is possible to utilize LEAD() and LAG() functions together in the same query for comparison of values from rows before and after the current row.