ABC Classification With SQL Server Window Function

by Julie (@MsSQLGirl)
on April 14, 2014

There are many benefits from using Window Functions with OVER clause which are available since SQL Server 2012 2005. In this blog post, we will go through a use case example of ABC Analysis / Classification using a Window Function.

Let us examine the definition of ABC Analysis.

Extracting the definition from Wikipedia, the ABC analysis provides a mechanism for identifying items that will have a significant impact on overall inventory cost.

ABC Analysis Scenario

Marco Russo has posted a very useful ABC classification using Power Pivot here. Using a similar rule, we will classify

  • Products that make 70% of the sales are in Class A
  • Products that make 20% of the sales are in Class B
  • Products that make 10% of the sales are in Class C

So, let’s take a look at how we can apply the new Window Function to do this. In this scenario, we will use AdventureWorksDW2012 to classify all product sales for each month. The breakdown of the logic to perform the ABC classification in this scenario is

  1. Calculate the total sales of each product model.
  2. Calculate the cumulative sales of each product model in the order of the Sales amount (higher to lower values).
  3. Calculate the percentage of the cumulative sales of each product model against the total sales.
  4. Determine the classification based on the percentage of cumulative sales.

In this example below, I use CTE and to calculate the sales of each product model. Step 2 to 4 can be done in one go. Please note that there are many ways of calculating ABC Classification, below is just one of the ways.

USE AdventureWorksDW2012
GO

WITH ProductSales AS
(
	-- Get the total for each Product Model
	SELECT 
		dp.[ModelName], 
		SUM(ssd.[SalesAmount]) Sales
	FROM [dbo].[FactInternetSales] ssd
		INNER JOIN [dbo].[dimProduct] dp
		ON ssd.[ProductKey] = dp.[ProductKey]
	GROUP BY 
	dp.[ModelName]
)
-- Calculate culmulative total and categorise it based on the percentage.
-- Product Model that have high sales amount and make up the first 70% 
-- will be classified as A, the next 20% is B and the rest is C.
-- Product Models in A generate more revenue.
SELECT
	ps.[ModelName], 
	ps.[Sales],
	SUM(ps.[Sales]) OVER (ORDER BY ps.[Sales] DESC) AS CumulativeSales,
	SUM(ps.[Sales]) OVER () AS TotalSales,
	SUM(ps.[Sales]) OVER (ORDER BY ps.[Sales] DESC) / SUM(ps.[Sales]) OVER () AS CumulativePercentage,
	CASE 
		WHEN SUM(ps.[Sales]) OVER (ORDER BY ps.[Sales] DESC) / SUM(ps.[Sales]) OVER () <= 0.7 
			THEN 'A'
		WHEN SUM(ps.[Sales]) OVER (ORDER BY ps.[Sales] DESC) / SUM(ps.[Sales]) OVER () <= 0.9 
			THEN 'B'
		ELSE 'C'
	END AS Class
FROM    ProductSales ps
GROUP BY 
	ps.[ModelName],
	ps.[Sales];

The result looks like this (exported to Excel)

ABC Analysis on AdventureWorks Product Model

Have I Done This Correctly?

This example is very similar to Marco Russo’s post on ABC Analysis Using Power Pivot. To check that I’ve done this correctly, I create a Pivot Table based on the table above (no Power Pivot used). The resulting Pivot Table is very simlar to Marco’s one.

Pivot Table of ABC Classification

Using PARTITION BY For Additional Context

What if I’m interested in the ABC Analysis of product models for each year? This is simply addressed by using PARTITION BY on Year to get the Year context. In the CTE part, I add YEAR(OrderDate) to obtain the Year that I will be partitioning by.

ABC Analysis per Order Year

The code will look similar to this:

USE AdventureWorksDW2012
GO

WITH ProductSales AS
(
	-- Get the total for each Product Model and Year
	SELECT 
		YEAR(ssd.[OrderDate]) AS OrderYear,
		dp.[ModelName], 
		SUM(ssd.[SalesAmount]) Sales
	FROM [dbo].[FactInternetSales] ssd
		INNER JOIN [dbo].[dimProduct] dp
		ON ssd.[ProductKey] = dp.[ProductKey]
	GROUP BY 
		dp.[ModelName],
		YEAR(ssd.[OrderDate])
)
-- Calculate culmulative total and categorise it based on the percentage.
-- Product Model each year that have high sales amount and make up 
-- the first 70% will be classified as A, the next 20% is B and the rest is C.
-- Product Models in A generate more revenue.
SELECT
	ps.[OrderYear],
	ps.[ModelName], 
	ps.[Sales],
	SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) AS CumulativeSales,
	SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) AS TotalSales,
	SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
		/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) AS CumulativePercentage,
	CASE 
		WHEN SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
				/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) <= 0.7 
			THEN 'A'
		WHEN SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
				/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) <= 0.9 
			THEN 'B'
		ELSE 'C'
	END AS Class
