There are many benefits from using Window Functions with OVER clause which are available since SQL Server 2012. 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

- Calculate the total sales of each product model.
- Calculate the cumulative sales of each product model in the order of the Sales amount (higher to lower values).
- Calculate the percentage of the cumulative sales of each product model against the total sales.
- 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)

## 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.

## 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.

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).

## 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.

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