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
- Ephrata vs Manheim Township
- Ephrata vs Conestoga Valley
- Ephrata vs McCaskey
- Manheim Township vs Conestoga Valley
- Manheim Township vs McCaskey
- 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:
