SQL - Ricardo Lizano

This SQL Project is an optimized ABC classification solution is a powerful tool for retail businesses aiming to maximize efficiency in product management. By utilizing SQL and a combination of key criteria such as Sales, Profit, and Units Sold, our classification algorithm provides valuable insights into the relative importance of your products, empowering you to make informed strategic decisions.

Key Benefits:

Precise identification of critical products: Our ABC classification enables accurate and automated identification of the most important products for your business. By considering criteria like Sales, Profit, and Units Sold, you can determine which products contribute the most to your revenue and focus your efforts on managing and promoting them effectively.

Inventory optimization: With our ABC classification solution, you can efficiently optimize your inventory. By understanding the importance of each product, you can allocate appropriate resources, manage stock levels, and forecast demand more accurately. This helps you avoid costly inventory excesses and ensures sufficient stock of critical products at all times.

Data-driven strategic decision-making: Our ABC classification algorithm provides a clear and comprehensive overview of your products. By comprehending the importance of each product in terms of Sales, Profit, and Units Sold, you can make informed strategic decisions regarding pricing, promotions, new product launches, and supplier management. This alignment with the most valuable and profitable products gives you a competitive edge.

Improved profitability and performance: By focusing on the most important products, you can enhance overall profitability and performance of your business. Increasing sales of your high-value products and effectively managing their inventory enables you to boost profit margins and achieve sustainable long-term growth.

In summary, this optimized ABC classification solution with SQL empowers you to efficiently understand and manage the significance of your products based on Sales, Profit, and Units Sold. Gain a competitive advantage, optimize your inventory, and make data-driven strategic decisions to maximize profitability and performance in your retail business.

My Github Repo: https://github.com/ricardo-lizano/sqlportfolio

SQL Server Code:


--Optimized ABC Classification: Enhance Product Management with SQL
--Author: Ricardo Lizano Monge https://lizano.live/
--Tables and data structures from ContosoRetailDW dabatese from Microsoft Corporatiion: https://www.microsoft.com/en-us/download/details.aspx?id=18279

WITH ProductSales AS
(
	SELECT
		YEAR(DateKey) AS YEAR,
		MONTH(DateKey) AS MONTH,
		1 AS CompanyKey,
		StoreKey,
		ProductKey,
		SUM(SalesAmount) AS SalesAmount,
		SUM(SalesAmount) - SUM(TotalCost) AS ProfitAmount,
		SUM(SalesQuantity) AS UnitsSold
	FROM FactSales
	GROUP BY
		YEAR(DateKey),
		MONTH(DateKey),
		StoreKey,
		ProductKey
)

SELECT
	'Sales' AS Criteria,
	StoreKey,
	ProductKey,
	SUM(ps.SalesAmount) AS SalesAmount,
	SUM(ps.ProfitAmount) AS ProfitAmount, 
	SUM(ps.UnitsSold) AS UnitsSold, 
	SUM(ps.SalesAmount) OVER (partition by ps.StoreKey ORDER BY ps.SalesAmount DESC) / SUM(NULLIF(ps.SalesAmount,0)) OVER (partition by ps.StoreKey) AS PorcetajeAcumulado,
	CASE
		WHEN SUM(ps.SalesAmount) OVER (partition by ps.StoreKey ORDER BY ps.SalesAmount DESC) / SUM(NULLIF(ps.SalesAmount,0)) OVER (partition by ps.StoreKey) <= 0.8 THEN 'A'
		WHEN SUM(ps.SalesAmount) OVER (partition by ps.StoreKey ORDER BY ps.SalesAmount DESC) / SUM(NULLIF(ps.SalesAmount,0)) OVER (partition by ps.StoreKey) <= 0.95 THEN 'B'
		ELSE 'C'
	END AS ABC
FROM ProductSales ps
GROUP BY
	StoreKey,
	ProductKey,
	SalesAmount

UNION ALL

SELECT
	'Porfit' AS Criteria,
	StoreKey,
	ProductKey,
	SUM(ps.SalesAmount) AS SalesAmount,
	SUM(ps.ProfitAmount) AS ProfitAmount,
	SUM(ps.UnitsSold) AS UnitsSold,
	SUM(ps.ProfitAmount) OVER (partition by ps.StoreKey ORDER BY ps.ProfitAmount DESC) / SUM(NULLIF(ps.ProfitAmount,0)) OVER (partition by ps.StoreKey) AS PorcetajeAcumulado,
	CASE
		WHEN SUM(ps.ProfitAmount) OVER (partition by ps.StoreKey ORDER BY ps.ProfitAmount DESC) / SUM(NULLIF(ps.ProfitAmount,0)) OVER (partition by ps.StoreKey) <= 0.8 THEN 'A'
		WHEN SUM(ps.ProfitAmount) OVER (partition by ps.StoreKey ORDER BY ps.ProfitAmount DESC) / SUM(NULLIF(ps.ProfitAmount,0)) OVER (partition by ps.StoreKey) <= 0.95 THEN 'B'
		ELSE 'C'
	END AS ABC
FROM ProductSales ps
GROUP BY
	StoreKey,
	ProductKey,
	ProfitAmount

UNION ALL

SELECT
	'Sold Units' AS Criteria,
	StoreKey,
	ProductKey,
	SUM(ps.SalesAmount) AS SalesAmount,
	SUM(ps.ProfitAmount) AS ProfitAmount,
	SUM(ps.UnitsSold) AS UnitsSold,
	SUM(ps.UnitsSold) OVER (partition by ps.StoreKey ORDER BY ps.UnitsSold DESC) / SUM(NULLIF(ps.UnitsSold,0)) OVER (partition by ps.StoreKey) AS PorcetajeAcumulado,
	CASE
		WHEN SUM(ps.UnitsSold) OVER (partition by ps.StoreKey ORDER BY ps.UnitsSold DESC) / SUM(NULLIF(ps.UnitsSold,0)) OVER (partition by ps.StoreKey) <= 0.8 THEN 'A'
		WHEN SUM(ps.UnitsSold) OVER (partition by ps.StoreKey ORDER BY ps.UnitsSold DESC) / SUM(NULLIF(ps.UnitsSold,0)) OVER (partition by ps.StoreKey) <= 0.95 THEN 'B'
		ELSE 'C'
	END AS ABC
FROM ProductSales ps
GROUP BY
	StoreKey,
	ProductKey,
	UnitsSold

GO