1. Window functions
You now have registrations and active users under you belt, but you're limited in what we can do with them.
For example, you can't yet calculate registrations running total, nor can you calculate the growth rate of active users.
2. Window functions - overview
Enter window functions. You've probably seen window functions before, but let's recap.
Window functions perform an operation across a set of rows related to the current row. This sounds abstract, so let's go over some examples.
The registrations KPI only counts registrations by month (or another time period). What if you want the registrations running total by month, so that you know how many registered users you have overall by month? To calculate a running total, you'll need window functions.
You can't compare the current and previous months' monthly active users (or MAUs) unless the two values are in one row. To fetch the value of a previous or following row, you'll also need window functions.
3. Running total
Let's start with running totals. A variable's running total is the cumulative sum of all that variable's values.
In the table above, x is a variable and x_rt is its running total. The first row's value and running total are equal, since there are no previous values to be added to the running total.
Then, each row's running total is the sum of all the previous and the current rows' values. For example, in the third row, x_rt is 6 since the values of x so far are 1, 2, and 3, which sum up to 6.
4. Registrations running total - query
The registrations running total by month is the total number of registered users in each month.
The first and second CTEs are familiar. reg_dates returns each user's registration date, and regs returns the registrations in each month.
In the final query, use SUM on the registrations column over a window, ordering by deliver month in ascending order. This will store the cumulative sum of registrations by month in the regs_rt column.
5. Registrations running total - result
This is the result. The first row's registrations running total is equal to the first row's registrations, because there are no values before it to sum up. Then, each value is a cumulative sum.
6. Lagged MAU - query
Another use for window functions is tracking change over time. That'll be covered in detail next lesson, but for now, let's just set things up.
To measure change, you have to compare a variable's previous value to its current value. The following query returns a table of MAUs and the previous month's MAU for each month.
Set up the query by counting each month's active users and storing the results in the MAUs CTE.
Then, select the month, the MAU column, and use the LAG function to fetch the previous month's MAU. By ordering by delivr_month in ascending order, you guarantee that the previous value fetched is that of the previous month. Since the first month has no previous month, LAG will return a NULL value for it; use COALESCE to set that NULL to 1.
7. Lagged MAU - result
This is the result. Note how the first month's last MAU is 1, since it has no previous month. The second month's last MAU is the first month's MAU, and so on.
8. Window functions
Window functions come in handy when calculating more complex KPIs. Practice using window functions before covering the KPIs that require them.