/********************************** Alteryx Weekly Challenge 409 ************************************/
/*
The below script works with pre-condition of creation the database tables with schema below:
[training].[die_1]
[training].[die_2]
[training].[die_3]
[training].[wr_mapping]
[training].[player_data]
These were all Alteryx inputs.
*/
-- Create table with all roll combinations
WITH
roll_combinations AS 
(
SELECT [die_1].[Die1], [die_2].[die2], [die_3].[die3]
,([die_1].[Die1]+ [die_2].[die2]+ [die_3].[die3]) AS [Roll]
,ROW_NUMBER() OVER(ORDER BY Die1) AS row_num
FROM [training].[die_1]
CROSS JOIN [training].[die_2]
CROSS JOIN [training].[die_3])
,
-- Create table with random number selection used for selection of die roll
randon_number AS
(
SELECT CEILING(RAND()*100) AS rand_num)
,
-- Roll 3 dies and get result
roll_selection AS
(
SELECT roll_combinations.*
FROM randon_number
INNER JOIN roll_combinations ON randon_number.rand_num = roll_combinations.row_num)
,
-- Find multiplier based on die roll
random_wr_mapping AS
(
SELECT [training].[wr_mapping].* 
FROM [training].[wr_mapping]
INNER JOIN roll_selection ON roll_selection.Roll = [training].[wr_mapping].[Roll])
,
-- Find team 1
top_1_team_selected AS
(
SELECT TOP (1) [Team]
FROM [training].[player_data]
GROUP BY [Team]
ORDER BY [Team])
,
-- Find team 2
top_2_team_selected AS
(
SELECT [Team]
FROM [training].[player_data]
GROUP BY [Team]
ORDER BY [Team]
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY)
,
-- Find best WR from team 1
top_wr_team_1 AS
(
SELECT TOP 1 [training].[player_data].*
,(([Catching]+[Catch In Traffic]+[Spec Catch])/3) AS [Matchup_position]
FROM [training].[player_data]
INNER JOIN top_1_team_selected ON [training].[player_data].[Team] = top_1_team_selected.[Team]
WHERE [Position] = 'WR'
ORDER BY [Overall] DESC)
,
-- Find best QB from team 1
top_qb_team_1 AS
(SELECT TOP 1 [training].[player_data].*
,(([Throw Power]+[Throw Acc Mid]+[Throw Under Pressure]+[Throw On The Run])/4) AS [Matchup_position]
FROM [training].[player_data]
INNER JOIN top_1_team_selected ON [training].[player_data].[Team] = top_1_team_selected.[Team]
WHERE [Position] = 'QB'
ORDER BY [Overall] DESC)
,
-- Find best CB from team 2
top_cb_team_2 AS
(SELECT TOP 1 [training].[player_data].*
,(([Pursuit]+[Man Coverage]+[Zone Coverage]+[Press]+[Play Recognition])/5) AS [Matchup_position]
FROM [training].[player_data]
INNER JOIN top_2_team_selected ON [training].[player_data].[Team] = top_2_team_selected.[Team]
WHERE [Position] = 'CB'
ORDER BY [Overall] DESC)
,
-- Combine all 3 players to one table
combined_players_pick AS
(
SELECT TOP 1 [Team], [Position], [First Name], [Last Name], [Matchup_position]
FROM top_wr_team_1
UNION
SELECT TOP 1 [Team], [Position], [First Name], [Last Name], [Matchup_position]
FROM top_qb_team_1
UNION
SELECT TOP 1 [Team], [Position], [First Name], [Last Name], [Matchup_position]
FROM top_cb_team_2
ORDER BY [Team])
,
-- Calculate modifier for WR and CB and add modifier based on die roll
added_modifier AS
(
SELECT 
combined_players_pick.[Team]
,combined_players_pick.[Position]
,combined_players_pick.[First Name]
,combined_players_pick.[Last Name]
,combined_players_pick.Matchup_position
,[Matchup_position]-LAG([Matchup_position],1) OVER(ORDER BY [Team]) AS [Modifier]
,random_wr_mapping.[Multiplier]
,random_wr_mapping.[Outcome1]
,random_wr_mapping.[Outcome2]
FROM combined_players_pick
CROSS JOIN random_wr_mapping)
,
-- Calculate Score
added_score AS
(
SELECT *
,[Modifier]*[Multiplier] AS [Score]
FROM added_modifier)
,
 -- RESULT presented in the 3rd row column [Result] based on Score comparison of WR and CB
result AS
(
SELECT *
,CASE
	WHEN [Score]<LAG([Score],1) OVER(ORDER BY [Team]) 
	THEN [Outcome2]
	ELSE [Outcome1]
	END AS [Result]
FROM added_score)
,
-- Separate the final result in line with CB as position
final_result AS
(
SELECT [Result] AS [Final_result]
FROM result
WHERE [Position] = 'CB')
-- Present final result in the main table by cross joining the final_result in 
SELECT *
FROM result
CROSS JOIN final_result