Pareto principle in sales analysis
The Pareto Principle, commonly known as the 80/20 rule, argues that 80% of the results are achieved with 20% of the effort. Joseph M. Juran introduced this idea, which was influenced by Vilfredo Pareto, an Italian economist who discovered that 20% of the people controlled 80% of the land in Italy. Over time, this approach has been widely implemented in a variety of fields, including business, engineering, and analytics. In interviews and real-world circumstances, recognizing the top 20% of contributors who drive the majority of results (80%) is a valuable talent.
Let’s start by looking at how to use this approach using SQL to determine the top 20% of products that account for 80% of overall sales.
We have a dataset that includes sales data for several products. The goal is to find the products that account for 80% of overall sales. This will help us focus on the core goods that provide the majority of the money.
We start by creating a table for order and populating it with sample data:
CREATE TABLE orders (
product_id INT,
sales NUMERIC
);
INSERT INTO orders (product_id, sales)
VALUES
(1, 100),
(2, 200),
(3, 150),
(4, 50),
(5, 300),
(6, 250);
This dataset represents six products, each with its corresponding sales figure.
To achieve our goal, we break the query into logical steps using Common Table Expressions (CTEs):
WITH product_by_sales AS (
SELECT
product_id,
SUM(sales) AS product_sales
FROM orders
GROUP BY product_id
),
ts AS (
SELECT
product_id,
product_sales,
SUM(product_sales) OVER (ORDER BY product_sales DESC) AS running_sales,
SUM(product_sales) OVER () AS total_sales,
(SUM(product_sales) OVER ()) * 0.8 AS eighty_percent
FROM product_by_sales
)
SELECT *
FROM ts
WHERE running_sales <= eighty_percent;
product_by_sales
CTE: product_id
and calculates the total sales for each product.ts
CTE: running_sales
: Cumulative sum of sales using a window function ordered by product_sales
in descending order.total_sales
: Total sales for all products.eighty_percent
: 80% of the total sales, calculated as (SUM(product_sales) OVER ()) * 0.8
.For the specified dataset, the query will return the products that account for 80% of total sales. This allows us to focus on the important goods that provide the majority of our revenue.
This method is a practical use of the Pareto Principle in sales analysis. Identifying the major items contributing to 80% of the income allows firms to:
I hope you would have found this useful, if so, consider sharing it with your friends, thank you.
Hi, I am Vishal Jaiswal, I have about a decade of experience of working in MNCs like Genpact, Savista, Ingenious. Currently i am working in EXL as a senior quality analyst. Using my writing skills i want to share the experience i have gained and help as many as i can.
Welcome to Day 15 of our Python for Data Science journey!On Day 15, we dived…
Welcome to Day 14 of our Python for Data Science journey! Today, we explored Seaborn,…
Welcome to Day 13 of Learning Python for Data Science! Today, we’re focusing on three…
Test your understanding of Python Data Structure, which we learned in our previous lesson of…
Welcome to Day 12 of Learning Python for Data Science. Today, we’ll dive into Pandas,…
NumPy Array in Python is a powerful library for numerical computing in Python. It provides…