Understanding sales trends is vital for every business.
It’s like trying to navigate an unfamiliar city without a map, a compass, or any directional signs.
Now imagine doing it with a ticking time bomb in your backpack. That’s what it feels like when you’re trying to steer a business without grasping your sales trends.
This is what sales leaders feel when they don’t have sales analytics in place.
I should know: I spent 5+ years working in sales operations reporting at different levels for a top-tier financial services firm. My clients were sales leaders at every level. From junior sales managers all the way up to the head of sales operations.
Unfortunately, many sales leaders don’t have that kind of setup.
It looks more like this.
- Making sales decisions based on gut feeling rather than data.
- Spending hours on end wrangling data in spreadsheets.
- Stressing over incomplete, inaccurate, or late reports.
It’s not just draining. It’s excruciating.
It’s enough to make you want to throw in the towel and question the feasibility of your business journey. Even the most seasoned executives have days where they’re on the brink of saying, “I can’t do this anymore.”
Imagine you’re working as an analyst for a sales leader. Working to learn SQL to solve real-world business problems.
Bingo. A light bulb goes off. “What if I could use SQL for sales trend analysis?”
But you’re not sure where to start.
With the power of SQL you know you can run complex queries on your sales data. Extract meaningful insights. Predict future trends with more confidence than ever before.
No more guesswork. No more drowning in data. No more late-night fretting over inaccurate forecasts.
Instead, your decisions begin to follow a more structured, data-driven path:
- Analyze: Understand your sales data 100%. Critical skill.
- Forecast: Predict future trends to “see around corners”.
- Adapt: Make well-informed decisions. And make them quickly.
- Innovate: Find new opportunities for growth. Not just for the business. But also for your career!
You KNOW that SQL can help here.
Imagine how much smoother your business operations could run.
You’d have clear visibility into your sales performance, an accurate understanding of your market, and an edge over your competitors.
To help you get there, I have written a detailed guide on using SQL for sales trend analysis. By following this guide, you’ll have the expertise you need to build out a sales analysis that will get to the bottom of any potential issues for your client.
Remember: Starting with Data means you are an ACTION TAKER. Follow these steps and you’ll actually have a full-blown sales analysis that actually works.
Let’s dig in:
(by the way, this newsletter was inspired by a survey response from a subscriber like you. Can you take 5 minutes to add your voice? Thanks so much!)
Step 1: Database Setup
Before you begin querying, you need a database. Let’s say we have three tables: Sales, Customers, and Products.
(btw, you can follow along with the interactive SQL here at no cost to you)
-- Create Tables for Sales Analysis
CREATE TABLE Customers (
CustomerID SERIAL PRIMARY KEY,
CustomerName TEXT,
Region TEXT
);
CREATE TABLE Products (
ProductID SERIAL PRIMARY KEY,
ProductName TEXT,
Category TEXT
);
CREATE TABLE Sales (
SaleID SERIAL PRIMARY KEY,
CustomerID INT,
ProductID INT,
Quantity INT,
Price DECIMAL(10,2),
SaleDate DATE
);
And here’s the code to populate the tables:
-- Populate the Customers table
INSERT INTO Customers (CustomerName, Region)
VALUES
('Charlie Chaplin', 'North'),
('Beyonce Beatle', 'South'),
('Mick Mercury', 'East'),
('Diana Drake', 'West')
('Freddie Fitzgerald', 'North'),
('Amy Adams', 'South'),
('Jennifer Joplin', 'East'),
('Elvis Evans', 'West');
-- Populate the Products table
INSERT INTO Products (ProductName, Category)
VALUES
('Falcon Flux', 'Hypercar'),
('Nebula Navigator', 'Spacecraft'),
('Quantum Quattro', 'Electric Car'),
('Galaxy Glider', 'Airbike'),
('Orion Overdrive', 'Hypercar'),
('Zephyr Zest', 'Spacecraft'),
('Comet Cruiser', 'Electric Car'),
('Astro Antelope', 'Airbike');
-- Populate the Sales table
INSERT INTO Sales (CustomerID, ProductID, Quantity, Price, SaleDate)
VALUES
(1, 1, 5, 150000.00, '2023-01-15'),
(1, 2, 10, 2000000.00, '2023-01-20'),
(2, 1, 3, 150000.00, '2023-02-05'),
(3, 3, 8, 60000.00, '2023-02-10'),
(4, 4, 2, 50000.00, '2023-02-15'),
(2, 2, 5, 2000000.00, '2023-02-20'),
(3, 1, 7, 150000.00, '2023-03-05'),
(4, 3, 4, 60000.00, '2023-03-10'),
(1, 4, 6, 50000.00, '2023-03-15'),
(3, 2, 9, 2000000.00, '2023-03-20'),
(5, 5, 7, 180000.00, '2023-04-05'),
(6, 6, 4, 2200000.00, '2023-04-10'),
(7, 7, 5, 65000.00, '2023-04-15'),
(8, 8, 6, 52000.00, '2023-04-20');
Step 2: Monthly Sales Trend Analysis
The first sales trend analysis you should create is a monthly trend.
This gives a business leader a good starting point to understand how sales are working. Then you can expand on that idea. You can either get more or less “granular” with the dates. For example, you can get more granular by showing weekly sales or daily sales. Or you can get less granular by showing quarterly or annual sales.
But monthly trends are perfect. So we start there.
Follow along with the sample code (free)
SELECT DATE_TRUNC('month', SaleDate) as Month, SUM(Quantity * Price) as TotalSales
FROM Sales
GROUP BY Month
ORDER BY Month;
Here’s a breakdown of the code:
- DATE_TRUNC(‘month’, SaleDate) as Month:
DATE_TRUNC is a PostgreSQL function that truncates a timestamp or interval data type to a specified precision. In this case, it truncates the SaleDate to the month. This means regardless of the day of the sale, all sales made in the same month and year will have the same value. The AS keyword renames the truncated SaleDate to Month for ease of understanding in the results. - SUM(Quantity * Price) as TotalSales:
This calculates the total sales for each month by multiplying the Quantity of each sale by the Price of the product sold, then summing these values for each month. The AS keyword renames this calculated field to TotalSales in the results. - FROM Sales:
This specifies the table from which the data is being pulled, in this case, the Sales table. - GROUP BY Month:
This groups the results by month. This means for each unique month in the Sales table, it will sum the total sales and display a single row. - ORDER BY Month:
This sorts the results by the Month field in ascending order. This means the results will be displayed starting from the earliest month to the latest.
The end result of this query is a table displaying the total sales for each month, ordered from the earliest to the latest month, providing an overview of the monthly sales trend.
Results
| month | totalsales |
|---|---|
| 2023-01-01 | 2075000.00 |
| 2023-02-01 | 1103000.00 |
| 2023-03-01 | 1959000.00 |
| 2023-04-01 | 1069700.00 |
Nice work!! Let’s keep going.
Step 3: Sales Representative Analysis
Now let’s use SQL to figure out how sales representatives are performing.
Here we’re looking at the sales rep’s region to get a high-level sense at the regional level. You could then create a deeper dive analysis by looking at each sales rep individually (or by team or sales manager if that data is available). It’s good to start at the high level and then dig deeper based on what you find.
Follow along with the sample code (free)
-- Sales Rep Analysis SELECT c.Region, SUM(s.Quantity * s.Price) as Sales FROM Sales s JOIN Customers c ON s.CustomerID = c.CustomerID GROUP BY c.Region ORDER BY Sales DESC;
Here’s a breakdown of the sales rep analysis code:
- SELECT c.Region, SUM(s.Quantity * s.Price) as Sales:
This line is specifying what information you want to select from the tables. c.Region is selecting the Region field from the Customers table, and SUM(s.Quantity * s.Price) as Sales is calculating the total revenue from sales in each region by multiplying the Quantity of each product sold by its Price and summing these values. AS renames this calculated field to Sales in the results. - FROM Sales s:
This line is telling the query from which table to start pulling data. In this case, it’s starting with the Sales table, and s is used as an alias for Sales to make the query easier to read and write. - JOIN Customers c ON s.CustomerID = c.CustomerID:
This line is using the JOIN clause to combine rows from the Sales and Customers tables based on a related column between them, which in this case is the CustomerID. c is used as an alias for Customers. - GROUP BY c.Region:
This line groups the results by the Region field. This means for each unique region in the Customers table, it will sum the sales and display a single row. - ORDER BY Sales DESC:
This line sorts the results by the Sales field in descending order. This means the results will be displayed starting from the region with the highest sales.
This will give you the total sales for each region, sorted in descending order, which can help a business understand its regional performance.
Results
| region | sales |
|---|---|
| North | 2231000.00 |
| East | 1985500.00 |
| South | 1925000.00 |
| West | 65200.00 |
Now you can quickly see that the North region is crushing it in total sales!
But West needs some help.
Step 4: Customer Analysis
This query can be updated to show which customers bring the most revenue.
This is a more detailed analysis that is useful to find any outliers. For example, you may find customers with a TON of sales and some customers with very little in sales. That would be good for a sales manager to know.
Follow along with the sample code (free)
-- Customer Sales Analysis SELECT c.CustomerName, SUM(s.Quantity * s.Price) as TotalRevenue FROM Sales s JOIN Customers c ON s.CustomerID = c.CustomerID GROUP BY c.CustomerName ORDER BY TotalRevenue DESC LIMIT 3;
Let’s go through the code line by line:
- SELECT c.CustomerName, SUM(s.Quantity * s.Price) as TotalRevenue:
This line is selecting the information you want from the tables. c.CustomerName is choosing the CustomerName field from the Customers table. SUM(s.Quantity * s.Price) as TotalRevenue is calculating the total revenue from each customer by multiplying the Quantity of each sale by its Price and summing these values. The AS keyword renames this calculated field to TotalRevenue in the results. - FROM Sales s:
This line is indicating the table from which to start pulling data. In this case, it’s starting with the Sales table, and s is used as an alias for Sales to make the query more concise. - JOIN Customers c ON s.CustomerID = c.CustomerID:
This line is using the JOIN clause to combine rows from the Sales and Customers tables based on a related column between them, which is the CustomerID. c is used as an alias for Customers. - GROUP BY c.CustomerName:
This line groups the results by the CustomerName field. This means for each unique customer in the Customers table, it will sum their total revenue and display a single row. - ORDER BY TotalRevenue DESC:
This line sorts the results by the TotalRevenue field in descending order. This means the results will be displayed starting from the customer who generated the most revenue. - LIMIT 3:
I only want the top 3 customers on the list.
And here are the results. Total revenue for each customer, ordered from highest to lowest. Now you can see which customers are generating the most revenue.
Results
| customername | totalrevenue |
|---|---|
| Charlie Chaplin | 2105000.00 |
| Mick Mercury | 1953000.00 |
| Beyonce Beatle | 1045000.00 |
Step 5: Product Analysis
Finally, let’s see which products generate the most revenue:
Follow along with the sample code (free)
-- Product Analysis SELECT p.ProductName, SUM(s.Quantity * s.Price) as TotalRevenue FROM Sales s JOIN Products p ON s.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY TotalRevenue DESC LIMIT 3;
And here’s how this code works:
- SELECT p.ProductName, SUM(s.Quantity * s.Price) as TotalRevenue:
The SELECT statement is used to pick out the data you’re interested in. In this case, you want to display the ProductName from the Products table (p.ProductName), and the total revenue each product has generated. The total revenue is calculated by multiplying the Quantity sold by the Price for each product, then adding up these amounts (SUM(s.Quantity * s.Price)). The AS keyword is used to rename this calculated value as TotalRevenue in the output. - FROM Sales s:
This part of the query is specifying the Sales table as the initial source of data. The s is an alias for the Sales table, a shorthand that makes the rest of the query easier to read and write. - JOIN Products p ON s.ProductID = p.ProductID:
Here, the JOIN clause is used to combine data from the Sales and Products tables based on a common field between them, which is ProductID. The p is an alias for the Products table. - GROUP BY p.ProductName:
This line is grouping the output by product name. This means for each unique product in the Products table, it will sum the total revenue and display a single row. - ORDER BY TotalRevenue DESC:
This line is sorting the output by TotalRevenue in descending order. This means the products generating the highest revenue will appear first in the results.
So the output of this query will be a table listing each unique product, alongside the total revenue that the product has generated, sorted from highest to lowest revenue. This can be helpful for a business in understanding which products are generating the most income.
Results
| productname | totalrevenue |
|---|---|
| Nebula Navigator | 4800000.00 |
| Zephyr Zest | 880000.00 |
| Falcon Flux | 225000.00 |
Five Simple Steps to Solving Real-World Sales Problems with SQL
- Set up your database
- Create a monthly trend analysis
- Understand rep sales performance
- Dig deeper into customer sales trends
- Analyze product sales trends
That’s it!
Each step in this process reveals a new layer of understanding, adding depth to your sales strategy. Remember, the key to mastering SQL is practice, so don’t be afraid to dive in and experiment with different queries.
Got an interesting use case that I haven’t mentioned? Let me know!