FROM    ProductSales ps
GROUP BY 
	ps.[OrderYear],
	ps.[ModelName],	
	ps.[Sales]
ORDER BY 
	ps.[OrderYear],
	CASE 
		WHEN SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
				/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) <= 0.7 
			THEN 'A'
		WHEN SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
				/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) <= 0.9 
			THEN 'B'
		ELSE 'C'
	END ;

The result will look similar to this (I have updated my AdventureWorksDW to start in Year 2001 instead of the default one).

ABC Analysis on Product Model Per Year

Wrap Up

A number of Window Functions are very useful for analytics. We use SUM with OVER and PARTITION BY to generate Cumulative Total and to calculate percentages,which then can be used derive the ABC classification.

This post is part of #SQLCoop series of Window Function.

#SQLCoop

I would highly recommend other Window Function articles by #SQLCoop bloggers –

A Date At The End of The Month by Mickey Stuewe

Windows Functions: Who Knew by Chris Yates

Write readable and high-performance queries with Window Functions by Jeffrey Verheul

Further Reading

ABC Analysis in PowerPivot by Marco Russo

ABC Analysis on Wikipedia

SUM (Transact-SQL) on Technet

Revision 30 Apr 2014 – Henning Frettem kindly corrected me about the version of SQL Server that Window Function is available. Thanks Henning!

Categories:

DBA / DB Dev

Tags:

#SQLCoopABC AnalysisT-SQLWindow Function

9 Responses

    • Hi Henning!

      You are correct. Thank you so much for taking the time and correcting me. I really appreciate it 🙂

      It must have slipped my mind when I wrote that blog post.

      Hope you have a good day.
      Julie

      Reply

  4. Hi,

    I am having this error
    Msg 102, Level 15, State 1, Line 20
    Incorrect syntax near ‘order’.

    This is my script

    WITH ProductSales AS
    (
    — Get the total for each Product Model
    SELECT
    dp.ItemCode,
    SUM(ssd.U_Sales) Sales
    FROM [@TLCC1] ssd
    INNER JOIN OITM dp
    ON ssd.U_ItemCode = dp.ItemCode
    GROUP BY
    dp.ItemCode
    )
    — Calculate culmulative total and categorise it based on the percentage.
    — Product Model that have high sales amount and make up the first 70%
    — will be classified as A, the next 20% is B and the rest is C.
    — Product Models in A generate more revenue.
    SELECT
    ps.U_ItemCode,
    ps.U_Sales,
    SUM(ps.U_Sales) OVER (ORDER BY ps.U_Sales DESC) AS CumulativeSales,
    SUM(ps.U_Sales) OVER () AS TotalSales,
    SUM(ps.U_Sales) OVER (ORDER BY ps.U_Sales DESC) / SUM(ps.U_Sales) OVER () AS CumulativePercentage,
    CASE
    WHEN SUM(ps.U_Sales) OVER (ORDER BY ps.U_Sales DESC) / SUM(ps.U_Sales) OVER () <= 0.7
    THEN 'A'
    WHEN SUM(ps.U_Sales) OVER (ORDER BY ps.U_Sales DESC) / SUM(ps.U_Sales) OVER () <= 0.9
    THEN 'B'
    ELSE 'C'
    END AS Class
    FROM [@TLCC1] ps
    GROUP BY
    ps.U_ItemCode,
    ps.U_Sales;

    Reply

    • Hi Raphael,

      I’m not sure why you are getting a syntax error – looking over it, I don’t see anything wrong except that:
      with your 4th last line on the statement “FROM [@TLCC1] ps”, i.e. the main select statement should be referring to the CTE “ProductSales”, and all the columns should be referring to the ProductSales columns rather than @TLCC1.

      Hope this helps.
      Julie

      Reply

  6. Hi Julie,

    Thanks fo a really neat and useful application of analytical functions, and for enlightening me that running sums are possible to calculate. I used your code, virtually unchanged, to implement an ABC analys in PL/SQL on Oracle 11g.

    Reply

    • Hi Daniel,

      Fantastic! I’m happy to hear that it is useful for PL/SQL on Oracle 11g. It’s been over a decade since I used Oracle – but I’m glad that it works there too 🙂

      Thanks for visiting and leaving the feedback!

      Julie

      Reply

Leave a Reply

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

Some simple Math is good for your brain! Thanks, Ms SQL Girl. * Time limit is exhausted. Please reload the CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 MsSQLGirl. Created for free using WordPress and Colibri