Talk Fantasy Football

Code: Select all

-- Set User Variables
SET @c:=0;
SET @w:=0;
SET @d:=0;
SET @l:=0;
SET @u:=0;
-- Get highest streaks
SELECT	coach_id
	   ,coach
	   ,max(win_streak)											HIGHEST_WIN_STREAK
	   ,max(draw_streak)										HIGHEST_DRAW_STREAK
	   ,max(loss_streak)										HIGHEST_LOSS_STREAK
	   ,max(undefeated_streak)									HIGHEST_UNDEFEATED_STREAK
FROM
	-- *** STREAKS QUERY ***
	(
	-- Get results and streak counters
	SELECT	coach_id
		   ,coach
		   ,CASE WHEN coach_id = @c AND match_result = 'W'
					THEN @w:=@w+1
				 WHEN coach_id != @c AND match_result = 'W'
					THEN @w:=1
				 ELSE @w:=0	END									WIN_STREAK
		   ,CASE WHEN coach_id = @c AND match_result = 'D'
					THEN @d:=@d+1
				 WHEN coach_id != @c AND match_result = 'D'
					THEN @d:=1
				 ELSE @d:=0	END									DRAW_STREAK
		   ,CASE WHEN coach_id = @c AND match_result = 'L'
					THEN @l:=@l+1
				 WHEN coach_id != @c AND match_result = 'L'
					THEN @l:=1
				 ELSE @l:=0	END									LOSS_STREAK
		   ,CASE WHEN coach_id = @c AND match_result != 'L'
					THEN @u:=@u+1
				 WHEN coach_id != @c AND match_result != 'L'
					THEN @u:=1
				 ELSE @u:=0	END									UNDEFEATED_STREAK
		   ,@c:=coach_id
	FROM
		-- *** RESULTS QUERY ***
		(
		-- Team One
		SELECT	c.coach_id										COACH_ID
			   ,c.name											COACH
			   ,t.name											TEAM
			   ,m.match_id										MATCH_ID
			   ,m.date_played									MATCH_DATE
			   ,CASE WHEN team1_score > team2_score THEN 'W'
					 WHEN team1_score < team2_score THEN 'L'
					 ELSE 'D' END 								MATCH_RESULT
		FROM matches m
			JOIN teams t
				ON m.team1_id = t.team_id
			JOIN coaches c
				ON t.owned_by_coach_id = c.coach_id
		WHERE date_played IS NOT NULL

		UNION ALL
		-- Team Two
		SELECT	c.coach_id										COACH_ID
			   ,c.name											COACH
			   ,t.name											TEAM
			   ,m.match_id										MATCH_ID
			   ,m.date_played									MATCH_DATE
			   ,CASE WHEN team2_score > team1_score THEN 'W'
					 WHEN team2_score < team1_score THEN 'L'
					 ELSE 'D' END 								MATCH_RESULT
		FROM matches m
			JOIN teams t
				ON m.team2_id = t.team_id
			JOIN coaches c
				ON t.owned_by_coach_id = c.coach_id
		WHERE m.date_played IS NOT NULL
		ORDER BY coach_id ASC, match_date ASC
		) r
	) s
GROUP BY coach_id, coach
ORDER BY highest_win_streak DESC
		,highest_undefeated_streak DESC
		,highest_draw_streak DESC
		,highest_win_streak DESC