-
Notifications
You must be signed in to change notification settings - Fork 3
/
wwc2019.neo4j-browser-guide
183 lines (137 loc) · 5.5 KB
/
wwc2019.neo4j-browser-guide
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
= Women's World Cup Sandbox
:img: https://guides.neo4j.com/sandbox/3.4/img
== Welcome to the World Cup Sandbox
It's Women's World Cup 2019 and we've created a special sandbox for you to play around with while you follow the tournament.
== Schema
We've imported all the matches, squads, lineups, and scorers from all the World Cups between 1991 and 2019.
Let's take a look at the schema.
Run the following query:
[source,cypher]
----
call db.schema.visualization()
----
== Who's playing in World Cup 2019?
We'll start with a basic query to find out which teams are playing in World Cup 2019:
[source, cypher]
----
MATCH path = (t:Tournament {year: 2019})<-[:PARTICIPATED_IN]-(team)
RETURN path
----
We can see a lot of familiar names there, but how many teams are actually participating?
== How many teams are playing in World Cup 2019?
We can write the following query to find the number of teams that have competed in each of the World Cups:
[source, cypher]
----
MATCH (t:Tournament)<-[:PARTICIPATED_IN]-(team)
RETURN t.name, t.year, count(*)
ORDER BY t.year
----
Since 2015, 24 teams have participated, which is double the number of teams that participated in the first World Cup in 1991.
== First time participants
Do we have any first time participants?
We can find out by executing the following query:
[source, cypher]
----
MATCH (t:Tournament {year: 2019})<-[:PARTICIPATED_IN]-(team)
WITH team, [(team)-[:PARTICIPATED_IN]->(other) WHERE other.year < 2019 | other] AS otherTournaments
WHERE size(otherTournaments) = 0
RETURN team.name
----
Just the 4 first time participants.
== How many World Cups have the other teams participated in?
And what about everybody else?
The following query shows us how many other tournaments that each team has participated in:
[source, cypher]
----
MATCH (t:Tournament {year: 2019})<-[:PARTICIPATED_IN]-(team)
WITH team, [(team)-[:PARTICIPATED_IN]->(other) WHERE other.year < 2019 | other] AS otherTournaments
RETURN team.name, size(otherTournaments) AS tournaments
ORDER BY tournaments DESC
----
== Who won the previous World Cups?
If we want to find the winners of the previous World Cups, we can run the following query:
[source, cypher]
----
MATCH (t1:Team)-[p1:PLAYED_IN]-(m:Match)<-[p2:PLAYED_IN]-(t2:Team),
(m)-[:IN_TOURNAMENT]->(tourn)
WHERE id(t1) < id(t2) AND m.stage = "Final"
RETURN tourn.name AS name, tourn.year AS year,
t1.name AS team1, t2.name AS team2,
CASE WHEN p1.score = p2.score
THEN p1.score + "-" + p2.score + " (" +
p1.penaltyScore + "-" + p2.penaltyScore + ")"
ELSE p1.score + "-" + p2.score
END AS result,
(CASE WHEN p1.score > p2.score THEN t1
WHEN p2.score > p1.score THEN t2
ELSE
CASE WHEN p1.penaltyScore > p2.penaltyScore THEN t1
ELSE t2 END END).name AS winner
ORDER BY tourn.year
----
USA have won it 3 times, Germany twice, while Norway and Japan have a single win each.
Will we get a new winner this time around?
== Who's the top scorer across all World Cups?
One of the most commonly mentioned statistics by football commentators is the top scorer across all World Cups.
We can run the following query to find the answer:
[source, cypher]
----
MATCH (p:Person)-[:SCORED_GOAL]->(match)-[:IN_TOURNAMENT]->(tourn),
(p)-[:REPRESENTS]->(team)
RETURN p.name, team.name AS team, count(*) AS goals,
apoc.coll.sort(collect(DISTINCT tourn.year)) AS years
ORDER BY goals DESC
LIMIT 10
----
It'll be interesting to see if any of the players in France can close the gap to those at the top.
Having said that, some of these players scored in the 2015 World Cup, so perhaps they're also playing this year and will extend their lead.
== Which top scorers are playing in 2019?
Who are the top scoring players participating in this year's World Cup?
[source, cypher]
----
MATCH (p:Person)-[:SCORED_GOAL]->(match)-[:IN_TOURNAMENT]->(tourn),
(p)-[:REPRESENTS]->(team)
WITH p, team, count(*) AS goals,
apoc.coll.sort(collect(DISTINCT tourn.year)) AS years
WHERE (p)-[:IN_SQUAD]->()-[:FOR]->(:Tournament {year: 2019})
RETURN p.name, team.name AS team, goals
ORDER BY goals DESC
LIMIT 10
----
Marta is playing in her 5th World Cup, but has anyone else been named in more World Cup Squads?
== Multiple World Cups?
How many players have been named in 5 or more World Cup squads?
Have a guess before you run the next query:
[source, cypher]
----
MATCH (player:Person)-[:IN_SQUAD]->(s)<-[:NAMED]-(team), (s)-[:FOR]->(tourn)
WITH player, team, count(*) AS squads, apoc.coll.sort(collect(tourn.year)) AS years
WHERE size(years) >= 5
RETURN player.name AS player, team.name AS team, squads, years
ORDER BY squads DESC
----
Formiga is playing in her 7th World Cup!
== Goalscoring substitutes
Some players never get a start but have a big impact when they come off the bench.
The following query returns goal scoring substitutes:
[source, cypher]
----
MATCH (p:Person)-[:SCORED_GOAL]->(match)<-[:PLAYED_IN {type: "Subbed On"}]-(p)
WITH p, count(*) AS goals
MATCH (p)-[:REPRESENTS]-(team)
RETURN p.name, team.name, goals
ORDER BY goals DESC
LIMIT 10
----
== Top scorer by country
Do you know who your country's top World Cup scorer is?
[source, cypher]
----
MATCH (p:Person)-[:SCORED_GOAL]->(match)-[:IN_TOURNAMENT]->(tourn),
(p)-[:REPRESENTS]->(team)
WITH team, p, count(*) AS goals
ORDER BY team, goals DESC
WITH team, collect({player: p, goals: goals}) AS topScorers
RETURN team.name AS team, topScorers[0].player.name AS player, topScorers[0].goals AS goals
ORDER BY team
----