Tutorial 5:
Window Functions in SQL

Have you ever felt like you're only scratching the surface of what SQL can do? SQL, a powerful tool for managing and analyzing data, has advanced features that many users overlook. One such feature is window functions. Let me explain why.

Window functions allow you to perform calculations across a set of rows related to the current row, all while keeping the detail of your data intact. It's like having the best of both worlds – you get to maintain the granularity of your data while still performing complex calculations.

When I first started learning window functions, I realized that I could use them in some of the course performance reports I run regularly. If we wanted to, we could use them daily to gain insights into student progress and course effectiveness. For example, we could easily calculate the moving average of course completions over time, giving us a clear picture of engagement trends. But instead, the SQL scripts I use contain subqueries and self-joins, which I discussed in previous posts. This is totally fine, and the results are accurate, but the code and logic is often more complex than if we used window functions.

Window functions simplify queries. I used to struggle with complicated subqueries and self-joins, but window functions often replace them with cleaner, more efficient code. This makes our queries easier to write and maintain, and it also significantly improves performance, especially when we're dealing with large datasets.

There's a whole range of window functions, each with its own unique capabilities. We have aggregate functions like SUM and AVG that you might already know, but used as window functions. Then there are ranking functions like ROW_NUMBER and RANK, which we use for everything from simple numbering to complex sales analysis. Distribution functions like PERCENT_RANK are great for statistical analysis, and offset functions like LAG and LEAD help us identify trends.

In this post, we'll explore these different types of window functions and see how they can enhance your approach to data analysis. We'll start with the basics of syntax and concepts, then move on to more advanced applications. Whether you're just starting out with SQL or looking to improve your skills, understanding window functions can open up exciting new possibilities in your data analysis toolkit.

Let's begin our exploration with an introduction to window functions and how they work. Trust me, once you get the hang of these, you'll wonder how you ever managed without them!

Lesson 1 – An Introduction to Window Functions

Imagine being able to analyze your data in a way that reveals hidden trends and patterns. That's exactly what window functions can do. Recall that window functions allow you to perform calculations across a set of related rows while keeping all your original data intact.

So, how do window functions work? The key is the OVER clause, which defines the "window" of rows the function will operate on. Let's take a look at an example:

SELECT bike_number, member_type, duration,
       AVG(duration) OVER (PARTITION BY member_type) AS avg_trip_duration
  FROM tbl_bikeshare;

In this query, we're calculating the average trip duration for each member type. The PARTITION BY member_type part of the OVER clause tells SQL to create separate windows for each member type.

Here's what the output might look like:

You can see how each row shows both the individual trip duration and the average for that member type. That's the beauty of window functions – you get to keep all your detailed data while also seeing the big picture.

You might be wondering, "Couldn't I just use GROUP BY for this?" Well, let's compare:

SELECT member_type, 
       AVG(duration) AS avg_trip_duration
  FROM tbl_bikeshare
 GROUP BY member_type;

This gives us:

While we get the average trip durations, we've lost all the individual trip data. With window functions, we keep both.

As you continue with learning SQL, you'll find that window functions open up a world of possibilities for data analysis. They allow you to perform complex calculations and comparisons without losing the details of your data. Whether you're analyzing bike share data, student performance, or any other dataset, window functions can help you uncover insights you might otherwise miss.

By learning window functions, you'll be able to gain a deeper understanding of your data and make more informed decisions. So, take the time to experiment and see what insights you can uncover. You might just find that window functions become your go-to tool for data analysis.

Lesson 2 – Window Function Framing

When I first learned about window functions, I realized I had discovered a powerful tool for data analysis. But then I checked out window function framing, and I gained a new level of precision in my data analysis. I'd like to share this insight with you.

Window framing allows you to define a specific set of rows within your partition to perform calculations on. Think of it as a magnifying glass that lets you focus on particular sections of your data. This approach is particularly valuable when analyzing time-series data or calculating rolling totals.

To define a window frame, you'll use the ROWS or RANGE keywords, along with frame bounds like PRECEDING, FOLLOWING, and CURRENT ROW. Here's an example of how to calculate a running total of product sales:

