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.

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:

```CODE language-sql```

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

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

**The aggregate function**: In the example above, I used SUM() but you can also use COUNT(), AVG(), etc.**PARTITION BY**: Simply think of this as a GROUP BY clause, but instead, it’s called PARTITION BY.**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.

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:

- ROW_NUMBER()
- SUM()
- AVG()
- LEAD()/LAG()
- DENSE_RANK()

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.

```CODE language-sql```

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

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:

```CODE language-sql```

SELECT

monthYear

, monthlyRevenue

, SUM(monthlyRevenue) OVER (ORDER BY monthYear) as cumRevenue

FROM

sales

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.

```CODE language-sql```

SELECT

Date

, dailySales

, AVG(dailySales) OVER (ORDER BY Date ROWS 5 PRECEDING) AS

5_dayMovingAverage

FROM

sales

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.

```CODE language-sql```

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

****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.

```CODE language-sql```

SELECT

customerId

, totalSales

, DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank

FROM

customers

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. 🚀

No items found.

Related integrations

No items found.

Get the best data & ops content (not just our post!) delivered straight to your inbox