[Jaci]

Originally Posted by 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)

Just for reference: instead of doing a substr on teams.key, teams.id is the team number itself. We use the Team Number as the Primary Key since they're unique per team.