SELECT *,
       SUM(quantity) OVER (
              ORDER BY sales_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS running_total_quantity
  FROM apple_sales_quantity_by_month;

This query gives us:

Lesson 3 – Window Aggregate Functions

As the animation above shows, when you write aggregate queries, the columns not included in the GROUP BY clause do not appear in the result set, and you lose the details. Window aggregate functions empower you to perform calculations across rows while preserving your original data intact, giving you a more comprehensive understanding of your data.

Let me explain how they work. Window aggregate functions utilize an OVER clause, which defines the set of rows we're working with – our 'window' of data. Here's a simple example:

SELECT sales_date, brand, model, quantity,
       SUM(quantity) OVER (PARTITION BY sales_date),
       AVG(quantity) OVER (PARTITION BY sales_date)
  FROM phone_sales_quantity;

In this query, we're calculating the sum and average quantity of phones sold each day. The PARTITION BY part tells SQL to create separate windows for each sales date, allowing us to see both aggregate calculations and individual sales data. Check it out:

But it gets even more powerful. We can refine our windows using a window frame. Check this out:

SELECT *,
       AVG(quantity * unit_price) OVER (
              PARTITION BY brand
              ORDER BY sales_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS running_average,
       AVG(quantity * unit_price) OVER (
              PARTITION BY brand
              ORDER BY sales_date
              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ) AS three_month_average
  FROM phone_sales_by_month;

This query performs two different calculations. The first, 'running_average', calculates the average sales from the start of our data up to each row. The second, 'three_month_average', uses just the current row and the two preceding it. This is particularly useful for tracking trends over time. Like this:

When using window aggregate functions, keep the following in mind:

1. Be thoughtful about your PARTITION BY clause. It should group your data in a way that makes sense for what you're trying to analyze.
2. Keep an eye on performance. These functions can be slower with extremely large datasets.
3. Remember that window functions are calculated after most other parts of the query, including the WHERE and ORDER BY clauses. This can affect how you structure complex queries.

Window aggregate functions enable me to ask more complex questions and obtain more nuanced answers, all without losing the details in my dataset. As you continue learning SQL, you'll likely find more ways to leverage these powerful tools to gain deeper insights into your data.

Next, let's explore ranking window functions.

Lesson 4 – Ranking Window Functions

When I first used ranking functions, I could easily identify top performers, group data logically, and reveal trends that might otherwise go unnoticed. Let's explore how these functions work and how you can use them in your own projects.

We'll start with the ROW_NUMBER() function. This function assigns a unique number to each row in your result set. Here's an example using our bike-sharing data:

SELECT start_date, bike_number, member_type, rider_rating,
       ROW_NUMBER() OVER (ORDER BY rider_rating DESC) AS row_num
  FROM trips;

This query assigns a row number to each trip, ordered by rider rating from highest to lowest. I often use this when I need to identify the top N results or create unique identifiers for each row. For instance, you could use it to find the top 10 highest-rated trips of the month. Here's a snippet of the results:

Next, let's look at the RANK() and DENSE_RANK() functions. These are similar to ROW_NUMBER(), but they handle ties differently. RANK() leaves gaps in the ranking when there are ties, while DENSE_RANK() doesn't. Here's an example that compares all three:

SELECT start_date, bike_number, member_type, rider_rating,
       ROW_NUMBER() OVER (ORDER BY rider_rating DESC),
       RANK() OVER (ORDER BY rider_rating DESC),
       DENSE_RANK() OVER (ORDER BY rider_rating DESC)
  FROM trips;

This query gives us:

Notice how RANK() jumps from 1 to 4, while DENSE_RANK() goes from 1 to 2. This difference can be crucial depending on your analysis needs. For example, if you're ranking sales performance and want to highlight the top 3 salespeople, RANK() would ensure you're not giving out more than three "medals" even if there are ties.

Lastly, let's explore the NTILE() function. This function is great for dividing your data into a specified number of groups. Here's an example:

SELECT start_date, bike_number, rider_rating,
       NTILE(2) OVER (
              PARTITION BY EXTRACT(DAY FROM start_date)
              ORDER BY rider_rating DESC
           )
  FROM trips;

This query divides each day's trips into two groups based on rider ratings. It's particularly useful for creating percentiles or segmenting data for analysis. You could use this to identify the top 50% of rated trips each day, which might be valuable for a rewards program or for identifying high-performing bikes. Here are the first few results:

When you're using ranking functions, keep these tips in mind:

1. Choose the right function for your needs. ROW_NUMBER() for unique ranks, RANK() or DENSE_RANK() for handling ties, and NTILE() for grouping.
2. Pay attention to the ORDER BY clause within the OVER() parentheses. This determines how your data is ranked.
3. Consider using PARTITION BY to reset rankings for different groups in your data. For example, you might want to rank bike trips separately for each city or each type of membership.
4. Remember that ranking functions are calculated after the WHERE clause but before the ORDER BY clause in your main query. This can affect how you structure complex queries.

Ranking window functions can help you uncover hidden insights in your data and make more informed decisions. By using these functions, you can identify top performers, group data logically, and reveal trends that might otherwise go unnoticed.

Lesson 5 – Offset Window Functions

Let's explore offset window functions, a valuable SQL technique that helps you analyze your data in new and insightful ways. These functions let you look at previous or future rows in your dataset, making it easier to spot trends and patterns.

The LAG() function is like a rearview mirror for your data, letting you look at previous rows. Here's an example:

SELECT *,
       LAG(revenue) OVER (PARTITION BY brand ORDER BY sales_date) AS prev_month_revenue,
       revenue - LAG(revenue) OVER (PARTITION BY brand ORDER BY sales_date) AS difference
  FROM phone_sales_revenue_by_month;

This query selects all columns from our phone_sales_revenue_by_month table, calculates the revenue from the previous row, and computes the difference between this month's revenue and last month's. We're doing this separately for each brand, ordering by sales_date.

Here's the output:

Similarly, the LEAD() function allows you to look at future rows in your dataset. This can be particularly useful when you want to calculate the difference between the current row and a future row, or when you need to look ahead in time-series data.

Now, let's examine another useful function: FIRST_VALUE().

This function lets you compare every row to the first row in a partition. Here's how it works:

SELECT *,
       FIRST_VALUE(hire_date) OVER (
              PARTITION BY department
              ORDER BY hire_date
           ) AS first_hire_date
  FROM employees;

This query selects all columns from our employees table, identifying the first hire date for each department. This can be helpful when analyzing employee tenure or departmental trends. Here are the results:

Now let's move onto distribution window functions.

Lesson 6 – Distribution Window Functions

As I explored SQL, I stumbled upon distribution window functions. These powerful analytical tools helped me gain a deeper understanding of how my data was spread out, revealing insights that went beyond simple averages.

I first encountered these functions while working on a project at Dataquest. It was a revelation – suddenly, I could see patterns in our course data that weren't visible before. We regularly use these functions to analyze student performance and improve our curriculum.

Let's take a closer look at the PERCENTILE_CONT function. This calculates a continuous percentile, which means it can return interpolated values that may not exist in your dataset. Here's an example using our phone sales data:

SELECT
       PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY quantity) AS "Median of Quantity"
  FROM phone_sales_quantity_by_month;

This query gives us the median quantity of phones sold:

The result, 89.5, is an interpolated value between the two middle values in our dataset. This function is particularly useful when you need a smooth distribution of your data, especially for continuous variables like time or money.

On the other hand, PERCENTILE_DISC returns an actual value from your dataset. It finds the first value that's greater than or equal to the specified percentile. Let's see it in action:

SELECT *
  FROM phone_sales_quantity_by_month
 WHERE quantity >= (
       SELECT PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY quantity) AS "75th percentile of Quantity"
         FROM phone_sales_quantity_by_month
       );

