41. Windowed Aggregates

So far in this section we have reviewed functions that can only be used as window functions: lead(), lag(), and row_number(). None of these functions can be used without the over() clause, meaning they are exclusively window functions. However, the aggregate functions we've already learned about like avg(), sum(), and max() can also be used as window functions when combined with the over() clause.

This is a complex topic which I will only be able to scratch the surface of. But hopefully this chapter will establish a foundation of knowledge for you that you can build on as your experience grows.

A Metamorphosis

Aggregate functions can be ✨magically transformed✨ into window functions simply by including an over() clause after them. This conversion allows you keep all the rows in your resultset while still computing something like a sum() over some partition of rows. Let's look at an example. The query below generates a list of all transactions in the database and also includes a column showing each donor's total cumulative giving:

If we take what we know about window functions and the over()clause and apply them to this query, it starts to become clear what this sum() is doing. This tells the database, "Put the rows in my results into groups based on their account_id. Then, sum up the amount values for all rows in each group, and use that number for each row in the group."

As you can see, every row in our results lists details of one single transaction, but our lifetime_amount column displays the sum of all gifts from the donor who made the gift on each row. So donor 4192 has given one gift only, it was $50, and so their lifetime_amount is $50. But donor 18718 has given a bunch of gifts. We've got a row in our results for each one of their gifts and if we were to add all those amounts up, they would total $215. So the lifetime_amount for all of those rows is $215.

We can do this same thing with every single aggregate function.

Exercise 1: Windowed Aggregates

Membership is looking to get a sense of how donors' gift amounts relate to their average gift. They'd like a list of all gifts in the database that includes:

• Donor ID
• Transaction Date
• Amount
• Donor's lifetime average gift amount

Write an SQL query using a windowed aggregate function (with over()) to generate this list.

Exercise 2: More Windowed Aggregates

Development is looking to understand how donors' average gift amount and largest gift amount are related. They'd like a that includes:

• Donor ID
• Transaction Date
• Transaction Channel
• Amount
• Donor's lifetime average gift
• Donor's largest ever gift

The listing should only include donors who have given at least one gift of $1,000 or more, but the average gift calculation should include all gifts. Write an SQL function using windowed aggregates to generate this list.