Adventure_analysis.sql

--This is an Analysis of the AdventureWorks Sales
USE [AdventureWorksDW2019]
--List the Revenue by Location(Country) for Internet Sales Only
SELECT 
	ISNULL(A.[SalesTerritoryCountry],'TOTAL') AS Country
	,FORMAT(SUM(B.[SalesAmount]),'$#,0.00') AS Revenue
FROM 
	[dbo].[DimSalesTerritory] A 
	LEFT JOIN [dbo].[FactInternetSales] B
		ON A.SalesTerritoryKey=B.SalesTerritoryKey
WHERE 
	B.[SalesAmount] Is NOT NULL
GROUP BY 
	A.[SalesTerritoryCountry] WITH ROLLUP
ORDER BY          /* Order by the country and have the group total at the bottom*/
	CASE 
		WHEN SalesTerritoryCountry IS NULL THEN 1
		ELSE 0
	END
	ASC, country ASC;

--List the Revenue by Location(Country) for Reseller Only
SELECT 
	ISNULL(A.[SalesTerritoryCountry],'TOTAL') AS Country
	,FORMAT(SUM(B.[SalesAmount]),'$#,0.00') AS Revenue
FROM 
	[dbo].[DimSalesTerritory] A 
	LEFT JOIN [dbo].[FactResellerSales] B
		ON A.SalesTerritoryKey=B.SalesTerritoryKey
WHERE 
	B.[SalesAmount] Is NOT NULL
GROUP BY 
	A.[SalesTerritoryCountry] WITH ROLLUP
ORDER BY          /* Order by the country and have the group total at the bottom*/
	CASE 
		WHEN SalesTerritoryCountry IS NULL THEN 1
		ELSE 0
	END
	ASC, country ASC;

--List the Revenue by Location(Country and continent) for Internet Sales and Reseller Sales
SELECT 
	A.[SalesTerritoryCountry] AS Country
	,A.[SalesTerritoryGroup] AS Continent
	,FORMAT(SUM(B.[SalesAmount]),'$#,0.00') AS Revenue
FROM 
	[dbo].[DimSalesTerritory] A 
	LEFT JOIN [dbo].[FactInternetSales] B
		ON A.SalesTerritoryKey=B.SalesTerritoryKey
	LEFT JOIN [dbo].[FactResellerSales] C
		ON B.SalesTerritoryKey=C.SalesTerritoryKey
WHERE 
	B.[SalesAmount] Is NOT NULL
GROUP BY 
	A.[SalesTerritoryCountry]
	,A.[SalesTerritoryGroup]
ORDER BY 
	Revenue DESC;

--List the Revenue by Location(Country) for Internet Sales and Reseller Sales on separate columns
SELECT 
	A.[SalesTerritoryCountry] AS Country
	,A.[SalesTerritoryGroup] AS Continent
	,FORMAT(SUM(B.[SalesAmount]),'$#,0.00') AS internetRevenue
	,FORMAT(SUM(C.[SalesAmount]),'$#,0.00') AS resellerRevenue
FROM 
	[dbo].[DimSalesTerritory] A 
	LEFT JOIN [dbo].[FactInternetSales] B
		ON A.SalesTerritoryKey=B.SalesTerritoryKey
	LEFT JOIN [dbo].[FactResellerSales] C
		ON B.SalesTerritoryKey=C.SalesTerritoryKey
WHERE 
	B.[SalesAmount] Is NOT NULL
GROUP BY 
	A.[SalesTerritoryCountry]
	,A.[SalesTerritoryGroup]
ORDER BY 
	internetRevenue DESC
	,resellerRevenue DESC;

--List Revenue by Product Category for both Internet and Reseller Sales
SELECT 
	E.EnglishProductCategoryName AS CategoryName
	,FORMAT(SUM(B.[SalesAmount]),'$#,0.00') AS Revenue
FROM 
	[dbo].[DimProduct] A
	LEFT JOIN [dbo].[FactInternetSales] B
		ON A.ProductKey=B.ProductKey
	LEFT JOIN [dbo].[FactResellerSales] C
		ON B.ProductKey=C.ProductKey
	LEFT JOIN [dbo].[DimProductSubcategory] D
		ON A.ProductSubcategoryKey=D.ProductSubcategoryKey
	LEFT JOIN [dbo].[DimProductCategory] E
		ON D.ProductCategoryKey=E.ProductCategoryKey
WHERE 
	B.[SalesAmount] Is NOT NULL
GROUP BY 
	E.EnglishProductCategoryName
ORDER BY 
	Revenue DESC;

--List Top 10 Product by Revenue for both Internet and Reseller Sales
SELECT TOP 10 
	A.EnglishProductName AS Product
	,SUM(B.[SalesAmount]) AS Revenue
FROM 
	[dbo].[DimProduct] A
	LEFT JOIN [dbo].[FactInternetSales] B
		ON A.ProductKey=B.ProductKey
	LEFT JOIN [dbo].[FactResellerSales] C
		ON B.ProductKey=C.ProductKey
WHERE 
	B.[SalesAmount] Is NOT NULL
GROUP BY 
	A.EnglishProductName
ORDER BY 
	Revenue DESC;

--List Bottom 10 Product by Revenue for both Internet and Reseller Sales
SELECT TOP 10 
	A.EnglishProductName AS Product
	,SUM(B.[SalesAmount]) AS Revenue
FROM 
	[dbo].[DimProduct] A
	LEFT JOIN [dbo].[FactInternetSales] B
		ON A.ProductKey=B.ProductKey
	LEFT JOIN [dbo].[FactResellerSales] C
		ON B.ProductKey=C.ProductKey
