Upset Percentage

[Ether]

Originally Posted by AndyBare

Does anyone have a percentage of all elimination matches, within all divisions, and extending onto Einstein, that were upsets (lower seed beating higher seed)?

To answer that question definitively, perhaps a SQL guru will write a script to query Jaci's 2016 SQL database.

[markmcgary]

Originally Posted by Ether

To answer that question definitively, perhaps a SQL guru will write a script to query Jaci's 2016 SQL database.

That sounds like a challenge. According to my query, there were 720 upsets out of 2273 playoff matches. This does not count ties where the fouls determined the outcome. I checked this against Einstein and it looks accurate.

select matches.event, events.name, match_levels.name, matches.set_number, matches.match_number,
red_scores.total_points as red_points, blue_scores.total_points as blue_points from matches
inner join events on matches.event = events.id
inner join match_levels on matches.match_level = match_levels.id
inner join match_scores as red_scores on matches.id = red_scores.match
inner join match_scores as blue_scores on matches.id = blue_scores.match
where match_level > 1
and red_scores.alliance_color = "red"
and blue_scores.alliance_color = "blue"
and blue_points > red_points
and events.is_official = 1
order by matches.event

Edit: You can add one upset for Einstein Final Match 3 which I know was tied. That makes 31.7% playoff upsets.

Edit 2: I added some logic to figure out upsets that were tie scores. That added 7 total upsets. 32%. Interestingly, 4322 won an upset on a tie at OCR QF2-1. That match now shows in the results.

and (blue_points > red_points or (blue_points = red_points and blue_scores.foul_points > red_scores.foul_points))

[antman]

Originally Posted by markmcgary

and (blue_points > red_points or (blue_points = red_points and blue_scores.foul_points > red_scores.foul_points))

You are assuming the blue alliance is always the lower-seeded alliance. This isn't always the case. For example:

<http://www.thebluealliance.com/match/2016pncmp_f1m1>

we were on the red alliance even though we were the #5 alliance going against the #2 alliance.

[markmcgary]

Originally Posted by antman

You are assuming the blue alliance is always the lower-seeded alliance. This isn't always the case. For example:

<http://www.thebluealliance.com/match/2016pncmp_f1m1>

we were on the red alliance even though we were the #5 alliance going against the #2 alliance.

That could certainly impact the results. I thought that red was always the higher seeded alliance and alliances sometimes change bumper colors during playoffs to maintain that relationship. I'd have to study the database design more deeply to determine the relationships between the matches, alliances and their seeding. I took the easy way assuming (incorrectly?) that blue was always the lower seed. The SQL will get more ugly. I wonder how significant will be the impact on the percentage?

[Ether]

Originally Posted by markmcgary

I'd have to study the database design more deeply to determine the relationships between the matches, alliances and their seeding.

The "alliance_picks" table has the alliance seeding.

[markmcgary]

Originally Posted by Ether

The "alliance_picks" table has the alliance seeding.

Thank you. I'll correct the query. Can't abide bogus results.

[markmcgary]

Originally Posted by Ether

The "alliance_picks" table has the alliance seeding.

I found the seeding value in alliances.num.

This SQL may not be the most efficient, but it works:

select distinct events.name as Event, match_levels.name as Round, matches.set_number as Number, matches.match_number as Match,
red_alliance.num as red_seed, red_teams.team as red_captain, red_scores.total_points as red_points,
blue_alliance.num as blue_seed, blue_teams.team as blue_captain, blue_scores.total_points as blue_points from matches
inner join events on matches.event = events.id
inner join match_levels on matches.match_level = match_levels.id
inner join match_teams as red_teams on matches.id = red_teams.match
inner join match_teams as blue_teams on matches.id = blue_teams.match
inner join alliance_picks as red_alliance_picks on red_teams.team = red_alliance_picks.team
inner join alliance_picks as blue_alliance_picks on blue_teams.team = blue_alliance_picks.team
inner join alliances as red_alliance on events.id = red_alliance.event and red_alliance_picks.alliance = red_alliance.id
inner join alliances as blue_alliance on events.id = blue_alliance.event and blue_alliance_picks.alliance = blue_alliance.id
inner join match_scores as red_scores on matches.id = red_scores.match
inner join match_scores as blue_scores on matches.id = blue_scores.match
where match_level > 1
and red_alliance_picks.pick = 0
and blue_alliance_picks.pick = 0
and red_teams.alliance_color = "red"
and blue_teams.alliance_color = "blue"
and red_scores.alliance_color = "red"
and blue_scores.alliance_color = "blue"
and ((red_seed > blue_seed and (red_points > blue_points or (red_points = blue_points and red_scores.foul_points > blue_scores.foul_points)))
 or  (blue_seed > red_seed and blue_points > red_points or (blue_points = red_points and blue_scores.foul_points > red_scores.foul_points)))
and events.is_official = 1
order by events.name, match_levels.id, set_number, match_number

Results are:
712 of 2273 playoff matches were upsets. That's 31.3%
643 times Blue was the underdog and upset Red.
69 times Red was the underdog and upset Blue.

Whew.... I hope that's it. Are there any other quirks of the system that I have missed?

(Now, I have to get back to work!)

[EricH]

Originally Posted by markmcgary

That could certainly impact the results. I thought that red was always the higher seeded alliance and alliances sometimes change bumper colors during playoffs to maintain that relationship. I'd have to study the database design more deeply to determine the relationships between the matches, alliances and their seeding. I took the easy way assuming (incorrectly?) that blue was always the lower seed. The SQL will get more ugly. I wonder how significant will be the impact on the percentage?

Easy way to do it: Red is from the highest seed in that branch of the bracket (1-8/4-5|2-7/3-6), regardless of if that seed moves on. #8 has red over #2, should they meet in finals.

Thus, #1 is always red. #8 is blue, but can take over red if they beat #1. #2 is red, unless they're in finals; #7 is blue unless they beat #2. #3 is red against #6, and blue otherwise (#6 is always blue). #4 is red against #5, blue for semis, and red for finals (while #5 is blue unless they're in finals).


Or, to put it another way:
Alliance: QFs, SFs, Fs (assuming they make it that far)
#1: R, R, R
#2: R, R, B
#3: R, B, B
#4: R, B, R
#5: B, B, R
#6: B, B, B
#7: B, R, B
#8: B, R, R

Best reason to be on the #1 alliance, you don't change your bumper color. (OK, so #6 has the same perk...)

[CalTran]

Originally Posted by EricH

#6: B, B, B
Best reason to be on the #1 alliance, you don't change your bumper color. (OK, so #6 has the same perk...)

Some robots just look better in blue anyways.

[orangemoore]

Originally Posted by CalTran

Some robots just look better in blue anyways.

254.