/******************************* Alteryx Weekly Challenge 412 *************************************/
/*
This script work with precondition that 5 tables for individual years have already been combined
and the combined table with appropriate headers was created as database table under name [training].[awc412].
The below two SELECT statements show:
1 - Table with 5 positions with smallest average overall rank across all years
2 - Pivot table with individual annual rank averages for these 5 positions which forms table placed
	under the visualisation in Alteryx
*/
-- Result 1
SELECT TOP 5
  [POS], AVG(CAST([RK] AS decimal(19,2))) AS AVG_RK
  FROM [training].[awc412]
  GROUP BY [POS]
  ORDER BY [AVG_RK];

-- Result 2
WITH result_1 AS
(
  SELECT TOP 5
  [POS], AVG(CAST([RK] AS decimal(19,2))) AS AVG_RK
  FROM [training].[awc412]
  GROUP BY [POS]
  ORDER BY [AVG_RK]
)

  SELECT [POS], [2018] AS avg_2018, [2019] AS avg_2019, [2020] AS avg_2020, [2021] AS avg_2021, [2022] AS avg_2022
  FROM 
	(SELECT CAST([RK] AS decimal(19,2)) AS [RK], [training].[awc412].[POS], [Year] 
		FROM [training].[awc412]
		INNER JOIN result_1 ON result_1.POS = [training].[awc412].POS
		) AS table_source
  PIVOT
  (AVG([RK])
  FOR [Year] IN ([2018], [2019], [2020], [2021], [2022])) AS pivot_table