{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n",
    "SPDX-License-Identifier: Apache-2.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# English Premier League Teams and Stadiums\n",
    "This notebook uses a property graph containing the teams that took part in the 2019/20 English Premier League season. The graph includes information about the teams, their stadiums and the cities where they play. This notebook is based on the EPL-Gremlin notebook but contains some differences. The queries provided are expressed using openCypher."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A note on basic terminology\n",
    "\n",
    "There are a few commonly used terms used when working with graph databases. The three main ones being node or vertex, edge or relationship, and property. Different communites tend to have some subset of these they use more often. The Apache TinkerPop Gremlin documentation for example uses the terms Vertex and Edge exclusively whereas users of openCypher more commonly use Node and Relationship. The examples presented in this notebook follow those same conventions.  One other area where the terminology differs is that edge labels in Gremlin are commonly referred to as a type of relationship in openCypher."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Seeing a visual representation of your query\n",
    "\n",
    "The results of any openCypher query that returns a path (`MATCH p= ... RETURN p`) or simple list of nodes can be displayed visually. When such queries are run you will see a `Graph` tab in the query results area alongside the `Console` tab. Specific visualization features will be explained  using examples throughout this notebook. \n",
    "\n",
    "\n",
    "### Query visualization hints\n",
    "\n",
    "Settings can be used to give the visual renderer hints as to how to label the nodes drawn.\n",
    "\n",
    "You can specify query visualization hints using  `-d`, `-de`, `-l` and `-g` after the `%%oc` or `opencypher` cell \"magics\". Either one of them can be used to run an openCypher query. The syntax in general is:\n",
    "\n",
    "`%%opencypher -d $my_vertex_mapping -de $my_edge_mapping -l <max label length> -g <property to group by>`\n",
    "\n",
    "All of these settings are optional. By default nodes in the visualization display their label text. The parameter `$my_mapping` refers to a Python variable that you create in a different cell that provides a mapping from a node's label to the name of a property you prefer be used to label nodes in the visualization. For example\n",
    "\n",
    "`my_vertex_mapping = '{\"Team\":\"name\",\"Stadium\":\"code\"}`\n",
    "\n",
    "By default labels shown in the visualization are truncated at 10 characters and the last 3 of the ten are replaced by an ellipsis. You can override this setting using the `-l` setting. For example:\n",
    "\n",
    "`%%opencypher -l 20`\n",
    "\n",
    "Nodes in the visualization are colored (grouped) using their labels by default. You can change this using the `-g` option. For example, to group nodes by the `founded` property:\n",
    "\n",
    "`%%opencypher -g founded`\n",
    "\n",
    "Grouping can be completely disabled using the `--ignore-groups` option.\n",
    "\n",
    "### Adjusting the visualization layout and other settings\n",
    "You can further adjust many of the visualization settings using the two commands\n",
    "\n",
    "- `%graph_notebook_vis_options`\n",
    "- `%%graph_notebook_vis_options`\n",
    "\n",
    "## Getting help\n",
    "You can get help at any time using the `--help` option as follows (the cell body needs at least one character in it for `--help` to work). \n",
    "\n",
    "`%%opencypher --help`\n",
    "\n",
    "Several of other the magic commands will accept `--help`. Each of the options described above also has a long form. For example `-d` can also be specified using `--display-options`. Run the cell below to see the full help text for the `%%opencypher` command.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%opencypher --help\n",
    "x"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the status of our connection\n",
    "The three cells below can be used to check the version of the workbench, the current configuration, and the status of the Neptune cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%graph_notebook_version"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using a different endpoint\n",
    "You can change the endpoint that this notebook is connected to at any time using the `%graph_notebook_host` or `%%graph_notebook_config` commands. Edit and run the cell below whenever you need to change the endpoint you are connected to."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%graph_notebook_host your-hostname-here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%graph_notebook_config"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Neptune endpoint status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%status"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the graph\n",
    "The cell below creates the EPL 2019.20 property graph. A set of queries that can be run against the data follows in the subsequent cells. A slightly different version of this graph can also be loaded via the `%seed` command. The main difference being that graph is created using Gremlin steps which allocate specific ID values to every vertex and some types and labels are in lower case. When working with openCypher, explicitly accessing a vertex by its ID value is a less used pattern and user defined ID values are not supported when creating nodes and relationships."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "CREATE (epl:League { name:\"English Premier League\",nickname: \"EPL\"})\n",
    "CREATE (arsenal:Team {name:\"Arsenal\", fullName:\"Arsenal F. C.\", nickname:\"The Gunners\", founded:1886})\n",
    "CREATE (chelsea:Team {name:\"Chelsea\", fullName:\"Chelsea F.C.\", nickname:\"The Blues\", founded:1905})\n",
    "CREATE (tottenhamhotspur:Team {name:\"Tottenham Hotspur\", fullName:\"Tottenham Hotspur F.C.\", nickname:\"Spurs\", founded:1882})\n",
    "CREATE (westhamunited:Team {name:\"West Ham United\", fullName:\"West Ham United F.C.\", nickname:\"The Hammers\", founded:1895})\n",
    "CREATE (liverpool:Team {name:\"Liverpool\", fullName:\"Liverpool F.C.\", nickname:\"The Reds\", founded:1892})\n",
    "CREATE (everton:Team {name:\"Everton\", fullName:\"Everton F.C.\", nickname:\"The Toffees\", founded:1878})\n",
    "CREATE (manchesterunited:Team {name:\"Manchester United\", fullName:\"Manchester United F.C.\", nickname:\"The Red Devils\", founded:1878})\n",
    "CREATE (manchestercity:Team {name:\"Manchester City\", fullName:\"Manchester City F.C\", nickname:\"The Citizens\", founded:1880})\n",
    "CREATE (wolverhamptonwanderers:Team {name:\"Wolverhampton Wanderers\", fullName:\"Wolverhampton Wanderers F.C\", nickname:\"Wolves\", founded:1877})\n",
    "CREATE (burnley:Team {name:\"Burnley\", fullName:\"Burnley F.C\", nickname:\"The Clarets\", founded:1882})\n",
    "CREATE (sheffieldunited:Team {name:\"Sheffield United\", fullName:\"Sheffield United F.C\", nickname:\"The Blades\", founded:1889})\n",
    "CREATE (crystalpalace:Team {name:\"Crystal Palace\", fullName:\"Crystal Palace F.C\", nickname:\"Eagles\", founded:1905})\n",
    "CREATE (southampton:Team {name:\"Southampton\", fullName:\"Southampton F.C\", nickname:\"The Saints\", founded:1885})\n",
    "CREATE (newcastleunited:Team {name:\"Newcastle United\", fullName:\"Newcastle United F.C\", nickname:\"The Magpies\", founded:1892})\n",
    "CREATE (brightonandhovealbion:Team {name:\"Brighton and Hove Albion\", fullName:\"Brighton and Hove Albion F.C\", nickname:\"Seagulls\", founded:1901})\n",
    "CREATE (watford:Team {name:\"Watford\", fullName:\"Watford F.C.\", nickname:\"Hornets\", founded:1898})\n",
    "CREATE (bournemouth:Team {name:\"Bournemouth\", fullName:\"Bournemouth F.C.\", nickname:\"The Cherries\", founded:1899})\n",
    "CREATE (astonvilla:Team {name:\"Aston Villa\", fullName:\"Aston Villa F.C.\", nickname:\"The Villans\", founded:1897})\n",
    "CREATE (leicestercity:Team {name:\"Leicester City\", fullName:\"Leicester City F.C.\", nickname:\"The Foxes\", founded:1884})\n",
    "CREATE (norwichcity:Team {name:\"Norwich City\", fullName:\"Norwich City F.C.\", nickname:\"The Canaries\", founded:1902})\n",
    "CREATE (theemirates:Stadium {name:\"The Emirates\", opened:2006, capacity:60704})\n",
    "CREATE (stamfordbridge:Stadium {name:\"Stamford Bridge\", opened:1877, capacity:40834})\n",
    "CREATE (tottenhamhotspurstadium:Stadium {name:\"Tottenham Hotspur Stadium\", opened:2019, capacity:62214})\n",
    "CREATE (londonstadium:Stadium {name:\"London Stadium\", opened:2016, capacity:60000})\n",
    "CREATE (anfield:Stadium {name:\"Anfield\", opened:1884, capacity:53394})\n",
    "CREATE (goodisonpark:Stadium {name:\"Goodison Park\", opened:1892, capacity:39414})\n",
    "CREATE (oldtrafford:Stadium {name:\"Old Trafford\", opened:1910, capacity:75643})\n",
    "CREATE (etihadstadium:Stadium {name:\"Etihad Stadium\", opened:2003, capacity:55107})\n",
    "CREATE (molineuxstadium:Stadium {name:\"Molineux Stadium\", opened:1889, capacity:32050})\n",
    "CREATE (turfmoor:Stadium {name:\"Turf Moor\", opened:1883, capacity:21944})\n",
    "CREATE (bramalllane:Stadium {name:\"Bramall Lane\", opened:1855, capacity:32125})\n",
    "CREATE (selhurstparkstadium:Stadium {name:\"Selhurst Park Stadium\", opened:1924, capacity:25486})\n",
    "CREATE (stmarysstadium:Stadium {name:\"St. Mary's Stadium\", opened:2001, capacity:32384})\n",
    "CREATE (stjamespark:Stadium {name:\"St. James' Park\", opened:1880, capacity:52305})\n",
    "CREATE (americanexpresscommunitystadium:Stadium {name:\"American Express Community Stadium\", opened:2011, capacity:30750})\n",
    "CREATE (vicarageroad:Stadium {name:\"Vicarage Road\", opened:1922, capacity:22200})\n",
    "CREATE (vitalitystadium:Stadium {name:\"Vitality Stadium\", opened:1910, capacity:11364})\n",
    "CREATE (villapark:Stadium {name:\"Villa Park\", opened:1897, capacity:42095})\n",
    "CREATE (kingpowerstadium:Stadium {name:\"King Power Stadium\", opened:2002, capacity:32261})\n",
    "CREATE (carrowroadstadium:Stadium {name:\"Carrow Road Stadium\", opened:1935, capacity:27359})\n",
    "CREATE (citylondon:City {name:\"London\"})\n",
    "CREATE (cityliverpool:City {name:\"Liverpool\"})\n",
    "CREATE (citymanchester:City {name:\"Manchester\"})\n",
    "CREATE (citywolverhampton:City {name:\"Wolverhampton\"})\n",
    "CREATE (cityleicester:City {name:\"Leicester\"})\n",
    "CREATE (citybrighton:City {name:\"Brighton\"})\n",
    "CREATE (citysouthampton:City {name:\"Southampton\"})\n",
    "CREATE (citynewcastle:City {name:\"Newcastle\"})\n",
    "CREATE (citysheffield:City {name:\"Sheffield\"})\n",
    "CREATE (citybirmingham:City {name:\"Birmingham\"})\n",
    "CREATE (citywatford:City {name:\"Watford\"})\n",
    "CREATE (citynorwich:City {name:\"Norwich\"})\n",
    "CREATE (citybournemouth:City {name:\"Bournemouth\"})\n",
    "CREATE (cityburnley:City {name:\"Burnley\"})\n",
    "CREATE (arsenal)-[:STADIUM]->(theemirates)-[:CITY]->(citylondon)\n",
    "CREATE (chelsea)-[:STADIUM]->(stamfordbridge)-[:CITY]->(citylondon)\n",
    "CREATE (tottenhamhotspur)-[:STADIUM]->(tottenhamhotspurstadium)-[:CITY]->(citylondon)\n",
    "CREATE (westhamunited)-[:STADIUM]->(londonstadium)-[:CITY]->(citylondon)\n",
    "CREATE (crystalpalace)-[:STADIUM]->(selhurstparkstadium)-[:CITY]->(citylondon)\n",
    "CREATE (liverpool)-[:STADIUM]->(anfield)-[:CITY]->(cityliverpool)\n",
    "CREATE (everton)-[:STADIUM]->(goodisonpark)-[:CITY]->(cityliverpool)\n",
    "CREATE (manchesterunited)-[:STADIUM]->(oldtrafford)-[:CITY]->(citymanchester)\n",
    "CREATE (manchestercity)-[:STADIUM]->(etihadstadium)-[:CITY]->(citymanchester)\n",
    "CREATE (wolverhamptonwanderers)-[:STADIUM]->(molineuxstadium)-[:CITY]->(citywolverhampton)\n",
    "CREATE (leicestercity)-[:STADIUM]->(kingpowerstadium)-[:CITY]->(cityleicester)\n",
    "CREATE (brightonandhovealbion)-[:STADIUM]->(americanexpresscommunitystadium)-[:CITY]->(citybrighton)\n",
    "CREATE (southampton)-[:STADIUM]->(stmarysstadium)-[:CITY]->(citysouthampton)\n",
    "CREATE (newcastleunited)-[:STADIUM]->(stjamespark)-[:CITY]->(citynewcastle)\n",
    "CREATE (sheffieldunited)-[:STADIUM]->(bramalllane)-[:CITY]->(citysheffield)\n",
    "CREATE (astonvilla)-[:STADIUM]->(villapark)-[:CITY]->(citybirmingham)\n",
    "CREATE (watford)-[:STADIUM]->(vicarageroad)-[:CITY]->(citywatford)\n",
    "CREATE (norwichcity)-[:STADIUM]->(carrowroadstadium)-[:CITY]->(citynorwich)\n",
    "CREATE (bournemouth)-[:STADIUM]->(vitalitystadium)-[:CITY]->(citybournemouth)\n",
    "CREATE (burnley)-[:STADIUM]->(turfmoor)-[:CITY]->(cityburnley)\n",
    "CREATE (arsenal)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (chelsea)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (tottenhamhotspur)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (westhamunited)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (liverpool)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (everton)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (manchesterunited)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (manchestercity)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (wolverhamptonwanderers)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (burnley)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (sheffieldunited)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (crystalpalace)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (southampton)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (newcastleunited)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (brightonandhovealbion)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (watford)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (bournemouth)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (astonvilla)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (leicestercity)-[:CURRENT_LEAGUE]->(epl)\n",
    "CREATE (norwichcity)-[:CURRENT_LEAGUE]->(epl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Verify some data was inserted\n",
    "When the cells below are run you should see the names of the 20 teams in the data set. Note that either `%%oc` or `%%opencypher` may be used to denote that a cell contains an openCypher query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (t:Team)\n",
    "RETURN t.name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Explore the relationships in the graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "  MATCH (league)<-[e:CURRENT_LEAGUE]-(team)-[s:STADIUM]->(stad)-[c:CITY]->(city)\n",
    "  RETURN league.name,type(e),team.name,type(s),stad.name,type(c),city.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (team)-[e:CURRENT_LEAGUE]->(league)\n",
    "RETURN team.name AS Team,type(e) AS Type,league.name AS League"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many teams were in the league that season?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "  MATCH (t:Team)\n",
    "  RETURN count(t) AS count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (:League {nickname:'EPL'})<-[:CURRENT_LEAGUE]-(t)\n",
    "RETURN count(t) AS count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Team info\n",
    "Find the teams in the graph and their properties. The first query also returns the ID and other information. The second query just returns the properties."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc  -l20\n",
    "MATCH (t:Team)\n",
    "RETURN t AS Team_Info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc \n",
    "MATCH (t:Team)\n",
    "RETURN properties(t) AS Team_Info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Team and stadium info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc \n",
    "MATCH  (t:Team)-[:STADIUM]->(stad)-[:CITY]->(city)\n",
    "RETURN t.name AS Team, stad.name AS Stadium, city.name AS City"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Visualization of the graph\n",
    "\n",
    "Running the next two cells will create a visualization of the entire EPL graph. The cell immediately below is used to define a mapping between node labels and the property to be used to label nodes in the visualization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display_var = '{\"Team\":\"name\",\"City\":\"name\",\"Stadium\":\"name\",\"League\":\"name\"}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The query below creates a path containing the entire graph structure. The `-d` hint is used to enable the mappings defined above. The `-l` hint sets the maximum length of the text that can be displayed in a node. The text will be truncated 3 characters short of that length in cases where there is more text to display. An elipsis `...` is used to indicate that not all of the text was shown."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc -d $display_var -l20\n",
    "MATCH p=(lg:League)<--(t:Team)-[:STADIUM]->(s:Stadium)-[:CITY]->(c:City)\n",
    "RETURN p"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Teams based in London"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "  MATCH (:City {name:'London'})<-[:CITY]-()<-[:STADIUM]-(t:Team)\n",
    "  RETURN t.name AS Team"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc  -d $display_var -l20\n",
    "MATCH p=(:City {name:'London'})<-[:CITY]-()<-[:STADIUM]-(t:Team)\n",
    "RETURN p"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stadiums in London"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (:City {name:'London'})<-[:CITY]-(s)\n",
    "RETURN s.name AS Stadium\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (s)-[:CITY]->(:City {name:'London'})\n",
    "RETURN s.name AS Stadium"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Teams in London plus their stadiums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (t:Team)-[:STADIUM]->(s)-[:CITY]->(:City {name:'London'})\n",
    "RETURN s.name AS Stadium, t.name AS Team"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding sub-strings\n",
    "This query looks for any teams that have the string \"ou\" in their name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (t:Team)\n",
    "WHERE t.name CONTAINS 'ou'\n",
    "RETURN t.name AS Team"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find everything the graph contains related to Arsenal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "  MATCH (t:Team {name:'Arsenal'})-->(stadium)-->(city)\n",
    "  RETURN t.name AS Name,\n",
    "         t.founded AS Founded,\n",
    "         t.nickname AS Nickname,\n",
    "         stadium.name AS Stadium,\n",
    "         city.name aS City\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many stadiums are in each city?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "  MATCH (s:Stadium)-[:CITY]->(city:City)\n",
    "  RETURN city.name AS Name, count(*) AS Count \n",
    "  ORDER BY Count DESC\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What year did each stadium open?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (s:Stadium)\n",
    "RETURN s.name AS Name, s.opened AS Opened\n",
    "ORDER BY s.opened "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stadiums ordered by descending capacity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (s:Stadium)\n",
    "RETURN s.name AS Name, s.capacity AS Capacity\n",
    "ORDER BY s.capacity DESC "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of teams founded in a given year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (t:Team)\n",
    "RETURN collect(t.name) AS Name,t.founded AS Founded, count(t) AS Count\n",
    "ORDER BY Founded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Teams founded in the same year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "  MATCH (a:Team) \n",
    "  MATCH (b:Team)\n",
    "  WHERE a.founded = b.founded AND a <> b\n",
    "  RETURN a.name AS Name_1, b.name AS Name_2, a.founded AS Founded\n",
    "  ORDER BY a.founded\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "  MATCH (a:Team) \n",
    "  MATCH (b:Team)\n",
    "  WHERE a.founded = b.founded AND id(a) > id(b)\n",
    "  RETURN a.name AS Name_1, b.name AS Name_2, a.founded AS Founded\n",
    "  ORDER BY a.founded\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Page Width\n",
    "If you would like Jupyter to maximise the horizontal screen real estate run the cell below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}