Blog Post

Random SQL Review: FHS Supplemental Draft Eligibility

Random SQL Review: FHS Supplemental Draft Eligibility

Over on FantasyHockeySim.com, specifically with the National Simulated Hockey League, we have the concept of a Supplemental Draft. This is a player draft that gives access to veteran players for teams who have historically not performed well.

How do we define “not performed well” though? From the league rulebook: “Teams with a regular season winning percentage over the previous three (3) seasons below .500, who also did not qualify for the postseason in either of the previous two (2) seasons…”

When we first started using the Supplemental Draft, I was manually checking the standings and determining which teams were eligible. That got old quick though, so I put together the following query to figure it out for me:

SELECT
	team_name,
	eligible_teams.*
FROM franchise
	INNER JOIN team_name ON franchise.team_name_id = team_name.team_name_id
	INNER JOIN (
		SELECT
			franchise_id,
			CONCAT_WS('-', SUM(team_w), SUM(team_l), SUM(team_t)) team_record,
			(SUM(team_w) + (SUM(team_t) / 2)) / SUM(team_gp) team_pct
		FROM stat_team
			INNER JOIN (
				SELECT schedule_id
				FROM season
					INNER JOIN schedule ON season.league_id = schedule.league_id and season.season_id = schedule.season_id
					INNER JOIN config_schedule_type ON schedule.schedule_type_id = config_schedule_type.schedule_type_id
				WHERE season.league_id = :league_id AND schedule_type_exhibition = 0 AND schedule_type_playoff = 0
				ORDER BY season_priority DESC, schedule_priority DESC
				LIMIT 3
			) allowed_schedules ON stat_team.schedule_id = allowed_schedules.schedule_id
			INNER JOIN season_team ON stat_team.team_id = season_team.team_id
		WHERE team_farm = 0
		GROUP BY franchise_id
		HAVING (SUM(team_w) + (SUM(team_t) / 2)) / SUM(team_gp) < .5 ) eligible_teams ON franchise.franchise_id = eligible_teams.franchise_id LEFT JOIN ( SELECT franchise_id FROM stat_team INNER JOIN ( SELECT schedule_id FROM season INNER JOIN schedule ON season.league_id = schedule.league_id and season.season_id = schedule.season_id INNER JOIN config_schedule_type ON schedule.schedule_type_id = config_schedule_type.schedule_type_id WHERE season.league_id = :league_id AND schedule_type_exhibition = 0 AND schedule_type_playoff = 1 ORDER BY season_priority DESC, schedule_priority DESC LIMIT 2 ) allowed_schedules ON stat_team.schedule_id = allowed_schedules.schedule_id INNER JOIN season_team ON stat_team.team_id = season_team.team_id WHERE team_farm = 0 GROUP BY franchise_id HAVING SUM(team_gp) > 0
	) ineligible_teams ON franchise.franchise_id = ineligible_teams.franchise_id
WHERE ineligible_teams.franchise_id IS NULL
ORDER BY team_pct ASC;

There are two parts to what we do here and that makes sense given that there are two parts to the rule determining eligibility. First we find all off the teams that had a sub-.500 winning percentage over the last three seasons (the slightly-misnamed “eligible_teams” subquery above), then we find all of the teams that made the playoffs over the last two seasons (the “ineligible_teams” subquery), then we exclude the latter from the former to get our list of teams.

I’m going to work my way backwards a little bit. How do we determine if a team has made the playoffs in the last two seasons? Well, first we figure out what the last two playoff schedules were.

SELECT schedule_id
FROM season
	INNER JOIN schedule ON season.league_id = schedule.league_id and season.season_id = schedule.season_id
	INNER JOIN config_schedule_type ON schedule.schedule_type_id = config_schedule_type.schedule_type_id
WHERE season.league_id = :league_id AND schedule_type_exhibition = 0 AND schedule_type_playoff = 1
ORDER BY season_priority DESC, schedule_priority DESC
LIMIT 2

I’ve written before about how each season has a season_priority that determines what order it happens in. It is made up of one or more schedules of games. Those schedules each have a schedule_priority as well. A schedule can be defined as schedule_type_exhibition or schedule_type_playoff.

The schedule type was previously an enum and maybe it still should be. FHS supports the concept of a preseason tournament. So the schedule for a preseason tournament would be both exhibition and playoff. A standard preseason schedule would be exhibition but not playoff. The regular season would be neither exhibition nor playoff and the postseason would not be exhibition but would be playoff.

