Solution.md

🚲 Adventure Works Cycle : Product Analysis

Solution

View the complete syntax here.

---

1. How many product do we manufacture?

SELECT COUNT(*) AS TotalNumberProduct
FROM Production.Product;

Steps:

Use COUNT to count all the rows in the Product table

Answer:

---

2. How many product do we have by category? by sub-category?

-- By Subcategory
SELECT ps.[Name] AS Subcategory, COUNT(*) AS TotalNumberProduct 
FROM Production.Product p
FULL JOIN Production.ProductSubcategory ps 
ON ps.ProductSubcategoryID = p.ProductSubcategoryID
GROUP BY ps.[Name];

-- By Category
SELECT pc.[Name] AS Category, ps.[Name] AS Subcategory, COUNT(p.ProductID) AS TotalNumberProduct 
FROM Production.ProductSubcategory ps
FULL JOIN  Production.Product p
ON ps.ProductSubcategoryID = p.ProductSubcategoryID
FULL JOIN Production.ProductCategory pc
ON pc.ProductCategoryID = ps.ProductCategoryID
GROUP BY pc.[Name], ps.[Name]
ORDER BY pc.[Name];

Steps:

• Use COUNT and GROUP BY to find out TotalNumberProduct by each Subcategory and Category.
• To calculate TotalNumberProduct by each Category, use JOIN to merge ProductSubcategory, Product and ProductCategory tables as the first one is linking the two tables.

Answer:

---

3. How many product do we have by productline? by style? by class?

--by productline R = Road, M = Mountain, T = Touring, S = Standard
SELECT ProductLine, COUNT(*) AS TotalNumberProduct 
FROM Production.Product
GROUP BY ProductLine;

--by style W = Womens, M = Mens, U = Universal
SELECT Style, COUNT(*) AS TotalNumberProduct 
FROM Production.Product
GROUP BY Style;

--by class H = High, M = Medium, L = Low
SELECT Class, COUNT(*) AS TotalNumberProduct 
FROM Production.Product
GROUP BY Class;

Steps:

Use COUNT and GROUP BY to find out TotalNumberProduct by each Productline, Style and Class.

Answer:

---

4. What are the product that aren't sell by the company anymore?

SELECT *
FROM Production.Product 
WHERE SellEndDate IS NOT NULL;

Steps:

SELECT all product where SellEndDate is not null.

Answer:

---

5. How many product model do we have?

SELECT COUNT(*) AS TotalNumberModel 
FROM Production.ProductModel;

Steps:

Use COUNT to count all the rows in the ProductModel table

Answer :

---

6. What is the average cost of product? by category? by sub-category? by productline? by style? by class?

SELECT AVG(StandardCost) AS AverageProductCost
FROM Production.Product
WHERE StandardCost <> 0; --Use to filter BOM elements which cost are equal to 0.

-- By Subcategory
SELECT ps.[Name] AS Subcategory , AVG(p.StandardCost) AS AverageProductCost
FROM Production.Product p
FULL JOIN Production.ProductSubcategory ps 
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
AND StandardCost <> 0 --Use to filter BOM elements which cost are equal to 0.
GROUP BY ps.[Name];

-- By Category
SELECT pc.[Name] AS Category, ps.[Name] AS Subcategory, AVG(p.StandardCost) AS AverageProductCost 
FROM Production.ProductSubcategory ps
FULL JOIN  Production.Product p
ON ps.ProductSubcategoryID = p.ProductSubcategoryID AND StandardCost <> 0
FULL JOIN Production.ProductCategory pc
ON pc.ProductCategoryID = ps.ProductCategoryID
GROUP BY pc.[Name], ps.[Name]
ORDER BY pc.[Name];

-- By Productline
SELECT ProductLine, AVG(StandardCost) AS AverageProductCost
FROM Production.Product
WHERE StandardCost <> 0 --Use to filter BOM elements which cost are equal to 0.
GROUP BY ProductLine;

-- By Style
SELECT Style, AVG(StandardCost) AS AverageProductCost
FROM Production.Product
WHERE StandardCost <> 0 --Use to filter BOM elements which cost are equal to 0.
GROUP BY Style;

-- By Class
SELECT Class, AVG(StandardCost) AS AverageProductCost
FROM Production.Product
WHERE StandardCost <> 0 --Use to filter BOM elements which cost are equal to 0.
GROUP BY Class;

Steps:

Use AVG to find out AverageProductCost for each Category, Subcategory, Productline, Style and Class.

Answer:

---

7. What is the average listing price of product? by category? by sub-category? by productline? by style? by class?

SELECT AVG(ListPrice) AS AverageProductListingPrice
FROM Production.Product
WHERE ListPrice <> 0;

