Skip to content

Step Up Your Business Data Analytics Game: Analyzing Sales Performance with SQL Window Functions (Plus the Code to Get Started)

In the business world, cash is king.

Without sales, the business is dead.

This applies to customers. It applies to products. And it applies to sales performance.

Fortunately, you can create awesome sales performance reports in SQL.

The key is a slightly more advanced feature of SQL called window functions.

So, if you’ve mastered the fundamentals of SQL and feel like you’re ready for something a bit more challenging, this step-by-step guide will help.

Don’t let your basic SQL skills hold you back any longer

Sure, you can create a simple sales performance report in SQL.

Select all the sales reps from the table and sum up their sales. Then sort from highest to lowest. Well, what happens if your boss wants to see the top 3 sales reps in each sales region?

Or what if you want to share the results of an ad campaign that you helped optimize with a new client by showing the customers that fall into the top 10% of purchases? Or how about calculating running totals for each month of the year to show growth for a new product line?

This is where SQL window functions come in.

And that’s what you’ll learn in this step-by-step SQL guide:

How to compare and rank employees based on sales performance with SQL

This step-by-step SQL guide has 3 parts:

  1. The Problem
  2. The Breakdown
  3. The Solution (with code)

Part 1: The Problem

You work for the regional manager of McAppleBucks, the world-renowned technology-coffee fast-food conglomerate.

It’s end-of-quarter review time, and you’re tasked with evaluating the sales performance of your employees within their respective regions.

Your task is to compare and rank your employees based on their sales performance, so you can reward your top performers and identify those who might need a bit more coaching.

Simple enough – if you know how to use SQL window functions.

Part 2: The Breakdown

Let’s break the problem down:

First, let’s figure out what data we’ll need.

We’ll need:

  • Employee data (name, region)
  • Sales data (sales amounts, dates)

And we’ll need a way to combine the data that’s in these two tables. Fortunately, we can use SQL JOINs to combine the data as long as there’s a common element between the two tables.

You’ll see that common element (or “key”) in the code in the next part of this guide.

Next, we’ll need to know the total sales for each employee in each region.

Again, SQL is to the rescue. We can use basic SQL commands like SELECT, FROM, WHERE, and GROUP BY to aggregate the sales data by employees in each region.

Finally, we need to be able to rank the employees within each region, based on their total sales amounts.

This is a bit trickier. But nothing that SQL can’t handle. And in Part 3, you’ll learn exactly how to build the entire analysis from scratch.

Part 3: The Solution (with code)

SQL window functions are called “window functions” because it’s like you’re looking at the data through a window.

You create the “window” in your SQL code. It’s called a partition and it allows you to view just part of your data when performing some calculation or analysis.

So, we’re going to:

  1. Calculate the total sales for each employee
  2. Create the region partition (this is the “window” we want to see the data in)
  3. Rank each employee to get the top employees in each region

Now, you could theoretically create any partition you wanted, as long as you have the data.

Instead of the region, you could rank by:

  • employee job title (top senior-level employees, top junior-level employees, etc.)
  • products (top employees that have sold Toyota 4Runners)
  • employee tenure (top employees with 3+, 5+, or 10+ years of experience)

You can also do more things than just a simple ranking.

But for this guide, we’re sticking with the problem: employee sales performance by region.

Step 1: Database setup

First, let’s create our database tables and insert some records.

Here’s the code. You can also try it online.​

Creating the tables:

-- Creating 'employees' table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    region VARCHAR(50)
);

