Examples of Using Window Functions for Data Analysis

Now you’re ready to dive into the world of practical examples and see how all this stuff works in real-life tasks!

Example: Calculating Sales Rank by Region

Let’s say we have a sales table with sales data from different regions. We need to figure out the sales ranks for each region.

Task: find the RANK of sales for each region

SELECT
    region,
    sales_amount,
    RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM 
    sales;

Result:

Notice that we used PARTITION BY region to calculate the ranks separately for each region. If we didn’t use PARTITION BY, the rank would be calculated globally for the whole table.

Example: Calculating Cumulative Revenue Sum

Now let’s work with the transactions table to calculate the cumulative (running) revenue sum for each customer.

Task: calculate the cumulative sum for each customer

SELECT
    customer_id,
    purchase_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM 
    transactions;

Result:

The key thing here is using ORDER BY purchase_date inside OVER() so the cumulative sum is calculated in chronological order.

Example: Splitting Data into Quantiles

Imagine we have a students table with names and test scores. We want to split the students into 4 groups based on their scores.

Task: split students into 4 groups using NTILE()

SELECT
    name,
    test_score,
    NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM 
    students;

The query result will look like this:

NTILE(4) splits the data into 4 groups. Students with the highest scores end up in the first group, and those with the lowest — in the last one.

Example: Time Series Analysis

The site_visits table stores data about the number of site visits per day. We need to calculate the difference in visits between days for each site.

Our task — calculate the difference in visits between days

SELECT
    site_id,
    visit_date,
    visits,
    visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM 
    site_visits;

Result:

The LAG() function lets you grab the value from the previous row. If there’s no previous row, the result will be NULL. You’ll learn more about it in the next lectures :P

2

Task

SQL SELF, level 30, lesson 1

Locked

Sales Rank Calculation

Sales Rank Calculation

Open