-- By Subcategory
SELECT ps.[Name] AS Subcategory , AVG(p.ListPrice) AS AverageProductListingPrice
FROM Production.Product p
FULL JOIN Production.ProductSubcategory ps 
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
AND p.ListPrice <> 0
GROUP BY ps.[Name];

-- By Category
SELECT pc.[Name] AS Category, ps.[Name] AS Subcategory, AVG(p.ListPrice) AS AverageProductListingPrice 
FROM Production.ProductSubcategory ps
FULL JOIN  Production.Product p
ON ps.ProductSubcategoryID = p.ProductSubcategoryID AND p.ListPrice <> 0
FULL JOIN Production.ProductCategory pc
ON pc.ProductCategoryID = ps.ProductCategoryID
GROUP BY pc.[Name], ps.[Name]
ORDER BY pc.[Name];

-- By Productline
SELECT ProductLine, AVG(ListPrice) AS AverageProductListingPrice
FROM Production.Product
WHERE ListPrice <> 0
GROUP BY ProductLine;

-- By Style
SELECT Style, AVG(ListPrice) AS AverageProductListingPrice
FROM Production.Product
WHERE ListPrice <> 0
GROUP BY Style;

-- By Class
SELECT Class, AVG(ListPrice) AS AverageProductListingPrice
FROM Production.Product
WHERE ListPrice <> 0
GROUP BY Class;

Steps:

Use AVG to find out AverageProductListingPrice for each Category, Subcategory, Productline, Style and Class.

Answer:

---

8. How many product did we sell through our channels (resellers, marketing, online stores, refferals)?

-- Online Store Orders 
SELECT COUNT(*) AS TotalNumberOrder
FROM Sales.SalesOrderHeader
WHERE OnlineOrderFlag = '1' AND YEAR(OrderDate) = '2014'; -- OnlineOrderFlag : 1= Order coming from onlinestore, 0= Order coming from physical stores

-- Resallers Orders 
SELECT COUNT(*) AS TotalNumberOrder
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL AND YEAR(OrderDate) = '2014';-- Salesperson are always the one placing order for reseller of their territory

--Orders from marketing and referrals 
SELECT sr.[Name] AS Channels, COUNT(so.SalesOrderID) AS TotalNumberOrder
FROM Sales.SalesOrderHeaderSalesReason so
JOIN Sales.SalesReason sr 
ON so.SalesReasonID = sr.SalesReasonID AND YEAR(so.ModifiedDate) = '2014' 
AND (SR.ReasonType = 'Marketing' OR SR.ReasonType = 'Promotion' OR SR.[Name] = 'Review') -- SalesReason register the purchase reason of the customer
GROUP BY sr.[Name];

Steps:

• Use mainly COUNT and WHERE to find and filter the results

Answer:

---

9. How many purchase do we have by product during the year?

SELECT od.ProductID, p.[Name] AS Product, COUNT(*) AS TotalNumberOrder
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID = oh.SalesOrderID 
JOIN Production.Product p
ON p.ProductID = od.ProductID
WHERE YEAR(oh.OrderDate) = '2014'
GROUP BY od.ProductID, p.[Name]
ORDER BY TotalNumberOrder DESC;

Steps:

• JOIN SalesOrderDetail, SalesOrderHeader and Product to find orders for all products.
• Filter to only retrieve orders from 2014

Answer:

---

10. Which product is the most purchase by customers during the past years?

WITH cte_product_ordered ([YEAR], ProductID, ProductName, OrderNumber) AS (
	SELECT YEAR(oh.OrderDate) as [YEAR], od.ProductID, p.[Name] AS ProductName, 
			COUNT(*) as OrderNumber
	FROM Sales.SalesOrderDetail od
	JOIN Sales.SalesOrderHeader oh
	ON od.SalesOrderID = oh.SalesOrderID 
	JOIN Production.Product p
	ON p.ProductID = od.ProductID
	GROUP BY YEAR(oh.OrderDate), od.ProductID, p.[Name]
)

SELECT po.[YEAR], po.ProductID, po.ProductName, po.OrderNumber
FROM cte_product_ordered po
INNER JOIN 
(SELECT [YEAR], MAX(OrderNumber) as MaxOrderNumber
FROM cte_product_ordered 
GROUP BY [YEAR]) pom
ON po.[YEAR] = pom.[YEAR] AND po.OrderNumber = pom.MaxOrderNumber;

Steps:

• Create a temp table to find orders for all products.
• Self JOIN the temp table to find the MaxOrderNumber.

Answer:

Note: This screenshot contains only a part of result table as it is contains many rows.

---

11. Which products are the most purchase by category? by sub-category?

