--------------------------------------- Alteryx weekly challenge 381 -------------------------------------
---- NOTE ---- This script worj under condition that the Alteryx input CSV file was imported as a table into
---- NOTE ---- the SQL database as a table with name awc381 under schema training (training.awc381)
-- 1 --
-- Alter the table and add index column.
-- This will be needed for any sort order.
ALTER TABLE [training].[awc381]
ADD RowID int IDENTITY(1, 1);

-- 2 --
-- Create CTE with all numeric fields converted to numbers. 
-- This will be needed for further aggregations.
WITH starting_pvt AS (
SELECT [RowID], [Employee ID], [Employee Name],
	CAST([January] AS numeric) AS [January],
	CAST([February] AS numeric) AS [February],
	CAST([March] AS numeric) AS [March],
	CAST([April] AS numeric) AS [April],
	CAST([May] AS numeric) AS [May],
	CAST([June] AS numeric) AS [June],
	CAST([July] AS numeric) AS [July],
	CAST([August] AS numeric) AS [August],
	CAST([September] AS numeric) AS [September],
	CAST([October] AS numeric) AS [October],
	CAST([November] AS numeric) AS [November],
	CAST([December] AS numeric) AS [December]
  FROM [training].[awc381])
,
-- 3 -- 
-- Create CTE with unpivoted table
starting_unpvt AS (
SELECT [RowID], [Employee ID], [Employee Name], [Month], [Sales]
FROM 
	(SELECT [RowID], [Employee ID], [Employee Name]
      ,[January]
      ,[February]
      ,[March]
      ,[April]
      ,[May]
      ,[June]
      ,[July]
      ,[August]
      ,[September]
      ,[October]
      ,[November]
      ,[December]
	FROM starting_pvt) AS pvt
UNPIVOT
	([Sales] FOR [Month] IN ([January]
      ,[February]
      ,[March]
      ,[April]
      ,[May]
      ,[June]
      ,[July]
      ,[August]
      ,[September]
      ,[October]
      ,[November]
      ,[December])) AS unpvt)
,
-- 4 -- 
-- Create CTE with month number for further filtering and join 
step1 AS (
SELECT TOP 1000 *,
	ROW_NUMBER() OVER(PARTITION BY RowID ORDER BY RowID) AS [MonthNo]
FROM starting_unpvt
ORDER BY [RowID], [MonthNo])
,
-- 5 -- 
-- Create CTE with M1 identification (i.e. where the sales are not nil)
step2 AS (
SELECT TOP 1000 *,
	CASE
		WHEN [Sales] != 0 THEN [MonthNo] ELSE 0
	END AS M1
FROM step1
ORDER BY [RowID], [MonthNo])
,
-- 6 -- 
-- Create CTE with calculated M2 and M3 (essentially increments by one)
step3 AS (
SELECT TOP 1000 *,
	M1+1 AS M2,
	M1+2 AS M3
FROM step2
ORDER BY [RowID], [MonthNo])
,
-- 7 --
-- Create CTE with created help column for future filtering only to relevant column
step4 AS (
SELECT TOP 1000 *,
	ROW_NUMBER() OVER(PARTITION BY RowID ORDER BY MonthNo) AS [MonthToKeep]
FROM step3
WHERE M1 != 0
ORDER BY [RowID], [MonthNo])
,
-- 8 --
-- Create CTE with only relevant columns ready for unpivot no. 2
step5 AS (
SELECT *
FROM step4
WHERE [MonthToKeep] = 1)
,
-- 9 --
-- Create CTE with unpivoted data ready for the join
step6 AS (
SELECT [RowID], [Employee ID], [Employee Name], [MonthName], [MonthNo2]
FROM (SELECT * FROM step5) AS p
UNPIVOT
	([MonthNo2] FOR [MonthName] IN ([M1], [M2], [M3])) AS unp)
,
-- 10 --
-- Create CTE with joined information to get sales values for relevant months only
step7 AS (
SELECT s6.[Employee ID], s6.[Employee Name], s1.[Sales]
FROM step6 AS s6
INNER JOIN step1 AS s1 ON s6.MonthNo2 = s1.MonthNo AND s6.[Employee ID] = s1.[Employee ID])
-- 11 --
-- Build the final table with aggregated result
SELECT [Employee ID], [Employee Name], FORMAT(AVG([Sales]), 'N', 'en-en')  AS [Average_Sales]
FROM step7
GROUP BY [Employee ID], [Employee Name]
ORDER BY [Employee ID]
;