Inter-Row Operation with Window Functions

You learned about scalar functions such as CASE WHEN, COALESCE, and NULLIF in Chapter 5, Presenting Data with SELECT. These functions receive data from a single row and produce a result for this row. The result of these functions is only determined by the data value in the row and has nothing to do with the dataset it is in. You also learned aggregate functions such as SUM, AVG, and COUNT in Chapter 8, Aggregating Data, with GROUP BY. These functions receive data from a dataset with multiple rows and produce a single result for this dataset. Both types of functions are useful in different scenarios.

You may also want to know the characteristics of a data point regarding its position in the dataset. A common request is to rank rows based on a certain column’s value, such as exam grade or product price. Rank is determined by both the measurement itself and the dataset it is in. A bicycle’s price rank within the bicycle group...