USE AdventureWorksDW
GO

CREATE VIEW [dbo].[CustomersHistory] AS
WITH Cust AS 
	(SELECT
	pc.[EnglishProductCategoryName] AS ProductCategoryName
	,c.[CustomerKey]
	,f.OrderQuantity AS Quantity
	,f.ExtendedAmount AS Amount
	,d.CalendarYear 
	FROM
	[dbo].[FactInternetSales] f
	INNER JOIN [dbo].[DimProduct] p
	ON f.[ProductKey] = p.[ProductKey]
	INNER JOIN [dbo].[DimDate] d
	ON f.[OrderDateKey] = d.[DateKey]
	INNER JOIN [dbo].[DimProductSubcategory] psc
	ON p.[ProductSubcategoryKey] = psc.[ProductSubcategoryKey]
	INNER JOIN [dbo].[DimProductCategory] pc
	ON psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
	INNER JOIN [dbo].[DimCustomer] c
	ON f.[CustomerKey] = c.[CustomerKey])
SELECT
ROW_NUMBER() OVER(ORDER BY c.[FirstName]) AS ID
,c.[FirstName],c.[LastName],c.Title,c.[BirthDate],c.[MaritalStatus],c.[Gender]
,c.[TotalChildren],c.[NumberChildrenAtHome],c.[EnglishEducation] AS Education
,c.[EnglishOccupation] AS Occupation,c.[YearlyIncome],c.[HouseOwnerFlag]
,c.[NumberCarsOwned],c.[DateFirstPurchase],c.EmailAddress,x.MinCalendarYear
,x.Amount AS TotalAmount,x.Quantity AS TotalQuantity,x.BikesQuantity,x.BikesAmount
,x.ClothingQuantity,x.ClothingAmount,x.AccessoriesQuantity,x.AccessoriesAmount
FROM
[dbo].[DimCustomer] c INNER JOIN (
	SELECT
	[CustomerKey]
	,SUM(CASE ProductCategoryName
		WHEN 'Bikes' THEN  Quantity
		ELSE 0 
		END) AS [BikesQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Bikes' THEN  Amount
		ELSE 0 
		END) AS [BikesAmount]
	,SUM(CASE ProductCategoryName
		WHEN 'Clothing' THEN  Quantity
		ELSE 0 
		END) AS [ClothingQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Clothing' THEN  Amount
		ELSE 0 
		END) AS [ClothingAmount]
	,SUM(CASE ProductCategoryName
		WHEN 'Accessories' THEN  Quantity
		ELSE 0 
		END) AS [AccessoriesQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Accessories' THEN  Amount
		ELSE 0 
		END) AS [AccessoriesAmount]
	,SUM(Quantity) AS Quantity,SUM(Amount) AS Amount,MIN(CalendarYear) AS MinCalendarYear
	FROM Cust 
	GROUP BY
	[CustomerKey]
	) AS [x]
ON c.[CustomerKey] = x.[CustomerKey];
GO

SELECT YearlyIncome, Occupation
FROM dbo.CustomersHistory
ORDER BY YearlyIncome;
GO


SELECT YearlyIncome, Count(*) AS Freq
FROM dbo.CustomersHistory
GROUP BY YearlyIncome
ORDER BY YearlyIncome;
GO

WITH Duplicates AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY ProductKey ORDER BY ProductAlternateKey) AS Number
FROM dbo.DimProduct)
SELECT ProductKey,ProductAlternateKey,EnglishProductName FROM Duplicates
WHERE Number >1;
GO

WITH Duplicates AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY ProductKey ORDER BY ProductAlternateKey) AS Number
FROM dbo.DimProduct)
DELETE FROM Duplicates
WHERE Number >1;
GO

SELECT *
FROM dbo.CustomersHistory
WHERE NumberCarsOwned <0 OR NumberCarsOwned>5;
GO

SELECT *
FROM dbo.CustomersHistory
WHERE NumberChildrenAtHome > TotalChildren;
GO

SELECT TOP 100 YearlyIncome
INTO #P100
FROM dbo.CustomersHistory
ORDER BY NEWID();
GO

SELECT YearlyIncome, COUNT(*) AS Freq
FROM #P100
GROUP BY YearlyIncome
ORDER BY YearlyIncome;
GO

