Jaci's Annual FRC Datadump 2016

[markmcgary]

Originally Posted by Jaci

Welcome to the inaugural release of my FRC datadumps

Or something a little more advanced... every Alliance Selection where the Captain's OPR is less than the 1st Pick's OPR...

SELECT events.short_name, alliances.num, teams.id FROM alliances
INNER JOIN alliance_picks ON alliances.id == alliance_picks.alliance        # Join the alliance picks for this alliance
INNER JOIN events ON events.id == alliances.event                           # Join the event for this alliance
INNER JOIN teams ON alliance_picks.team == teams.id                         # Join the teams for the picks
INNER JOIN stats ON stats.team == teams.id                                  # Join the statistics (opr) for the teams
WHERE alliance_picks.pick == 0                                              # Select the Captain team
AND stats.event == events.id                                                # Make sure the event we're checking OPRs is the same event we're checking alliances on
AND
(SELECT stats.opr FROM alliance_picks                                       # Select the OPR stat
INNER JOIN stats ON stats.team == alliance_picks.team                       # Join the stats to the alliance picks
WHERE alliance_picks.alliance == alliances.id                               # Make sure the alliance we're checking is the same alliance the captain is from
AND alliance_picks.pick == 1)                                               # Make sure we're checking the 1st Pick. At this point, the OPR stat is selected
> stats.opr                                                                 # Check the 1st picks OPR from the previous select against the captains

All these can be seen in the examples of the ZIP file.

Thank you for this. Really great work. Your 'more advanced' example intrigued me because I know our team was a 1st pick for alliance 5 at the OCR (CAPL) and we had a higher OPR. When I ran your query, we did not appear in the results. I did a little testing and found an improvement to the SQL.

SELECT events.short_name, alliances.num, teams.id, stats.opr FROM alliances
INNER JOIN alliance_picks ON alliances.id == alliance_picks.alliance
INNER JOIN events ON events.id == alliances.event
INNER JOIN teams ON alliance_picks.team == teams.id 
INNER JOIN stats ON stats.team == teams.id
WHERE alliance_picks.pick == 0 
AND alliances.event == stats.event
AND
(SELECT stats.opr FROM alliance_picks
INNER JOIN stats ON stats.team == alliance_picks.team
WHERE alliance_picks.alliance == alliances.id
AND stats.event = alliances.event
AND alliance_picks.pick == 1)
> stats.opr
ORDER BY events.short_name, alliances.num

Now, we appear in the results as expected!

Thanks again for putting this together.