Given a bunch of data on how many pins the players on each team knocked down each frame of each game, determine the wins and losses for all the teams in the league.

gistfile1.txt

select t.TeamName, ifnull(Wins, 0) Wins, ifnull(Total-Wins,0) Losses
from Team t
left join (
	select b.TeamID, count(*) Wins
	from (
		select a.GameID, a.TeamID, max(Points)
		from (
			select gf.GameID, gf.TeamID, sum(gf.FirstRoll + gf.SecondRoll) Points
			from GameFrame gf
			group by gf.GameID, gf.TeamID
			order by GameID, Points desc
		) a
		group by a.GameID
	) b
	group by b.TeamID
) c on c.TeamID = t.TeamID
left join (
	select TeamID, count(*)  Total
	from (
		select distinct GameID, TeamID
		from GameFrame
	) d
	group by TeamID
) e on e.TeamID = t.TeamID