USE DataMiningDW
GO

SELECT ID,
CASE WHEN (Gender IS NULL) THEN 0 ELSE 1 END MissingGender,
CASE WHEN (City IS NULL) THEN 0 ELSE 1 END MissingCity,
CASE WHEN (State IS NULL) THEN 0 ELSE 1 END MissingState,
CASE WHEN (ZIP IS NULL) THEN 0 ELSE 1 END MissingZIP,
CASE WHEN (StoreCd IS NULL) THEN 0 ELSE 1 END MissingStoreCd,
CASE WHEN (Source IS NULL) THEN 0 ELSE 1 END MissingSource,
CASE WHEN (Age IS NULL) THEN 0 ELSE 1 END MissingAge,
CASE WHEN (YearsRunning IS NULL) THEN 0 ELSE 1 END MissingYearsRunning,
CASE WHEN (MilesWeek IS NULL) THEN 0 ELSE 1 END MissingMilesWeek,
CASE WHEN (RacesYear IS NULL) THEN 0 ELSE 1 END MissingRacesYear,
CASE WHEN (Triathlete IS NULL) THEN 0 ELSE 1 END MissingTriathlete,
CASE WHEN (Style IS NULL) THEN 0 ELSE 1 END MissingStyle,
CASE WHEN (ShoeCode IS NULL) THEN 0 ELSE 1 END MissingShoeCode,
CASE WHEN (PurchaseNumber IS NULL) THEN 0 ELSE 1 END MissingPurchaseNumber
INTO ShoeMVP
FROM Shoe;


USE AdventureWorksDW
GO

ALTER VIEW [dbo].[CustomersHistory] AS
WITH Cust AS 
	(SELECT
	pc.[EnglishProductCategoryName] AS ProductCategoryName
	,c.[CustomerKey]
	,f.OrderQuantity AS Quantity
	,f.ExtendedAmount AS Amount
	FROM
	[dbo].[FactInternetSales] f
	INNER JOIN [dbo].[DimProduct] p
	ON f.[ProductKey] = p.[ProductKey]
	INNER JOIN [dbo].[DimProductSubcategory] psc
	ON p.[ProductSubcategoryKey] = psc.[ProductSubcategoryKey]
	INNER JOIN [dbo].[DimProductCategory] pc
	ON psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
	INNER JOIN [dbo].[DimCustomer] c
	ON f.[CustomerKey] = c.[CustomerKey])
SELECT
ROW_NUMBER() OVER(ORDER BY c.[FirstName]) AS ID
,c.[FirstName],c.[LastName],c.Title,c.[BirthDate],c.[MaritalStatus],c.[Gender]
,c.[TotalChildren],c.[NumberChildrenAtHome],c.[EnglishEducation] AS Education
,c.[EnglishOccupation] AS Occupation,c.[YearlyIncome],c.[HouseOwnerFlag]
,c.[NumberCarsOwned],c.[DateFirstPurchase],c.EmailAddress,x.Amount AS TotalAmount
,x.Quantity AS TotalQuantity,x.BikesQuantity,x.BikesAmount
,x.ClothingQuantity,x.ClothingAmount,x.AccessoriesQuantity,x.AccessoriesAmount
FROM
[dbo].[DimCustomer] c INNER JOIN (
	SELECT
	[CustomerKey]
	,SUM(CASE ProductCategoryName
		WHEN 'Bikes' THEN  Quantity
		ELSE 0 
		END) AS [BikesQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Bikes' THEN  Amount
		ELSE 0 
		END) AS [BikesAmount]
	,SUM(CASE ProductCategoryName
		WHEN 'Clothing' THEN  Quantity
		ELSE 0 
		END) AS [ClothingQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Clothing' THEN  Amount
		ELSE 0 
		END) AS [ClothingAmount]
	,SUM(CASE ProductCategoryName
		WHEN 'Accessories' THEN  Quantity
		ELSE 0 
		END) AS [AccessoriesQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Accessories' THEN  Amount
		ELSE 0 
		END) AS [AccessoriesAmount]
	,SUM(Quantity) AS Quantity,SUM(Amount) AS Amount
	FROM Cust 
	GROUP BY
	[CustomerKey]
	) AS [x]
ON c.[CustomerKey] = x.[CustomerKey];
GO
