Jaci's Annual FRC Datadump 2016

[antman]

Thanks for making all this data easily accessible. So here's something my team was casually tracking throughout the season: the 100% breach club. Here's the the sql for one way to get the answer:

select substr(teams.key, 4) as team, match_counts.match_count
from teams
inner join
(select match_teams.team, count(match_teams.id) as match_count 
from match_teams 
group by match_teams.team) as match_counts 
on substr(teams.key, 4) = match_counts.team
inner join
(select match_teams.team, sum(match_scores.defenses_breached) as breach_count 
from match_teams inner join match_scores on match_teams.match = match_scores.match and match_teams.alliance_color = match_scores.alliance_color
group by match_teams.team) as breach_counts
on substr(teams.key, 4) = breach_counts.team
where match_counts.match_count = breach_counts.breach_count
order by cast(substr(teams.key, 4) as int)

and here's the result:

Team Match Count
101 10
159 11
525 45
558 68
1014 30
1569 17
1750 11
1986 51
2062 28
2354 11
2767 63
2855 11
2882 10
3225 16
3238 65
3310 46
3488 10
3546 65
3683 50
3695 10
4476 12
4931 28
5254 45
5271 10
5618 27
5805 21
5905 11
5951 14