So this gets us the schedule_id for the two most recent playoff (but not exhibition) schedules. We use that to get each franchise_id that represents team that appeared in games from those schedules.

SELECT franchise_id
FROM stat_team
	INNER JOIN (
		SELECT schedule_id
		FROM season
			INNER JOIN schedule ON season.league_id = schedule.league_id and season.season_id = schedule.season_id
			INNER JOIN config_schedule_type ON schedule.schedule_type_id = config_schedule_type.schedule_type_id
		WHERE season.league_id = :league_id AND schedule_type_exhibition = 0 AND schedule_type_playoff = 1
		ORDER BY season_priority DESC, schedule_priority DESC
		LIMIT 2
	) allowed_schedules ON stat_team.schedule_id = allowed_schedules.schedule_id
	INNER JOIN season_team ON stat_team.team_id = season_team.team_id
WHERE team_farm = 0
GROUP BY franchise_id
HAVING SUM(team_gp) > 0

Something else I’ve mentioned previously is that – in the context of FHS – a “team” is a season-specific instance of a “franchise.” With that in mind, we get the team stats, join onto our previously-discussed schedule_id set (aliased as allowed_schedules) so that we’re only getting stats from the proper schedules, and then join to the season_team table because we can get our franchise_id from there. We explicitly filter out any cases where the stats are for a farm team (that should never be the case right now but it’s a little bit of future-proofing), group by franchise_id, and then filter again for any franchises that have any games played over that set of stats. This gets us our playoff teams for the past two seasons, which we know are ineligible for the Supplemental Draft.

Eligible teams have to have a winning percentage under .500 for the last three regular seasons so we start with a familiar-looking subquery over there.

SELECT schedule_id
FROM season
	INNER JOIN schedule ON season.league_id = schedule.league_id and season.season_id = schedule.season_id
	INNER JOIN config_schedule_type ON schedule.schedule_type_id = config_schedule_type.schedule_type_id
WHERE season.league_id = :league_id AND schedule_type_exhibition = 0 AND schedule_type_playoff = 0
ORDER BY season_priority DESC, schedule_priority DESC
LIMIT 3

Once again we’re getting a schedule_id set but this time we’re specifying that the games were neither exhibition nor playoff (thus being regular season games) and now we want the last three seasons.

SELECT
	franchise_id,
	CONCAT_WS('-', SUM(team_w), SUM(team_l), SUM(team_t)) team_record,
	(SUM(team_w) + (SUM(team_t) / 2)) / SUM(team_gp) team_pct
FROM stat_team
	INNER JOIN (
		SELECT schedule_id
		FROM season
			INNER JOIN schedule ON season.league_id = schedule.league_id and season.season_id = schedule.season_id
			INNER JOIN config_schedule_type ON schedule.schedule_type_id = config_schedule_type.schedule_type_id
		WHERE season.league_id = :league_id AND schedule_type_exhibition = 0 AND schedule_type_playoff = 0
		ORDER BY season_priority DESC, schedule_priority DESC
		LIMIT 3
	) allowed_schedules ON stat_team.schedule_id = allowed_schedules.schedule_id
	INNER JOIN season_team ON stat_team.team_id = season_team.team_id
WHERE team_farm = 0
GROUP BY franchise_id
HAVING (SUM(team_w) + (SUM(team_t) / 2)) / SUM(team_gp) < .5

Once again we start with the stat_team table, join onto our allowed_schedules alias, and join to season_team. We also, again, filter out farm teams and group by franchise_id. This time we determine the winning percentage by adding the sum of the team’s wins to half of the sum of their ties (a tie being worth half a win) and divide that by the sum of the number of games played. If that’s less than .5, they’re eligible.

With those two subqueries out of the way, we’re ready to pull it all together.

We start with our franchise table so that we can join onto the team_name table and get the current name of each franchise. Then we join to our eligible_teams alias and left join to our ineligible_teams alias. We don’t want teams in the ineligible list, so we filter out any cases where a franchise_id does appear in that set. Finally, we sort by the winning percentage determined in the eligible_teams subquery.

This give me the current name, record over the last three seasons, and winning percentage over the last three seasons of each team eligible for the Supplemental Draft.

Some of that is just to make the list pretty for me. I only need the franchise names, I don’t actually need the record or winning percentage, especially if I’m trusting this query to do the work for me. It’s nice to be able to show if anyone asks, though.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.