WHERE 
	B.[SalesAmount] Is NOT NULL
GROUP BY 
	A.EnglishProductName
ORDER BY 
	Revenue ASC;

--List the Revenue generated by Sales Representative and the country
SELECT
	CONCAT(A.FirstName,' ',A.LastName) AS FullName
	,A.EmailAddress
	,C.SalesTerritoryCountry
	,SUM(B.SalesAmount) AS Revenue
FROM 
	[dbo].[DimEmployee] A
	LEFT JOIN [dbo].[FactResellerSales] B
		ON A.EmployeeKey=B.EmployeeKey
	LEFT JOIN [dbo].[DimSalesTerritory] C
		ON B.SalesTerritoryKey=C.SalesTerritoryKey
WHERE 
	Title LIKE 'Sales Representative'
GROUP BY 
	CONCAT(A.FirstName,' ',A.LastName)
	,A.EmailAddress
	,C.SalesTerritoryCountry
ORDER BY 
	Revenue DESC
	,FullName

--List the Internet Sales Revenue generated by Month

SELECT 
	[MonthNumberOfYear]
    ,[CalendarYear] AS Year_
	,[EnglishMonthName] AS Month_
    ,FORMAT(SUM([SalesAmount]),'$#,0.00') AS Revenue
FROM 
	[dbo].[FactInternetSales] A
	LEFT JOIN [dbo].[DimDate] B
		ON A.OrderDateKey=B.DateKey
GROUP BY 
	[MonthNumberOfYear]
	,[CalendarYear]
	,[EnglishMonthName]
ORDER BY
	[CalendarYear] DESC
	,[MonthNumberOfYear] DESC;

--List the Reseller Sales Revenue generated by Month

SELECT 
	[MonthNumberOfYear]
    ,[CalendarYear] AS Year_
	,[EnglishMonthName] AS Month_
    ,FORMAT(SUM([SalesAmount]),'$#,0.00') AS Revenue
FROM 
	[dbo].[FactResellerSales] A
	LEFT JOIN [dbo].[DimDate] B
		ON A.OrderDateKey=B.DateKey
GROUP BY 
	[MonthNumberOfYear]
	,[CalendarYear]
	,[EnglishMonthName]
ORDER BY
	[CalendarYear] DESC
	,[MonthNumberOfYear] DESC;

--What is the Month over Month Growth for Internet Sales?

SELECT [CalendarYear] AS [Year] 
       ,[MonthNumberOfYear] AS [Month] 
       ,FORMAT(SUM([SalesAmount]), '$#,0.00') AS InternetRevenue 
       ,CONCAT(
		   100*(SUM([SalesAmount])-LAG(SUM([SalesAmount]), 1, 0) OVER(ORDER BY [CalendarYear],[MonthNumberOfYear] ASC))/LAG(SUM([SalesAmount]), 1) OVER(ORDER BY [CalendarYear],[MonthNumberOfYear] ASC)
		   ,'%'
		) AS [MoM Growth]
FROM [dbo].[FactInternetSales] A
LEFT JOIN [dbo].[DimDate] B ON A.OrderDateKey=B.DateKey
GROUP BY [CalendarYear] ,
         [MonthNumberOfYear]
ORDER BY [Year] DESC,
         [Month] DESC;

--What is the Month over Month Growth for Reseller Sales?

SELECT [CalendarYear] AS [Year] 
       ,[MonthNumberOfYear] AS [Month] 
       ,FORMAT(SUM([SalesAmount]), '$#,0.00') AS ResellerRevenue 
       ,CONCAT(
		   100*(SUM([SalesAmount])-LAG(SUM([SalesAmount]), 1, 0) OVER(ORDER BY [CalendarYear],[MonthNumberOfYear] ASC))/LAG(SUM([SalesAmount]), 1) OVER(ORDER BY [CalendarYear],[MonthNumberOfYear] ASC)
		   ,'%'
		) AS [MoM Growth]
FROM [dbo].[FactResellerSales] A
LEFT JOIN [dbo].[DimDate] B ON A.OrderDateKey=B.DateKey
GROUP BY [CalendarYear] ,
         [MonthNumberOfYear]
ORDER BY [Year] DESC,
         [Month] DESC;

---What is the Monthly Active Customers for Internet Sales ?
SELECT 
	[MonthNumberOfYear]
    ,[CalendarYear] AS Year
	,[EnglishMonthName] AS Month
    ,COUNT(DISTINCT[CustomerKey]) AS TotalCustomer
FROM 
	[dbo].[FactInternetSales] A
	LEFT JOIN [dbo].[DimDate] B
		ON A.OrderDateKey=B.DateKey
GROUP BY 
	[MonthNumberOfYear]
	,[CalendarYear]
	,[EnglishMonthName]
ORDER BY
	[CalendarYear] DESC
	,[MonthNumberOfYear] DESC;

---What is the Monthly Active Customers for Reseller Sales ?
SELECT 
	[MonthNumberOfYear]
    ,[CalendarYear] AS Year
	,[EnglishMonthName] AS Month
    ,COUNT(DISTINCT[ResellerKey]) AS TotalReseller
FROM 
	[dbo].[FactResellerSales] A
	LEFT JOIN [dbo].[DimDate] B
		ON A.OrderDateKey=B.DateKey
GROUP BY 
	[MonthNumberOfYear]
	,[CalendarYear]
	,[EnglishMonthName]
ORDER BY
	[CalendarYear] DESC
	,[MonthNumberOfYear] DESC;