SQL Challenge: Cross-Country Scoring (Solution)

I introduced the challenge of Scoring a Cross Country match in a 2005 article.

This article provides some SQL Solutions to parts of the Challenge.

1) Matching Teams:

In a four-way match each combination of teams, each team is scored against the others:

Teams: Ephrata, Manheim Township, Conestoga Valley, McCaskey

There are 6 pairs: 3+2+1

  1. Ephrata vs Manheim Township
  2. Ephrata vs Conestoga Valley
  3. Ephrata vs McCaskey
  4. Manheim Township vs Conestoga Valley
  5. Manheim Township vs McCaskey
  6. Conestoga Valley vs McCaskey

2) How do we generate these team pairings with SQL?

/* Get Pairs of Teams */
select 
	t1.team_name as t1, 			
	t2.team_name as t2
from 
	teams as t1
	cross join	/* Cross join two teams: generates all possible combinations */
	teams as t2
where
        /* 'greater than' fix to make sure that teams don't race against themselves or their inverse */
	t1.team_name > t2.team_name	
order by
	t1.team_name, t2.team_name;

3) How to we generate each runner’s points?

select 
	rank() over (order by place) as points,	  
	team_pairs.team1, 
	team_pairs.team2, 
	race_results.runner_name,
	teams.team_name, 
	race_results.place, 
	race_results.time 
from 
  ..

4) Combine Together Using “With-Statement”

/* Add the Race Results to the Team Parings */
with team_pairs as (		
	/* Team Pairs */
	select 
		t1.id as id1,
		t1.team_name as team1, 
		t2.id as id2,
		t2.team_name as team2
	from 
		teams as t1 
		cross join						
		teams as t2
	where
		t1.team_name > t2.team_name			/* could also use teams.id but I didn't want you to think the ids are meaningful */
	order by
		t1.team_name, t2.team_name

)
select 
	rank() over (order by place) as points,		
	team_pairs.team1, 
	team_pairs.team2, 
	race_results.runner_name,
	teams.team_name, 
	race_results.place, 
	race_results.time 
from 
	team_pairs					   /* team_pairs is the 'named' variable from the above with statement */
	left outer join race_results 			   /* this could change to an'inner join' but I want to see all teams, evem if no runners */
	  on 	team_pairs.id1 = race_results.team_id or
	  		team_pairs.id2 = race_results.team_id
	inner join teams
	  on teams.id = race_results.team_id

/* 	There's a lot of data returned.  Let's focus in on one match: Ephrata vs Manheim Township for which we at least have 4 runners. 
	We can remove the filter once we have a single match worked out */
where
	(	team_pairs.team1 = 'Ephrata' and
		team_pairs.team2 = 'Manheim Township'
	)
	or 
	(	team_pairs.team2 = 'Ephrata' and
		team_pairs.team1 = 'Manheim Township'
	)

order by 
	race_results.place;


	/* 	We can add another with statement and sum up the points for each team, grouping by team1, and team2
		We also have to figure out how we're going to solve the 6th and 7th runner rule.
		I think we can do that with a where clause that limits the top 7 runners, 
		which may require a second rank column and an order by statement, but I'm fuzzy on what I did before
	*/

 

View More: