SQL CTEs Demystified: Step-by-Step Guide to Employee Sales Reporting and Trend Analysis (Code Samples Inside)

When I was first assigned to create an Employee Sales Reporting analysis for my employer at the time, I was honestly terrified. I had to navigate through a complex jungle of tangled data, and SQL was my only tool.

And trust me, that’s a tough spot to be in.

Because I felt like I was letting my team down:

• Drowning in raw, unstructured data.
• Struggling with time-consuming debugging sessions.
• Facing performance issues with traditional SQL queries.
• Having a hard time understanding complex data relationships.

It was nothing short of a nightmare.

The confusion and complexity were so intense that I almost decided to stick with Excel sheets and pivot tables. But then, I discovered the magic of SQL Common Table Expressions (CTEs).

And let me tell you, it was a game-changer.

CTEs allowed me to structure my queries in a more readable and manageable manner. I could create temporary result sets and call upon them multiple times within the same query. The process of debugging became less daunting, and the overall performance of my queries improved drastically.

No more fighting with piles of raw data. No more deciphering unoptimized SQL queries. No more debugging until the break of dawn.

Instead, I transformed my process into a super-efficient pipeline:

• Identify the data requirements.
• Structure the CTEs.
• Test each CTE for accurate results.
• Combine all CTEs to generate the final output.

It was like finding a flashlight in the dark, confusing world of SQL querying.

And now, I want to share this approach with you.

I’ve written a comprehensive guide on how to use SQL Common Table Expressions (CTEs) for creating Employee Sales Reporting analysis. It’s written in simple, easy-to-understand language, and it’s designed to help you navigate the data jungle confidently.

Just remember, complex data analysis doesn’t have to be a daunting task. With the right tools and approach, you can transform it into an engaging, rewarding process.

And SQL CTEs are definitely a part of that solution.

So, dive in, embrace the learning, and experience the transformative power of SQL CTEs.

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

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

Part 1: The Problem

Imagine you are the Sales Director at Star-Apple, a globally recognized tech retail giant.

Despite having a strong sales team, you’ve noticed an unsteady performance pattern in the last few quarters.

Your task: find a way to track your sales team’s performance over time to identify the consistent high performers and those who may be facing challenges.

Part 2: The Breakdown

Now that we understand the problem, let’s break this down step-by-step:

First, let’s create a list of each employee’s total sales per quarter for the past year.

After that, we’ll find a way to identify the top performers for each quarter.

Finally, we’ll create the employee sales trend analysis and save the day 🙂

And along the way, you’ll learn how to use CTEs to make this easy.

Part 3: The Solution (with Code)

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:

CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
role VARCHAR(50),
hire_date DATE,
department VARCHAR(50)
);

CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(50),
launch_date DATE,
price DECIMAL(10, 2)
);

CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
employee_id INT REFERENCES employees(employee_id),
product_id INT REFERENCES products(product_id),
sale_date DATE,
quantity INT
);

CREATE TABLE sales_targets (
target_id SERIAL PRIMARY KEY,
employee_id INT REFERENCES employees(employee_id),
quarter INT,
year INT,
target DECIMAL(10, 2)
);

Inserting records:

INSERT INTO employees (first_name, last_name, role, department, hire_date)
VALUES
('Lionel', 'Messie', 'Sales Associate', 'Electronics', '2020-02-01'),
('Cristiano', 'Ranoldo', 'Sales Associate', 'Fashion', '2018-07-15'),
('Beyonc', 'Knolls', 'Sales Associate', 'Cosmetics', '2019-11-10'),
-- shortened for readability.
-- view the full code online https://t.ly/swd-sql-cte
('Nelson', 'Mandel', 'Sales Associate', 'Office Supplies', '2018-10-01'),
('Mahatma', 'Ghandy', 'Sales Associate', 'Office Supplies', '2020-04-01'),
('Martin', 'Luther King', 'Sales Manager', 'Office Supplies', '2016-01-01');

INSERT INTO products (product_name, price, launch_date)
VALUES
('Galaxy Sky', 1200.00, '2020-09-01'),
('iDrone', 1500.00, '2018-11-10'),
('Zenvo Shirt', 60.00, '2017-02-20'),
('Armane Jacket', 250.00, '2018-09-15'),
('Sparkling Star Eyeshadow', 35.00, '2019-06-01'),
('Glowing Moon Lipstick', 25.00, '2020-01-15'),
-- shortened for readability.
-- view the full code online https://t.ly/swd-sql-cte
('Fantastic Toaster', 30.00, '2018-06-01'),
('Smooth Ink Pens', 5.00, '2020-03-01'),
('Clear Vision Paper Clips', 2.00, '2019-01-01');

INSERT INTO sales (employee_id, product_id, quantity, sale_date)
VALUES
(1, 1, 10, '2021-01-10'),
(2, 2, 5, '2021-01-15'),
(3, 3, 15, '2021-02-20'),
-- shortened for readability.
-- view the full code online https://t.ly/swd-sql-cte
(13, 2, 8, '2021-12-28'),
(15, 3, 19, '2021-08-09'),
(16, 4, 10, '2021-07-23');


INSERT INTO sales_targets (employee_id, quarter, year, target)
VALUES
(1, 1, 2021, 500),
(1, 2, 2021, 700),
(1, 3, 2021, 800),
-- shortened for readability.
-- view the full code online https://t.ly/swd-sql-cte
(8, 2, 2021, 750),
(8, 3, 2021, 850),
(8, 4, 2021, 750);

Step 2: Quick Analysis

First, calculate the sales for each employee for the last quarter of 2021.

SELECT employee_id, SUM(quantity * price) AS total_sales
FROM sales
JOIN products ON sales.product_id = products.product_id
WHERE sale_date BETWEEN '2021-10-01' AND '2021-12-31'
GROUP BY employee_id

Results

employee_id	total_sales
1	18000.00
8	1200.00
13	12000.00
19	120.00
20	3750.00

Next, let’s turn that into a CTE so that we can perform additional analysis on it.

For example, finding the top 5 employees in terms of sales for the last quarter of 2021.

WITH sales_last_quarter AS (
SELECT employee_id, SUM(quantity * price) AS total_sales
FROM sales
JOIN products ON sales.product_id = products.product_id
WHERE sale_date BETWEEN '2021-10-01' AND '2021-12-31'
GROUP BY employee_id
)
SELECT first_name, last_name, total_sales
FROM sales_last_quarter
JOIN employees ON sales_last_quarter.employee_id = employees.employee_id
ORDER BY total_sales DESC
LIMIT 5;

Results

first_name	last_name	total_sales
Lionel	Messie	18000.00
Rihana	Finty	12000.00
Martin	Luther King	3750.00
Marie	Curious	1200.00
Mahatma	Ghandy	120.00

This becomes the “base CTE” that we can use for more complex analysis.

Step 3: Quarterly Employee Sales Target Performance Analysis

With our “base CTE” created, we can perform some more complex analysis, like understanding employee sales performance vs. target on a quarterly basis.

This type of analysis solves many problems:

• It allows managers to see how many employees were able to meet their sales targets.
• This information can be used to assess the effectiveness of the sales strategies used in the last quarter.
• If the number of employees reaching their target is low, the company may need to provide additional training or resources or adjust the targets.

All are extremely useful for business leadership.

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

WITH sales_last_quarter AS (
SELECT employee_id, SUM(quantity * price) AS total_sales
FROM sales
JOIN products ON sales.product_id = products.product_id
WHERE sale_date BETWEEN '2021-10-01' AND '2021-12-31'
GROUP BY employee_id
),
targets_last_quarter AS (
SELECT employee_id, target
FROM sales_targets
WHERE quarter = 4 AND year = 2021
)
SELECT COUNT(*) as employees_reached_target
FROM sales_last_quarter
JOIN targets_last_quarter ON sales_last_quarter.employee_id = targets_last_quarter.employee_id
WHERE total_sales >= target;

Results

employees_reached_target
2

Step 4: Employee Sales Trend Analysis – Compare Quarterly Performance vs. Annual Performance

Finally, we can create a comparison of the quarterly sales performance against the entire year’s sales performance to understand trends.

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

Notice we have a new CTE that calculates the Q4 2021 targets.

We use both CTEs to calculate the difference and then sort the results by that difference.

WITH sales_last_quarter AS (
    SELECT employee_id, SUM(quantity * price) AS total_sales
    FROM sales
    JOIN products ON sales.product_id = products.product_id
    WHERE sale_date BETWEEN '2021-10-01' AND '2021-12-31'
    GROUP BY employee_id
),
yearly_avg_sales AS (
    SELECT employee_id, AVG(quantity * price) AS avg_sales
    FROM sales
    JOIN products ON sales.product_id = products.product_id
    WHERE EXTRACT(YEAR FROM sale_date) = 2021
    GROUP BY employee_id
)
SELECT E.first_name, E.last_name, LQ.total_sales AS q4_sales, YAS.avg_sales AS yearly_avg_sales, 
       (LQ.total_sales - YAS.avg_sales) / YAS.avg_sales * 100 AS percent_difference
FROM sales_last_quarter LQ
JOIN yearly_avg_sales YAS ON LQ.employee_id = YAS.employee_id
JOIN employees E ON LQ.employee_id = E.employee_id
ORDER BY percent_difference DESC;

Results

first_name	last_name	q4_sales	yearly_avg_sales	percent_difference
Marie	Curious	1200.00	720.00	66.66
Rihana	Finty	12000.00	7200.00	66.66
Lionel	Messie	18000.00	15000.00	20.00
Mahatma	Ghandy	120.00	120.00	0.00
Martin	Luther King	3750.00	3750.00	0.00

SQL Common Table Expressions (CTEs) are incredibly versatile and powerful tools.

In this step-by-step guide, we’ve used them to solve real-world business problems specific to sales data analysis.

Here’s a quick recap of what we’ve covered:

1. You’ve learned how to utilize SQL CTEs to structure and simplify complex queries.
2. You’ve created tables, inserted data, and leveraged this data to derive meaningful insights.
3. We’ve walked through how to use CTEs to calculate total sales, analyze quarterly performance, and compare it with the yearly average for each sales representative.
4. You’ve gained a practical understanding of how to use CTEs to identify top-performing employees and track sales trends.
5. You’ve discovered how SQL can help turn raw sales data into actionable insights that can drive business growth and inform strategic decisions.

Remember, SQL is a language of discovery, and each new question brings fresh insights.

CTEs are just one of the many tools in your SQL toolkit, and there’s so much more to learn and explore.

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