-- By Subcategory
WITH cte_subcategory(ProductSubcategoryID, SubcategoryName, ProductID, ProductName) AS (
	SELECT ps.ProductSubcategoryID, ps.[Name] AS SubcategoryName, p.ProductID, p.[Name] AS ProductName
	FROM Production.Product p
	FULL JOIN Production.ProductSubcategory ps 
	ON p.ProductSubcategoryID = ps.ProductSubcategoryID
),
cte_product_ordered (ProductSubcategoryID, SubcategoryName, ProductID, ProductName, OrderNumber) AS (
	SELECT  s.ProductSubcategoryID,
			s.SubcategoryName,
			od.ProductID, 
			s.ProductName, 
			COUNT(*) as OrderNumber
	FROM Sales.SalesOrderDetail od
	JOIN Sales.SalesOrderHeader oh
	ON od.SalesOrderID = oh.SalesOrderID 
	FULL JOIN cte_subcategory s
	ON s.ProductID = od.ProductID
	WHERE YEAR(oh.OrderDate) = '2014'
	GROUP BY s.ProductSubcategoryID, s.SubcategoryName, od.ProductID, s.ProductName
)
SELECT  po.ProductSubcategoryID, po.SubcategoryName, po.ProductID, po.ProductName, po.OrderNumber
FROM cte_product_ordered po
INNER JOIN 
(SELECT ProductSubcategoryID, MAX(OrderNumber) as MaxOrderNumber
FROM cte_product_ordered 
GROUP BY ProductSubcategoryID) pom
ON po.ProductSubcategoryID = pom.ProductSubcategoryID AND po.OrderNumber = pom.MaxOrderNumber
ORDER BY po.OrderNumber DESC;

-- By Category
WITH cte_Category(Category, SubcategoryName, ProductID, ProductName) AS (
	SELECT pc.[Name] AS Category, ps.[Name] AS SubcategoryName, p.ProductID, p.[Name] AS ProductName
	FROM Production.ProductSubcategory ps
	FULL JOIN  Production.Product p
	ON p.ProductSubcategoryID = ps.ProductSubcategoryID
	FULL JOIN Production.ProductCategory pc
	ON pc.ProductCategoryID = ps.ProductCategoryID
	GROUP BY pc.[Name], ps.[Name], p.ProductID, p.[Name]
),
cte_product_ordered (Category, SubcategoryName, ProductID, ProductName, OrderNumber) AS (
	SELECT  c.Category,
			c.SubcategoryName,
			od.ProductID, 
			c.ProductName, 
			COUNT(*) as OrderNumber
	FROM Sales.SalesOrderDetail od
	JOIN Sales.SalesOrderHeader oh
	ON od.SalesOrderID = oh.SalesOrderID 
	FULL JOIN cte_Category c
	ON c.ProductID = od.ProductID
	WHERE YEAR(oh.OrderDate) = '2014'
	GROUP BY c.Category, c.SubcategoryName, od.ProductID, c.ProductName
)
SELECT  po.Category, po.SubcategoryName, po.ProductID, po.ProductName, po.OrderNumber
FROM cte_product_ordered po
INNER JOIN 
(SELECT Category, MAX(OrderNumber) as MaxOrderNumber
FROM cte_product_ordered 
GROUP BY Category) pom
ON po.Category = pom.Category AND po.OrderNumber = pom.MaxOrderNumber
ORDER BY po.OrderNumber DESC;

Steps:

Same Steps as Question 10 just add a new temp table to calculate the number of by category and by subcategory.

Answer:

---

12. What is the total revenue by each product this year?

WITH cte_numberproduct (ProductID, Product, TotalNumberOrder) AS (
	SELECT od.ProductID, p.[Name] AS Product, COUNT(*) AS TotalNumberOrder--, (  COUNT(*) * p.ListPrice ) AS TotalRevenue
	FROM Sales.SalesOrderDetail od
	JOIN Sales.SalesOrderHeader oh
	ON od.SalesOrderID = oh.SalesOrderID 
	JOIN Production.Product p
	ON p.ProductID = od.ProductID
	WHERE YEAR(oh.OrderDate) = '2014'
	GROUP BY od.ProductID, p.[Name]
)
SELECT p.ProductID, np.Product, np.TotalNumberOrder ,(np.TotalNumberOrder * p.ListPrice) AS TotalRevenue
FROM Production.Product p
JOIN cte_numberproduct np
ON p.ProductID = np.ProductID
ORDER BY TotalRevenue DESC;

Steps:

• Create a temp table to calculate TotalNumberOrder by product and filter 2014.
• Multiply TotalNumberOrder by the respective ListPrice of the product to find the revenue of each product.

