OVER: calculating without losing the rows

GROUP BY reduces rows to one: you learn the genre's average and the books are gone. A window function does the same calculation and leaves the rows where they are: next to each book goes its genre's average, or its rank within the genre.

1SELECT title, genre, price,
2       RANK() OVER (PARTITION BY genre ORDER BY price DESC) AS place
3FROM books;

OVER (...) says which rows the function looks at. PARTITION BY genre means "each genre on its own" and ORDER BY price DESC "from expensive to cheap". RANK() gives the place in that order; equal prices share a place and the next place is skipped (1, 1, 3). For no skipping use DENSE_RANK(), and to tell even the ties apart, ROW_NUMBER().

With an ORDER BY in the window, aggregates run cumulatively: SUM(amount) OVER (ORDER BY month) writes next to each month the total up to that month. LAG(amount) OVER (ORDER BY month) brings the value of the row before; subtract the two and you have the change from month to month. The first row has nothing before it, so LAG gives NULL there.

Tasks