This query identifies all the months where the quantity sold was in the top 25% of sales. The results would show us which months had exceptionally high sales, helping us identify seasonal trends or successful marketing campaigns. Here are the results:

PERCENTILE_CONT is useful when you need a smooth distribution and are okay with interpolated values, while PERCENTILE_DISC is better when you need actual values from your dataset, especially for discrete data like counts or categories.

When working with distribution window functions, keep the following tips in mind:

• Use PERCENTILE_CONT when you need a smooth distribution, even if the resulting values aren't in your dataset. This is ideal for variables like time or money where interpolation makes sense.

• Opt for PERCENTILE_DISC when you need actual values from your data. This is useful for discrete variables or when you need to identify specific data points.

• These functions are excellent for finding outliers or setting benchmarks in your data. For example, you could use them to identify top-performing products or employees.

• Remember that percentiles are sensitive to the distribution of your data. Always visualize your data or use other statistical measures alongside percentiles for a complete picture.

By learning distribution window functions, you'll unlock new insights from your data. They allow you to answer questions like "What's our median sales figure?" or "Who are our top 10% of customers?" with ease. These insights can drive decision-making, helping you identify areas for improvement or opportunities for growth.

As you continue your SQL learning journey, I encourage you to experiment with these functions. Apply them to different datasets and see what insights you can uncover. You might be surprised at the stories your data can tell when you look at it through the lens of distribution functions.

Guided Project: SQL Window Functions for Northwind Traders

Let's see how we can apply our SQL skills to a real-world scenario. In this example we'll walk through a Dataquest guided project where we'll analyze data from Northwind Traders, a fictional international gourmet food distributor. We'll explore how window functions can provide valuable insights for business decision-making.

Imagine you're a data analyst at Northwind Traders. The management team has asked you to dig into the company's data to help them make informed decisions about employee performance, sales trends, and customer behavior. This is where our SQL skills, particularly window functions, come in handy.

One of the first tasks is to evaluate employee performance based on their total sales. For example, we can calculate both a running average and a three-month moving average of sales for each brand.