-- Creating 'sales' table
CREATE TABLE sales (
    transaction_id INT PRIMARY KEY,
    employee_id INT,
    sale_amount_usd DECIMAL(10,2),
    sale_amount_eur DECIMAL(10,2),
    sale_date DATE,
    closing_date DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Inserting records:

-- Inserting records into 'employees' table
INSERT INTO employees (employee_id, employee_name, region)
VALUES
(1, 'Dwayne Therockington', 'Asia'),
(2, 'Lionel Missi', 'South America'),
(3, 'Brontonio James', 'North America'),
(4, 'Marilyn Moremo', 'Europe'),
-- shortened for readability.
-- view the full code online https://t.ly/swd-win-func
(17, 'Julius Squeezer', 'Europe'),
(18, 'Aristotle Onaspork', 'Asia'),
(19, 'Vincent Van Dough', 'South America'),
(20, 'Charles Cello', 'North America');

-- Inserting records into 'sales' table
INSERT INTO sales (transaction_id, employee_id, sale_amount_usd,
sale_amount_eur, sale_date, closing_date)
VALUES
(1, 1, 500, 420, '2023-01-02', '2023-01-30'),
(2, 2, 650, 548, '2023-02-10', '2023-02-25'),
(3, 3, 800, 674, '2023-03-15', '2023-03-30'),
(4, 4, 700, 590, '2023-01-25', '2023-02-14'),
-- shortened for readability.
-- view the full code online https://t.ly/swd-win-func
(37, 7, 850, 716, '2023-05-26', '2023-06-14'),
(38, 11, 900, 758, '2023-05-29', '2023-06-17'),
(39, 15, 750, 633, '2023-06-02', '2023-06-21'),
(40, 20, 1000, 843, '2023-06-05', '2023-06-24');

Alrighty. With our database set up, we can get to work analyzing.

Step 2: Quick analysis

Here, we’re just creating a quick analysis to get familiar with the data and answer some basic questions about the data set. For example, who are the top 5 sales reps by sales across all regions?

Here’s the code for that. View the code online.​

-- Analysis - Finding total sales for each employee in each region
SELECT 
    e.employee_name,
    e.region,
    SUM(s.sale_amount_usd) as total_sales_usd
FROM 
    employees e 
JOIN 
    sales s 
ON 
    e.employee_id = s.employee_id
GROUP BY 
    e.employee_name, e.region
ORDER BY SUM(s.sale_amount_usd) desc
LIMIT 5;

This code joins the employees table with the sales table on employee_id and groups the data by employee name and region.

It sums the sales amount in USD for each employee in each region.

Results:

employee_nameregiontotal_sales_usd
Pablo CheesecassoSouth America4300.00
Cleo PastaAfrica4150.00
Beyon SenolesAfrica3800.00
Brontonio JamesNorth America2450.00
Charles CelloNorth America2000.00

Here, you can see that Pablo Cheesecasso has the highest sales amount ($4,300) across all employees.

Finally, let’s add a RANK() window function to rank all employees by sales amount within each region.

Step 3: Regional employee sales performance analysis with SQL window functions

Okay, now we’re ready for the window functions.

Here’s the code. View it online.​

-- Analysis - Ranking employees within each region based on their sales performance
SELECT
    e.region,
    e.employee_name,
    SUM(s.sale_amount_usd) as total_sales_usd,
    RANK () OVER (
        PARTITION BY e.region
        ORDER BY SUM(s.sale_amount_usd) DESC
    ) as sales_rank
FROM 
    employees e 
JOIN 
    sales s 
ON 
    e.employee_id = s.employee_id
GROUP BY 
    e.employee_name, e.region

This query is similar to the previous one but adds a RANK() window function.

This ranks the employees within each region (PARTITION BY e.region) based on their total sales (ORDER BY SUM(s.sale_amount_usd) DESC).

And here are the results:

(Note: I removed some regions for readability purposes. View the query and analysis here.)

regionemployee_nametotal_sales_usdsales_rank
AfricaCleo Pasta4150.001
AfricaBeyon Senoles3800.002
AfricaAda Loveleaves1550.003
<…><…><…><…>
South AmericaPablo Cheesecasso4300.001
South AmericaVincent Van Dough750.002
South AmericaLionel Missi650.003

Here, you can see a new sales_rank column. This shows where each employee falls within each region by total sales amount.

These analyses would solve business problems like this:

  • Helping regional managers evaluate employee sales performance quickly across regions.
  • Helping managers identify top-performing and low-performing employees within each region.
  • Enabling managers to understand the sales distribution within their region so they can develop new sales strategies.

SQL window functions are really powerful. And this step-by-step guide is an introduction to them so that you can start solving real-world problems.

Here’s what you learned in this guide:

  • How to use SQL window functions to solve real-world business problems.
  • You know how to make tables, fill them with data, and answer complex questions.
  • You’ve used SQL to rank employees based on their sales in different regions.
  • You’ve seen the RANK() function in action.
  • Created insights into important questions like who’s the top seller and sales trends.
  • Now you have the tools to turn sales data into actions to help a business grow.

Keep learning SQL – each new question brings fresh discoveries.

So, what’s your next question going to be?

Leave a Reply

Your email address will not be published. Required fields are marked *