/******************************************** Alteryx Weekly Challenge 410 *******************************************
* This script works with the following pre-conditions:
1/ Alteryx input was created as a database table under schema and table_name [training].[awc410]
2/ Database user has ALTER permissions on this table 
*/

-- Add identity column to table [training].[awc410] 
-- which creates a row_id (used for ORDER BY in window functions later)
-- NOTE - Alter statement can be run only once and then it needs to be commented out
-- if the user wants to cahnge the final SELECT statement in order to query one of 2 results
ALTER TABLE [training].[awc410]
ADD row_id int IDENTITY(1,1);
-- Create column 'Product_ID'
WITH 
f1 AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY row_id) AS id1 
,[Field1] AS [Product_ID]
FROM [training].[awc410]
WHERE ([Field1] IS NOT NULL) AND ([Field1] <> 'Product ID')
)
,
-- Create column 'Launch_Date'
f2 AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY row_id) AS id2 
,[Field2] AS [Launch_Date]
FROM [training].[awc410]
WHERE ([Field2] IS NOT NULL) AND ([Field2] <> 'Launch Date')
)
,
-- Create column 'Units_Produced'
f3_a AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY row_id) AS id_for_modulo
,[Field4] AS [Units_Produced]
FROM [training].[awc410]
WHERE ([Field4] IS NOT NULL)
)
,
f3 AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY id_for_modulo) AS id3
,[Units_Produced]
FROM f3_a
WHERE (id_for_modulo % 2 <> 0) AND ([Units_Produced] <> 'Units Produced')
)
,
-- Create column 'Spoilage'
f4 AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY id_for_modulo) AS id4
,[Units_Produced] AS [Spoilage]
FROM f3_a
WHERE (id_for_modulo % 2 = 0) AND ([Units_Produced] <> 'Spoilage')
)
,
-- Create column 'Units_Sold'
f5_a AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY row_id) AS id_for_modulo
,[Field5] AS [Units_Sold]
FROM [training].[awc410]
WHERE ([Field5] IS NOT NULL)
)
,
f5 AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY id_for_modulo) AS id5
,[Units_Sold]
FROM f5_a
WHERE (id_for_modulo % 2 <> 0) AND ([Units_Sold] <> 'Units Sold')
)
,
-- Create column 'Sales_USD'
f6 AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY id_for_modulo) AS id6
,[Units_Sold] AS [Sales_USD]
FROM f5_a
WHERE (id_for_modulo % 2 = 0) AND ([Units_Sold] <> 'Sales (USD)')
)
,
-- Combine all columns together to create transformed awc410
awc410_transformed AS
(
SELECT 
f1.[Product_ID]
,f2.[Launch_Date]
,CAST(f3.[Units_Produced] AS int) AS [Units_Produced]
,CAST(f4.[Spoilage] AS int) AS [Spoilage]
,f5.[Units_Sold]
,CAST(f6.[Sales_USD] AS decimal(19,2)) AS [Sales_USD]
FROM f1
INNER JOIN f2 ON f1.id1 = f2.id2
INNER JOIN f3 ON f1.id1 = f3.id3
INNER JOIN f4 ON f1.id1 = f4.id4
INNER JOIN f5 ON f1.id1 = f5.id5
INNER JOIN f6 ON f1.id1 = f6.id6
)
,
-- Result 1 - TOP5 spoilage (use the final SELECT statement to query the result)
result_1 AS
(
SELECT TOP 5 *
FROM awc410_transformed
ORDER BY [Spoilage] DESC
)
,
-- Result 2 - bottom 5 Sale_per_unit (use the final SELECT statement to query the result)
result_2 AS
(
SELECT TOP 5 *
,CAST([Sales_USD]/[Units_Produced] AS decimal(19,2)) AS Sale_per_unit
FROM awc410_transformed
ORDER BY Sale_per_unit
)

-- final SELECT presenting individual results (only change CTE reference in FROM clause)
SELECT *
FROM result_2
;