Aggregation functions

Lesson 11 of 13 in Coddy's SQL for advanced course.

Aggregation functions are used to calculate the AVG() or MAX() or any other aggregation function up until the current row.
For example, we could calculate the maximum revenue we got until each ending period:

month	revenue
4	40
5	20
6	60
7	55
8	61

SELECT month, revenue, MAX(revenue) OVER (ORDER BY month ASC) as max_revenue
from table1

This will return:

month	revenue	max_revenue
4	40	40
5	20	40
6	60	60
7	55	60
8	61	61

For months 4 and 5 the maximum revenue is 40, for months 5 and 6 the revenue is 60 and for months 8, it is 61. This is because when it finds a new bigger revenue, it drops the old one and uses the biggest so far.

For AVG() function it will look like this:

SELECT month, revenue, AVG(revenue) OVER (ORDER BY month ASC) as avg_revenue
from table1

month	revenue	avg_revenue
4	40	40
5	20	30
6	60	40
7	55	43.75
8	61	47.2

Challenge

Easy

Books have many pages and each page has a lot of words.

In this challenge, we'll calculate many different metrics about these books.

Fetch for each book the total amount of words, the maximum words and the minimum words, and the average words up until each page, call these columns: total_words, max_words, min_words, avg_words respectively. In addition, densely rank the pages (for each book) by calculating the ratio: (avg_words-min_words)/(total_words-min_words) in ascending order for each book, call this column ratio_rank. Finally, order the result by book, and ratio_rank in ascending order.

Try it yourself

All lessons in SQL for advanced

1Introdcution

Introduction Simplify queries, WITH keyword

4Summary

Final challenge #1

2Window Functions part 1

ROW_NUMBER function ORDER BY criterion PARTITION BY criterion LEAD & LAG functions Recap challenge #1 Recap challenge #2

3Window Functions part 2

RANK & DENSE_RANK functions NTILE functionAggregation functionsROWS & RANGE criterion