Essential SQL window functions for business operations | Census

To run a successful business today, having good analytics is not only beneficial but almost instrumental. Part of having strong business analytics is having good SQL fluency.

SQL is arguably the most important skill to learn across any type of data-related profession, whether you’re a business operations associate, a product analyst, a data scientist, etc.

This article will look at a particular SQL concept called window functions (aka analytics functions), which is essential for unlocking advanced analytics and deeper insights.

Window functions: What are they and how do they differ from aggregate functions?

A window function is like an aggregate function in the sense that it returns aggregate values (eg. SUM(), COUNT(), MAX()).

What makes window functions different is that it does not group the result set. The number of rows in the output is the same as the number of rows in the input.

The basic anatomy of a window function is as follows:

SELECT SUM() OVER(PARTITION BY ___ ORDER BY ___) FROM Table

To make it easier to understand, there are three main parts to remember:

1. The aggregate function: In the example above, I used SUM() but you can also use COUNT(), AVG(), etc.
2. PARTITION BY: Simply think of this as a GROUP BY clause, but instead, it’s called PARTITION BY.
3. ORDER BY: ORDER BY is the same as you would expect. This is important to consider when the order of your output matters.

In order to get a better understanding of windows functions, and how they can be used in business contexts, we’re going to look at five essential windows functions for business operations.

5 essential window functions

Window functions are a powerful tool in your data kit, and there's a lot of heavy lifting you can do with them. But for today, let's focus on the five essential window functions that'll make your life easier right now:

1. ROW_NUMBER()
2. SUM()
3. AVG()
4. LEAD()/LAG()
5. DENSE_RANK()

1. Get first or last instance via ROW_NUMBER()

ROW_NUMBER() returns the number of each row, which starts at 1 for the first record and increases by 1 for each row following it. ROW_NUMBER() is extremely useful when you want to get the first or last record of a specific table.

When it's useful: If you have a table of customer purchases and you want to get the date of the first purchase for each customer, you can PARTITION BY customer (name/id) and ORDER BY purchase date. Then you can filter the table WHERE row number = 1.

The following query is an example of how you can use ROW_NUMBER() to get the first purchase date for each customer id.

with numbered_transactions as (
    SELECT
        customerId
      , purchaseDate
      , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY
        purchaseDate) as rowNumber
    FROM
        transactions
)
SELECT
    *
FROM
    numbered_transactions
WHERE 
    rowNumber = 1

2. Cumulative sum via SUM()

SUM() is an aggregate function that SUMs the values in a column. By using SUM() or COUNT() in a window function, you can calculate cumulative sums or cumulative counts.

When it's useful: If you want to create graphs that show the growth of a specific metric (i.e. number of orders, revenue, etc…) over time.

The following example shows how you can include a cumulative sum column of monthly revenue:

SELECT
    monthYear
  , monthlyRevenue
  , SUM(monthlyRevenue) OVER (ORDER BY monthYear) as cumRevenue
FROM
    sales

3. Moving averages via AVG()

AVG() function is an aggregate function that returns the average value of a column. AVG() is really powerful in windows functions as it allows you to compute moving averages over time.

When it's useful: If you want to calculate the average spend of your customers, moving averages for sales, or average use statistics for different features within your product. The following query is an example of calculating the 5 day moving average of daily sales.

SELECT
    Date
  , dailySales
  , AVG(dailySales) OVER (ORDER BY Date ROWS 5 PRECEDING) AS
    5_dayMovingAverage
FROM
    sales

4. Deltas via LEAD()/LAG()

LEAD() and LAG() are useful when you want to compare the values of previous rows or later rows. LEAD() and LAG() are mostly used when comparing one period of time with the previous period of time for a given metric.

When it's useful: LEAD() and LAG() are most useful if you're trying to do things like:

• Compare each month’s revenue with the previous month’s revenue
• Calculate the percentage growth in user sign-ups on a monthly basis
• Compare the change in the number of users churned on a monthly basis

The following query shows how you can query the monthly percent change in revenue, given a date column and a monthly revenue column.

with monthly_sales as (
    SELECT
        monthYear
      , monthlyRevenue
      , LEAD(monthlyRevenue) OVER (ORDER BY monthYear) as
        previousMonth
    FROM
        sales
)
SELECT
    monthYear
  , (monthlyRevenue - previousMonth) / previousMonth * 100 AS
    revenuePercentChange
FROM monthly_sales

5. Ranking via DENSE_RANK()

‍DENSE_RANK() is similar to ROW_NUMBER() except that it returns the same rank (number) for equal values.

When it's useful: When you want to rank your data based on a particular variable or variables.

If you wanted to rank your top customers by total sales, DENSE_RANK() would be an appropriate function to use.

SELECT
    customerId
  , totalSales
  , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank
FROM
    customers

Wrapping up your SQL window skills

By reading this article, you should not only know what a window function is, but you should also have a good grasp of its versatility and functionality. There are so many more ways that you can use window functions, but these will take you pretty far.

Hungry to learn more? Check out our collection of data tutorials to sharpen your skills further. 🚀