What is Pareto Principle

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.

Pareto Principle

Problem Statement

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.

Database Setup

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.

Query to Identify Top Contributors

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;

Step-by-Step Explanation

1. product_by_sales CTE:
   • The first CTE groups the sales by product_id and calculates the total sales for each product.
2. ts CTE:
   • This adds the following columns to refine the data:
     • 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.
3. Final Query:
   • Filters the results to only show products with running_sales less than or equal to 80% of total sales.
   • This helps identify the products contributing to the top 80% of sales.

Query Results

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:

• Optimize inventory management.
• Focus marketing efforts on top-performing products.
• Improve decision-making for resource allocation.

I hope you would have found this useful, if so, consider sharing it with your friends, thank you.

• SQL Interview Question at Zomato for a Data analyst Position (0-3 Years) – | Shared By An Experienced Data Analyst
• The Ultimate Guide to SQL Indexing and Query Optimization
• SQL Interview Questions Asked In Walmart For Data Analyst Post | CTC – 18 LPA | Learn With Curious Club!!
• SQL Interview Questions for Deloitte Data Engineer Roles: Your Ultimate Prep Guide
• Data Analyst SQL Interview Questions | EY (Ernst & Young) | Shared By An Experienced Data Analyst