GraphGists

FIS Alpine Skiing Seasons

Introduction

I was interested on playing with Neo4j with some real data and so I grabbed some from the FIS site and tried to play with it. The outcome is a graph that stores the results of alpine skiing races.

The idea is to have a graph that is easy to navigate: you can start from the Alpine skiing node, then go to Ladies, then to the current season and then to the first race, then to the next race, and so on. Every race has its type (Downhill, Slalom , Giant Slalom , Super G, Super Combined) and location. Athletes are connected to the races through their results. Seasons, races and results are linked lists.

2014 will be the year of the XXII winter olympic games in Sochi. Go Switzerland!

Graph model

2e2f2eb8 1c7b 4d0c acf1 0a0845993fa9 DyznPCNSY

The graph

Use cases

Current season ladies calendar

MATCH (g:Gender)-[:CURRENT_SEASON]->(s:Season)-[*]->(r:Race)
where g.name = 'Ladies'
match (r)-[:IS_A]->(t), (r)-[:IN]->(l)
return r.date as date, l.name as location, t.name as type

Ranking by race

MATCH (r:Race)
where r.date = '29/11/2013'
match (r)<-[time:FINISHED*]-(f)<-[:FINISHED_AT]-(racer)
with racer, f, time, reduce(totalDiff = 0, n IN time| totalDiff + n.diff) as diff
return size(time) as rank, racer.firstname+" "+racer.lastname as racer, f.time as time, "+"+diff as diff

Ladies overall leader board current season

MATCH (g:Gender)-[:CURRENT_SEASON]->(s:Season)-[*]->(r:Race)
Where g.name = 'Ladies'
with r
MATCH (n:Athlete)
MATCH p = allShortestPaths((n)-[:FINISHED_AT|FINISHED*]->(r))
WITH size(relationships(p))-1 as c, p
WITH CASE
WHEN c < 4
THEN 100-((c-1)*20)
WHEN c < 7
THEN 50-((c-4)*5)
WHEN c = 7
THEN 36
WHEN c < 11
THEN 32-((c-8)*3)
WHEN c < 16
THEN 24 -((c-11)*2)
WHEN c < 31
THEN 31-c
ELSE 0  END AS result, p
return distinct(head(nodes(p)).firstname+' '+head(nodes(p)).lastname) as racer , sum(result) as points order by points desc

Another way to calculate ranking points

Added a second graph with the ranking points. Every node is a ranking position, with its related points. Doing so, the association between rank and points is no more in the query, but in the database.

077f9503 12da 4b7b 9dfa 8e36e072323a JPV InysF

Ladies overall leader board current season using rankpoints graph

MATCH (g:Gender)-[:CURRENT_SEASON]->(s:Season)-[*]->(r:Race)
Where g.name = 'Ladies'
with r
MATCH (n:Athlete)
MATCH p = allShortestPaths((n)-[:FINISHED_AT|:FINISHED*]->(r))
WITH length(relationships(p))-1 as c, p
match (rp:Rankpoints)
where rp.label = c+''
return distinct(head(nodes(p)).firstname+' '+head(nodes(p)).lastname) as racer , sum(rp.points) as points  order by points desc

Ladies Downhill leader board current season

MATCH (g:Gender)-[:CURRENT_SEASON]->(s:Season)-[*]->(r:Race)
Where g.name = 'Ladies'
with r
MATCH (n:Athlete), r-[:IS_A]->(t:Type)
Where t.name = 'Downhill'
MATCH p = allShortestPaths((n)-[:FINISHED_AT|:FINISHED*]->(r))
WITH length(relationships(p))-1 as c, p
match (rp:Rankpoints)
where rp.label = c+''
return distinct(head(nodes(p)).firstname+' '+head(nodes(p)).lastname) as racer , sum(rp.points) as points  order by points desc

Ladies country leader board current season

MATCH (g:Gender)-[:CURRENT_SEASON]->(s:Season)-[*]->(r:Race)
Where g.name = 'Ladies'
with r
MATCH (n:Athlete)-[:RACES_FOR]->(country:Country)
MATCH p = allShortestPaths((n)-[:FINISHED_AT|:FINISHED*]->(r))
WITH length(relationships(p))-1 as c, p
match (rp:Rankpoints)
where rp.label = c+''
with head(nodes(p)) as e, rp
match e-[:RACES_FOR]->(d)
return distinct(d.name) as country , sum(rp.points) as points  order by points desc

Podiums by athlete ever

Podiums of Lara Gut ever

MATCH (n:Athlete),(r:Race)
WHERE n.lastname = 'Gut'
MATCH p = allShortestPaths((n)-[:FINISHED_AT|FINISHED*..4]->(r))
RETURN count(p) as Gut_podiums

Rank by athlete in all races on current season

Rank of Anna Fenninger in all races in current season

MATCH (g:Gender)-[:CURRENT_SEASON]->(s:Season)-[*]->(r:Race)
Where g.name = 'Ladies'
with r
MATCH (n:Athlete)
WHERE n.lastname = 'Fenninger'
MATCH p = allShortestPaths((n)-[:FINISHED_AT|FINISHED*]->(r))
MATCH r-[:IS_A]->(t), r-[:IN]->(c)
return r.date as date, t.name as type, c.name as location, length(relationships(p))-1 as rank

Average ranking by athlete

Average ranking of Anna Fenninger ever

MATCH (n:Athlete),(r:Race)
WHERE n.lastname = 'Fenninger'
MATCH p = allShortestPaths((n)-[:FINISHED_AT|FINISHED*]->(r))
MATCH r-[:IS_A]->(t), r-[:IN]->(c)
return avg(length(relationships(p))-1) as avg_rank, "over "+count(p)+" races" as races

Podiums by country ever

Podiums of Austria ever

MATCH (n:Country)
WHERE n.name = 'AUT'
MATCH (a:Athlete)-[:RACES_FOR]->(n),(r:Race)
MATCH p = allShortestPaths((a)-[:FINISHED_AT|FINISHED*..4]->(r))
RETURN count(p) as AUT_podiums

How close was a victory

How close to first place was a second place

MATCH (n:Athlete),(r:Race)
MATCH p = allShortestPaths((n)-[:FINISHED_AT|FINISHED*..3]->(r))
with filter(x IN relationships(p) WHERE (x.diff < 200 and x.diff <> 0)) as filtered
with filtered[0] as m
match (racer)-[:FINISHED_AT]->()-[m]->()-[:FINISHED]->(r), r-[:IN]->(l), r-[:IS_A]->(t)
return racer.firstname+' '+racer.lastname as racer, r.date as date,l.name as location, t.name as type,"+"+m.diff as diff