Answer:

---

13. Which product makes the highest revenue this year by category? by sub-category?

-- By Subcategory
WITH cte_subcategory(ProductSubcategoryID, SubcategoryName, ProductID, ProductName) AS (
	SELECT ps.ProductSubcategoryID, ps.[Name] AS SubcategoryName, p.ProductID, p.[Name] AS ProductName
	FROM Production.Product p
	FULL JOIN Production.ProductSubcategory ps 
	ON p.ProductSubcategoryID = ps.ProductSubcategoryID
),
cte_numberproduct (ProductSubcategoryID, SubcategoryName, ProductID, ProductName, TotalNumberOrder) AS (
	SELECT s.ProductSubcategoryID, s.SubcategoryName, od.ProductID, s.ProductName, COUNT(*) AS TotalNumberOrder
	FROM Sales.SalesOrderDetail od
	JOIN Sales.SalesOrderHeader oh
	ON od.SalesOrderID = oh.SalesOrderID 
	JOIN cte_subcategory s
	ON s.ProductID = od.ProductID
	WHERE YEAR(oh.OrderDate) = '2014'
	GROUP BY s.ProductSubcategoryID, s.SubcategoryName, od.ProductID, s.ProductName
),
cte_revenue (ProductSubcategoryID, SubcategoryName, ProductID, ProductName, TotalNumberOrder, TotalRevenue) AS (
	SELECT np.ProductSubcategoryID, np.SubcategoryName, np.ProductID, np.ProductName, 
			TotalNumberOrder, 
			(np.TotalNumberOrder * p.ListPrice) AS TotalRevenue
	FROM Production.Product p
	JOIN cte_numberproduct np	
	ON p.ProductID = np.ProductID
)
SELECT  r.ProductSubcategoryID, r.SubcategoryName, r.ProductID, r.ProductName, r.TotalNumberOrder, r.TotalRevenue
FROM cte_revenue r
INNER JOIN (
SELECT ProductSubcategoryID, MAX(TotalRevenue) AS MaxTotalRevenue
FROM cte_revenue 
GROUP BY ProductSubcategoryID) rm
ON r.ProductSubcategoryID = rm.ProductSubcategoryID AND r.TotalRevenue = rm.MaxTotalRevenue
ORDER BY r.TotalRevenue DESC;

-- By Category
WITH cte_category(Category, SubcategoryName, ProductID, ProductName) AS (
	SELECT pc.[Name] AS Category, ps.[Name] AS SubcategoryName, p.ProductID, p.[Name] AS ProductName
	FROM Production.ProductSubcategory ps
	FULL JOIN  Production.Product p
	ON p.ProductSubcategoryID = ps.ProductSubcategoryID
	FULL JOIN Production.ProductCategory pc
	ON pc.ProductCategoryID = ps.ProductCategoryID
	GROUP BY pc.[Name], ps.[Name], p.ProductID, p.[Name]
),
cte_product_ordered (Category, SubcategoryName, ProductID, ProductName, TotalNumberOrder) AS (
	SELECT  c.Category,
			c.SubcategoryName,
			od.ProductID, 
			c.ProductName, 
			COUNT(*) as TotalNumberOrder
	FROM Sales.SalesOrderDetail od
	JOIN Sales.SalesOrderHeader oh
	ON od.SalesOrderID = oh.SalesOrderID 
	FULL JOIN cte_category c
	ON c.ProductID = od.ProductID
	WHERE YEAR(oh.OrderDate) = '2014'
	GROUP BY c.Category, c.SubcategoryName, od.ProductID, c.ProductName
),
cte_revenue (Category, SubcategoryName, ProductID, ProductName, TotalNumberOrder, TotalRevenue) AS (
	SELECT np.Category, np.SubcategoryName, np.ProductID, np.ProductName, 
			np.TotalNumberOrder, 
			(np.TotalNumberOrder * p.ListPrice) AS TotalRevenue
	FROM Production.Product p
	JOIN cte_product_ordered np	
	ON p.ProductID = np.ProductID
)
SELECT  r.Category, r.SubcategoryName, r.ProductID, r.ProductName, r.TotalNumberOrder, r.TotalRevenue
FROM cte_revenue r
INNER JOIN (
SELECT Category, MAX(TotalRevenue) AS MaxTotalRevenue
FROM cte_revenue 
GROUP BY Category) rm
ON r.Category = rm.Category AND r.TotalRevenue = rm.MaxTotalRevenue
ORDER BY r.TotalRevenue DESC;

Steps:

• Add 2 temp tables to find the category and subcategory for each product.
• Self JOIN the cte_revenue temp table to find the MaxTotalRevenue for each category and subcategory.

Answer:

---