SELECT *,
       AVG(quantity * unit_price) OVER (
              PARTITION BY brand
              ORDER BY sales_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS running_average,
       AVG(quantity * unit_price) OVER (
              PARTITION BY brand
              ORDER BY sales_date
              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ) AS three_month_average
  FROM phone_sales_by_month;

This calculation helps us understand how employees are performing over time.

Next, let's look at how we can use window functions to calculate running totals of monthly sales. This calculation helps us understand how sales are trending over time. The query above is a great example of this.

In the first part of the query, we're calculating a running average of sales

for each brand. This calculates the average sales up to and including each month, giving us a running average over time.

We use similar techniques at Dataquest to analyze our course engagement over time. By looking at running averages of student activity, we can spot trends and make adjustments to our curriculum or marketing strategies as needed. For example, we noticed that engagement tends to dip around holidays, so we now factor this into our forecasts.

Another important aspect of business analysis is identifying high-value customers. For example, we might want to identify customers whose average order value is above the overall average.

We can use window functions to categorize customers based on their total purchase amounts. This approach would involve using the AVG() function as a window function to calculate the overall average order value, and then comparing each customer's average to this overall average.

Finally, let's consider how we can use window functions to analyze the performance of different product categories. We might want to calculate the percentage of total sales that each category represents.

Again, this would typically involve using the SUM() function both as a window function (to get the total sales across all categories) and as a regular aggregate function (to get the sales for each category). By dividing these, we can calculate the percentage of sales for each category.

This guided project demonstrates how window functions can be applied to real-world business scenarios. By using these SQL techniques, you can uncover valuable insights about employee performance, sales trends, customer behavior, and product performance.

Remember, the key to effective data analysis is asking the right questions and using the right SQL techniques to answer them. As you continue to practice and apply these techniques, you'll become more adept at extracting meaningful insights from your data.

I encourage you to take what you've learned here and apply it to your own data challenges. Whether you're analyzing business data, scientific research, or any other type of information, window functions can help you uncover patterns and insights that might otherwise remain hidden.

Advice from a SQL Expert

When I first discovered SQL window functions, I was amazed by their ability to transform complex queries into elegant, efficient code. As we've explored, these functions open up a world of possibilities for data analysis, from calculating running totals to performing advanced ranking and distribution analysis.

I've come to realize how window functions simplify queries that would otherwise require multiple subqueries or self-joins. By understanding the different types—aggregate, ranking, distribution, and offset—we can tackle a wide range of analytical challenges with greater ease and precision. This understanding has been incredibly valuable in my own work.

If you're feeling a bit overwhelmed by all the new concepts we've covered, don't worry. Learning SQL window functions is a process, and every step forward is progress. The key is to practice regularly and apply these functions to real-world problems. Start with a simple task—perhaps calculating running totals in a sales dataset—and gradually work your way up to more complex analyses. In addition, try to think of ways you can apply window functions to your own work or projects.

The practical applications of window functions are vast. For instance, a retail company might use them to analyze customer purchase patterns over time, identifying trends that inform inventory decisions and marketing strategies. Or a healthcare organization could use window functions to track patient outcomes, comparing individual results against overall averages to improve care quality.

If you're interested in exploring window functions further, our Window Functions in SQL course offers hands-on experience with these powerful tools. We use real-world datasets to solve practical problems, helping you build confidence in your SQL skills. If you're looking to learn even more, our SQL Fundamentals path covers everything from the basics to advanced techniques.

Remember, every SQL expert started as a beginner. Keep practicing, stay curious, and don't be afraid to experiment with your queries. With window functions in your toolkit, you're well-equipped to uncover insights that can drive real value in your analytical work.

Frequently Asked Questions

SELECT sales_date, brand, quantity,
       SUM(quantity) OVER (
              ORDER BY sales_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS running_total_quantity
  FROM apple_sales_quantity_by_month;

SELECT *,
       AVG(quantity * unit_price) OVER (
              PARTITION BY brand
              ORDER BY sales_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS running_average,
       AVG(quantity * unit_price) OVER (
              PARTITION BY brand
              ORDER BY sales_date
              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ) AS three_month_average
  FROM phone_sales_by_month;

SELECT *,
       SUM(quantity) OVER (
              ORDER BY sales_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS running_total_quantity
  FROM apple_sales_quantity_by_month;

AVG(quantity * unit_price) OVER (
       PARTITION BY brand
       ORDER BY sales_date
       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_month_average

SELECT *
  FROM phone_sales_quantity_by_month
 WHERE quantity >= (
       SELECT PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY quantity) AS "75th percentile of Quantity"
         FROM phone_sales_quantity_by_month
       );

SELECT *,
       LAG(revenue) OVER (PARTITION BY brand ORDER BY sales_date) AS prev_month_revenue,
       revenue - LAG(revenue) OVER (PARTITION BY brand ORDER BY sales_date) AS difference
  FROM phone_sales_revenue_by_month;