/******************************************* Alteryx Weekly Challenge 419 *****************************
NOTE_1 - the below script works with pre-condition that Alteryx inputs were created as tables
in the database. Schema and table names as per below:
[training].[awc419_basket_contents]
[training].[awc419_stuffed_bunny_pricelist]
[training].[awc419_treats_pricelist]
NOTE_2 - to see the results, scroll to bottom of the query and uncomemnt/comment SELECT statements
*/
WITH price_list_long AS
(
SELECT CAST([Basket List] AS varchar(50)) AS [Basket List]
      ,[Purchase Year]
      ,CAST(t.[Treat] AS varchar(50)) AS [Product]
	  ,tpc.[Price]
  FROM [training].[awc419_basket_contents] AS t
  INNER JOIN [training].[awc419_treats_pricelist] AS tpc ON CAST(tpc.[Treat] AS varchar(50)) = CAST(t.[Treat] AS varchar(50)) AND tpc.[Year] = t.[Purchase Year]
UNION
SELECT CAST([Basket List] AS varchar(50)) AS [Basket List]
      ,[Purchase Year]
      ,CAST(b.[Stuffed Animal] AS varchar(50)) AS [Product]
	  ,bpc.[Price]
  FROM [training].[awc419_basket_contents] AS b
  INNER JOIN [training].[awc419_stuffed_bunny_pricelist] AS bpc ON CAST(bpc.[Stuffed Animal] AS varchar(50)) = CAST(b.[Stuffed Animal] AS varchar(50)) AND bpc.[Year] = b.[Purchase Year]
  )
,price_list_wide AS
(
  SELECT TOP(1000) *
  FROM (SELECT [Basket List], [Purchase Year], [Price] FROM price_list_long) AS price_list_long
  PIVOT 
  (
  SUM([Price]) 
  FOR [Purchase Year] IN ([2021],[2022],[2023])
  ) AS price_list_wide
  ORDER BY [Basket List]
  )
,
result_1 AS
(
SELECT *
,CAST(([2022]-[2021])/[2021]*100 AS decimal(19,2)) AS pct_increase_2021_to_2022
,CAST(([2023]-[2022])/[2022]*100 AS decimal(19,2)) AS pct_increase_2022_to_2023
FROM price_list_wide
)
,
result_2 AS
(
SELECT 
AVG([2021]) AS [avg_2021]
,AVG([2022]) AS [avg_2022]
,AVG([2023]) AS [avg_2023]
,AVG([pct_increase_2021_to_2022]) AS [avg_pct_increase_2021_to_2022]
,AVG([pct_increase_2022_to_2023]) AS [avg_pct_increase_2022_to_2023]
,AVG([2023])*(1+AVG([pct_increase_2022_to_2023])/100)*100 AS expected_2024_fundraising
FROM result_1
)

-- uncomment individually the below queries to see the results
SELECT *
FROM result_1;

--SELECT *
--FROM result_2;