got-analysis/game-of-graphs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "62259917",
   "metadata": {},
   "source": [
    "# Game of Graphs - Graph analytics on a GoT dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1358e7b",
   "metadata": {},
   "source": [
    "\n",
    "## Prerequisites <a name=\"prerequisites\"></a>\n",
    "\n",
    "For this tutorial, we need to install:\n",
    "\n",
    "- [Docker](https://docs.docker.com/get-docker/) - to run Memgraph, since Memgraph is a native Linux application and cannot be installed on Windows and macOS\n",
    "- [Memgraph Platform](https://memgraph.com/docs/memgraph/installation) - the complete streaming graph application platform; follow the instructions to install Memgraph Platform with Docker for your OS\n",
    "- [GQLAlchemy](https://pypi.org/project/gqlalchemy/)\n",
    "\n",
    "> ### Memgraph Platform installation using Docker\n",
    ">\n",
    "> After we install Docker, we can run the Memgraph Platform container by running:\n",
    ">\n",
    ">```\n",
    ">docker run -it -p 7687:7687 -p 7444:7444 -p 3000:3000 memgraph/memgraph-platform\n",
    ">```\n",
    ">\n",
    ">**Memgraph Platform** contains:\n",
    ">\n",
    ">- **MemgraphDB** - the database that holds your data\n",
    ">- **Memgraph Lab** - visual user interface for running queries and visualizing graph data (running at >`localhost:3000`)\n",
    ">- **mgconsole** - command-line interface for running queries\n",
    ">- **MAGE** - graph algorithms and modules library\n",
    "\n",
    "We will use the **GQLAlchemy**'s object graph mapper (OGM) to connect to Memgraph and quickly execute **Cypher** queries. GQLAlchemy also serves as a Python driver/client for Memgraph. We can install it using:\n",
    "\n",
    "```\n",
    "pip install gqlalchemy\n",
    "```\n",
    "\n",
    "> You may need to install [CMake](https://cmake.org/download/) before installing GQLAlchemy."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d6dfef6",
   "metadata": {},
   "source": [
    "## Connect to Memgraph with GQLAlchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7b913342",
   "metadata": {},
   "outputs": [],
   "source": [
    "from gqlalchemy import Memgraph\n",
    "memgraph = Memgraph(\"127.0.0.1\", 7687)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3c44ad9",
   "metadata": {},
   "source": [
    "Let's see if there's anything in the database. To do that, we can count the number of nodes in the database with the following Cypher query:\n",
    "```\n",
    "MATCH (n) RETURN count(n) AS number_of_nodes;\n",
    "```\n",
    "\n",
    "With the GQLAlchemy, you can execute that query and fetch its results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "69bcdc59",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'number_of_nodes': 2677}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (n) RETURN count(n) AS number_of_nodes ;\n",
    "    \"\"\"\n",
    ")\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcf51341",
   "metadata": {},
   "source": [
    "Besides simple Cypher query execution, you can also use GQLAlchemy query builder:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "02d2d12a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'number_of_nodes': 2677}\n"
     ]
    }
   ],
   "source": [
    "from gqlalchemy import match\n",
    "results = match().node(variable=\"n\").return_({\"count(n)\": \"number_of_nodes\"}).execute()\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9967d2ac",
   "metadata": {},
   "source": [
    "In the similar way, you can determine the number of relationships in the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "85feb1aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'number_of_relationships': 11967}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH ()-[r]->() RETURN count(r) AS number_of_relationships ;\n",
    "    \"\"\"\n",
    ")\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e929551",
   "metadata": {},
   "source": [
    "And with the query builder like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "58caf1d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'number_of_relationships': 11967}\n"
     ]
    }
   ],
   "source": [
    "from gqlalchemy import match\n",
    "results = match().node().to(variable=\"r\").node().return_({\"count(r)\": \"number_of_relationships\"}).execute()\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05ebf808",
   "metadata": {},
   "source": [
    "## Load the dataset\n",
    "\n",
    "The simplest way to load a dataset into Memgraph is by using **Memgraph Lab** which is currently running on `localhost:3000`. The first thing you're going to see on that address is the **Quick Connect**. You just have to click `Connect`!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "208e2e29",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/2bb531a3459d5b07953ecb124b50ff677ee337bd/got-analysis/img/memgraph-lab-quick-connect.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1da310da",
   "metadata": {},
   "source": [
    "After you click `Connect`, head over to the `Datasets` tab, and load **Game of Thrones deaths** dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d900aa3",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/2bb531a3459d5b07953ecb124b50ff677ee337bd/got-analysis/img/memgraph-lab-load-dataset.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c4934ea",
   "metadata": {},
   "source": [
    "Now, let's again check the **number of nodes** and **relationships** in the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dc3b280d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'number_of_nodes': 2677}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (n) RETURN count(n) AS number_of_nodes ;\n",
    "    \"\"\"\n",
    ")\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7b521170",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'number_of_relationships': 11967}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH ()-[r]->() RETURN count(r) AS number_of_relationships ;\n",
    "    \"\"\"\n",
    ")\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1be411f",
   "metadata": {},
   "source": [
    "You can also visualize this in the Memgraph Lab, by running:\n",
    "\n",
    "```\n",
    "MATCH (u)-[r]->(m)\n",
    "RETURN u, r, m;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f77472b0",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/da5a8dc96eb9035912bcf23f2ec9e457f2fbd262/got-analysis/img/memgraph-lab-got-dataset.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec67d68f",
   "metadata": {},
   "source": [
    "Next, click on the **Graph Schema** tab and generate graph schema to see how the relationships are connecting the nodes in the database."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c0ec9be",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/b483044ba9c88a4a43c7544d27af750bc84a36f8/got-analysis/img/memgraph-lab-graph-schema.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14ca61f1",
   "metadata": {},
   "source": [
    "Therefore, we have nodes labeled with `Character`, `Allegiance`, `Death`, `Episode`, `Location` and `Season`, and relationships of type `KILLED`, `LOYAL_TO`, `KILLER_IN`, `VICTIM_IN`, `HAPPENED_IN` and `PART_OF` connecting them. In more details, we have a whole graph model:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4352839",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/b483044ba9c88a4a43c7544d27af750bc84a36f8/got-analysis/img/graph-data-model.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a13b90e",
   "metadata": {},
   "source": [
    "## Map nodes and relationships\n",
    "\n",
    "We can create a schema for this model inside the Python code by defining the classes that inherit from the `Node` and `Relationship` classes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d387293e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from gqlalchemy import Node, Relationship\n",
    "\n",
    "class Character(Node):\n",
    "    name: str\n",
    "\n",
    "class Allegiance(Node):\n",
    "    name: str\n",
    "        \n",
    "class Death(Node):\n",
    "    order: int\n",
    "        \n",
    "class Episode(Node):\n",
    "    name: str\n",
    "    number: int\n",
    "    imdb_rating: float\n",
    "\n",
    "class Season(Node):\n",
    "    number: int\n",
    "\n",
    "class Location(Node):\n",
    "    name: str\n",
    "\n",
    "class Killed(Relationship, type=\"KILLED\"):\n",
    "    method: str\n",
    "    count: int\n",
    "\n",
    "class LoyalTo(Relationship, type=\"LOYAL_TO\"):\n",
    "    pass\n",
    "\n",
    "class VictimIn(Relationship, type=\"VICTIM_IN\"):\n",
    "    pass\n",
    "\n",
    "class KillerIn(Relationship, type=\"KILLER_IN\"):\n",
    "    pass\n",
    "\n",
    "class HappenedIn(Relationship, type=\"HAPPENED_IN\"):\n",
    "    pass\n",
    "\n",
    "class PartOf(Relationship, type=\"PART_OF\"):\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df16e53d",
   "metadata": {},
   "source": [
    "Since the data is already loaded into Memgraph, there is no need to define any constraints. If you want to learn how to do that, head over to the [GQLAlchemy docs](https://memgraph.com/docs/gqlalchemy/how-to-guides). What’s going on here:\n",
    "\n",
    "- `Node` is a Python class that maps to a graph object in `Memgraph`.\n",
    "- Classes that inherit from `Node` map to a single label in the graph database.\n",
    "- In this case, the class `Character` maps to the label `:Character`.\n",
    "- The property `name` is mapped to property of the node labeled `Character` in the graph database.\n",
    "- Relationships can also have properties, such as defined in `Killed` class that maps to `:KILLED` type of the relationship in the database.\n",
    "- If the relationship doesn't have a property, you can just use `pass`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce39f612",
   "metadata": {},
   "source": [
    "## Simple analytics with GQLAlchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20d94c98",
   "metadata": {},
   "source": [
    "Let's first list the total number of seasons and episodes within each season in the dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "06ba29c8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'total_number_of_seasons': 8}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (s:Season)\n",
    "    RETURN count(s) AS total_number_of_seasons;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "38b466c9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Season: 1 Number of episodes: 9\n",
      "Season: 2 Number of episodes: 9\n",
      "Season: 3 Number of episodes: 9\n",
      "Season: 4 Number of episodes: 10\n",
      "Season: 5 Number of episodes: 10\n",
      "Season: 6 Number of episodes: 9\n",
      "Season: 7 Number of episodes: 7\n",
      "Season: 8 Number of episodes: 6\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (e:Episode)-[:PART_OF]->(s:Season)\n",
    "    RETURN s, collect(e) AS episodes\n",
    "    ORDER BY s.number;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Season:\", result[\"s\"].number, \"Number of episodes:\", len(result[\"episodes\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a2dc933",
   "metadata": {},
   "source": [
    "Let's also see how many characters and deaths there are in the dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "951b2985",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'total_number_of_characters': 289}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (c:Character)\n",
    "    RETURN count(c) AS total_number_of_characters;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5d9a2d43",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'total_number_of_deaths': 2224}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (d:Death)\n",
    "    RETURN count(d) AS total_number_of_deaths;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "266c2041",
   "metadata": {},
   "source": [
    "## No deaths episodes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13a6bd03",
   "metadata": {},
   "source": [
    "We all know that Game of Thones is famous for the amount of (important) people that died throughout the TV show. But, there are couple of exceptions - episode when no one died! Above we listed the number of episodes per season that are in the dataset, and that leads us to the conclusion on how many episodes have no deaths in them. Let's first create lists of all episodes per season and then filter it out to find out which ones are missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "206dc6a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "season_1 = {'Winter Is Coming ', 'The Kingsroad ', 'Lord Snow', \n",
    "            'Cripples Bastards and Broken Things ', 'The Wolf and the Lion ', \n",
    "            'A Golden Crown ', 'You Win or You Die', 'The Pointy End ', \n",
    "            'Baelor ', 'Fire and Blood'}\n",
    "\n",
    "season_2 = {\"The North Remembers\", \"The Night Lands \", \"What Is Dead May Never Die\", \n",
    "            \"Garden of Bones\", \"The Ghost of Harrenhal \", \"The Old Gods and the New\", \n",
    "            \"A Man Without Honor\", \"The Prince of Winterfell\", \"Blackwater \", \"Valar Morghulis \"}\n",
    "\n",
    "season_3 = {\"Valar Dohaeris \", \"Dark Wings Dark Words \", \"Walk of Punishment \", \n",
    "            \"And Now His Watch Is Ended \", \"Kissed by Fire\", \"The Climb \", \"The Bear and the Maiden Fair\", \n",
    "            \"Second Sons \", \"The Rains of Castamere\", \"Mhysa \"}\n",
    "\n",
    "season_6 = {\"The Red Woman \", \"Home \", \"Oathbreaker \", \"Book of the Stranger \", \"The Door \", \n",
    "            \"Blood of My Blood\", \"The Broken Man \", \"No One\", \"Battle of the Bastards\", \"The Winds of Winter\"}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3266f562",
   "metadata": {},
   "source": [
    "First, let's check which episode is missing from the Season 1:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "48497779",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Lord Snow'}\n"
     ]
    }
   ],
   "source": [
    "season_1 = {'Winter Is Coming ', 'The Kingsroad ', 'Lord Snow', \n",
    "            'Cripples Bastards and Broken Things ', 'The Wolf and the Lion ', \n",
    "            'A Golden Crown ', 'You Win or You Die', 'The Pointy End ', \n",
    "            'Baelor ', 'Fire and Blood'}\n",
    "\n",
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (e:Episode)-[p:PART_OF]->(s:Season {number: 1})\n",
    "    RETURN e;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "season_1_deaths = set()\n",
    "\n",
    "for result in results:\n",
    "    season_1_deaths.add(result[\"e\"].name)\n",
    "\n",
    "print(season_1 - season_1_deaths)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0137d54",
   "metadata": {},
   "source": [
    "Looks like it's the episode **Lord Snow**, which is the Episode 3. Next, let's see which episode is missing from Season 2:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3e14f31e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'The Prince of Winterfell'}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (e:Episode)-[p:PART_OF]->(s:Season {number: 2})\n",
    "    RETURN e;\n",
    "    \"\"\"\n",
    ")\n",
    "season_2_deaths = set()\n",
    "\n",
    "for result in results:\n",
    "    season_2_deaths.add(result[\"e\"].name)\n",
    "\n",
    "print(season_2 - season_2_deaths)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8046f79",
   "metadata": {},
   "source": [
    "The episode that is missing is called **The Prince of Winterfell** and it is Episode 8. Next up, Season 3:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d4668039",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'The Bear and the Maiden Fair'}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (e:Episode)-[p:PART_OF]->(s:Season {number: 3})\n",
    "    RETURN e;\n",
    "    \"\"\"\n",
    ")\n",
    "season_3_deaths = set()\n",
    "\n",
    "for result in results:\n",
    "    season_3_deaths.add(result[\"e\"].name)\n",
    "\n",
    "print(season_3 - season_3_deaths)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb623305",
   "metadata": {},
   "source": [
    "**The Bear and the Maiden Fair** is the episode without any killings, and it's 7th Episode of Season 3. The last season which we have to check is Season 6:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "df5c41d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Blood of My Blood'}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (e:Episode)-[p:PART_OF]->(s:Season {number: 6})\n",
    "    RETURN e;\n",
    "    \"\"\"\n",
    ")\n",
    "season_6_deaths = set()\n",
    "\n",
    "for result in results:\n",
    "    season_6_deaths.add(result[\"e\"].name)\n",
    "\n",
    "print(season_6 - season_6_deaths)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fb12815",
   "metadata": {},
   "source": [
    "That's it! **Blood of My Blood** is the last episode without any deaths, and it is the Episode 6!\n",
    "Besided that, there are couple of sources claming that there are more episodes without any deaths: Season 3 - Episode 1 (**Valar Dohaeris**), Season 3 - Episode 2 (**Dark Wings, Dark Words**), and Season 8 - Episode 2 (**A Knight of the Seven Kingdoms**). Let's check whether that's true!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2da4615",
   "metadata": {},
   "source": [
    "### Valar Dohaeris"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "3b2365ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Manticore\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (c:Character)-[v:VICTIM_IN]->(d:Death)\n",
    "    -[:HAPPENED_IN]->(e:Episode {number: 1})\n",
    "    -[p:PART_OF]->(s:Season {number: 3})\n",
    "    RETURN c;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "print(next(results)[\"c\"].name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6300c25c",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/a698d3095c5bc0066a379004ef88c2a44f8c8821/got-analysis/img/memgraph-lab-manticore.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c02b1d6",
   "metadata": {},
   "source": [
    "### Dark Wings, Dark Words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "02fdba60",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hoster Tully\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (c:Character)-[v:VICTIM_IN]->(d:Death)\n",
    "    -[:HAPPENED_IN]->(e:Episode {number: 2})\n",
    "    -[p:PART_OF]->(s:Season {number: 3})\n",
    "    RETURN c;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "print(next(results)[\"c\"].name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "025b8490",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/a698d3095c5bc0066a379004ef88c2a44f8c8821/got-analysis/img/memgraph-lab-hoster-tully.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dbf13a4",
   "metadata": {},
   "source": [
    "### A Knight of the Seven Kingdoms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9fe6edc3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ned Umber\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (c:Character)-[v:VICTIM_IN]->(d:Death)\n",
    "    -[:HAPPENED_IN]->(e:Episode {number: 2})\n",
    "    -[p:PART_OF]->(s:Season {number: 8})\n",
    "    RETURN c;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "print(next(results)[\"c\"].name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8600acc1",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/a698d3095c5bc0066a379004ef88c2a44f8c8821/got-analysis/img/memgraph-lab-ned-umber.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "704d489a",
   "metadata": {},
   "source": [
    "## Characters that killed themselves"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa0e798a",
   "metadata": {},
   "source": [
    "There were a couple of characters that managed to run away from the other killers, but still ended up dead. Let's see how:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "431c4119",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Character: Dothraki man | Method: Arakh\n",
      "Character: Sandor the Hound Clegane | Method: Dragonfire (Dragon)\n",
      "Character: Melisandre the Red Woman of Asshai | Method: Old Age\n",
      "Character: Bolton soldier | Method: Arrow\n",
      "Character: Jaqen Hghar | Method: Poison\n",
      "Character: Peasant | Method: Poison\n",
      "Character: Greyjoy Soldier | Method: Sword\n",
      "Character: Greyjoy Soldier | Method: Axe\n",
      "Character: Greyjoy Soldier | Method: Mace\n",
      "Character: Greyjoy Soldier | Method: Knife\n",
      "Character: Tommen Baratheon | Method: Falling\n",
      "Character: Sons of the Harpy agent | Method: Sword\n",
      "Character: Aemon Targaryen | Method: Old Age\n",
      "Character: Gladiator | Method: Sword\n",
      "Character: Gladiator | Method: Spear\n",
      "Character: Selyse Florent | Method: Rope\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (c:Character)-[k:KILLED]->(d:Character)\n",
    "    WHERE c = d\n",
    "    RETURN c, k;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Character:\", result[\"c\"].name, \"| Method:\", result[\"k\"].method)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe577ba3",
   "metadata": {},
   "source": [
    "I guess the only ones who died of old age are **Melisandre** and **Aemon Targaryen**. But, let's be real - Aemon is the only true badass here!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5db00ea9",
   "metadata": {},
   "source": [
    "## Graph traversals and PageRank"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "583112f1",
   "metadata": {},
   "source": [
    "Let's see how the importance of episodes and locations in the dataset is truly measured by the number of deaths. First, we can list the top 10 locations where the most deaths occurred:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "118403fc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Kings Landing 1170\n",
      "Roseroad 207\n",
      "Winterfell 169\n",
      "Meereen 136\n",
      "The Twins 89\n",
      "Beyond the Wall 78\n",
      "Castle Black 66\n",
      "The Narrow Sea 35\n",
      "Riverlands 28\n",
      "The Wall 26\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (l:Location)<-[:HAPPENED_IN]-(d:Death)\n",
    "    RETURN l AS location, count(d) AS death_count\n",
    "    ORDER BY death_count DESC\n",
    "    LIMIT 10;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"location\"].name, result[\"death_count\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "721b7feb",
   "metadata": {},
   "source": [
    "We can also run PageRank algorithm as a measure of node influence in a network:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c0a91a25",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Episode: Kings Landing | Rank: 0.06106011381918924\n",
      "Episode: Roseroad | Rank: 0.010786290904476399\n",
      "Episode: Winterfell | Rank: 0.009650604216610618\n",
      "Episode: Meereen | Rank: 0.007653449136907163\n",
      "Episode: The Twins | Rank: 0.005029859049634092\n",
      "Episode: Beyond the Wall | Rank: 0.0048574484597048535\n",
      "Episode: Castle Black | Rank: 0.003955983097311168\n",
      "Episode: The Narrow Sea | Rank: 0.0021040262793516795\n",
      "Episode: Riverlands | Rank: 0.0019043870463720024\n",
      "Episode: The Wall | Rank: 0.001583596860766157\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    CALL pagerank.get()\n",
    "    YIELD *\n",
    "    WITH node, rank\n",
    "    WHERE node:Location\n",
    "    RETURN node, rank\n",
    "    ORDER BY rank DESC\n",
    "    LIMIT 10;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Episode:\", result[\"node\"].name, \"| Rank:\", result[\"rank\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cff6ae6",
   "metadata": {},
   "source": [
    "And which episodes have the most deaths?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d1834484",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The Bells  844\n",
      "The Spoils of War  205\n",
      "The Winds of Winter 203\n",
      "Battle of the Bastards 133\n",
      "The Watchers on the Wall  86\n",
      "Blackwater  72\n",
      "The Dance of Dragons 58\n",
      "Dragonstone  54\n",
      "Sons of the Harpy 48\n",
      "The Rains of Castamere 45\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (d:Death)-[:HAPPENED_IN]->(e:Episode)\n",
    "    RETURN e AS episode, count(d) AS death_count\n",
    "    ORDER BY death_count DESC\n",
    "    LIMIT 10;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"episode\"].name, result[\"death_count\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d000805f",
   "metadata": {},
   "source": [
    "Let's check whether the **PageRank algorithm** gives us the similiar result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "6a9afb9b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Episode: The Bells  | Rank: 0.043164724601018695\n",
      "Episode: The Winds of Winter | Rank: 0.010801371244279762\n",
      "Episode: The Spoils of War  | Rank: 0.010652824071617746\n",
      "Episode: Battle of the Bastards | Rank: 0.007326424536668109\n",
      "Episode: The Watchers on the Wall  | Rank: 0.004888653411156207\n",
      "Episode: Blackwater  | Rank: 0.0040451560244256475\n",
      "Episode: The Dance of Dragons | Rank: 0.003304325314698726\n",
      "Episode: Dragonstone  | Rank: 0.002979021268992005\n",
      "Episode: Sons of the Harpy | Rank: 0.002835536547706026\n",
      "Episode: The Rains of Castamere | Rank: 0.002811794141977363\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    CALL pagerank.get()\n",
    "    YIELD *\n",
    "    WITH node, rank\n",
    "    WHERE node:Episode\n",
    "    RETURN node, rank\n",
    "    ORDER BY rank DESC\n",
    "    LIMIT 10;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Episode:\", result[\"node\"].name, \"| Rank:\", result[\"rank\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5183c441",
   "metadata": {},
   "source": [
    "## Seasons and allegiances"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d21056e",
   "metadata": {},
   "source": [
    "### Number of kills per season"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "32535947",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8 892\n",
      "6 396\n",
      "7 334\n",
      "4 170\n",
      "5 158\n",
      "2 130\n",
      "3 86\n",
      "1 58\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (d:Death)-[:HAPPENED_IN]->(s:Season)\n",
    "    RETURN s AS season, count(d) AS death_count\n",
    "    ORDER BY death_count DESC;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"season\"].number, result[\"death_count\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eee3fc0c",
   "metadata": {},
   "source": [
    "### Top seasons by their IMDB rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "55679941",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Season: 4 | Rating: 9.31\n",
      "Season: 1 | Rating: 9.14\n",
      "Season: 6 | Rating: 9.13\n",
      "Season: 7 | Rating: 9.1\n",
      "Season: 3 | Rating: 9.09\n",
      "Season: 2 | Rating: 8.98\n",
      "Season: 5 | Rating: 8.83\n",
      "Season: 8 | Rating: 6.38\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (e:Episode)-[:PART_OF]->(s:Season)\n",
    "    RETURN s AS season, round(100 * avg(e.imdb_rating))/100 AS rating\n",
    "    ORDER BY rating DESC;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Season:\", result[\"season\"].number, \"| Rating:\", result[\"rating\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65d019f1",
   "metadata": {},
   "source": [
    "### Top 10 allegiances by the kill/death ratio (KDR)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "329bd44a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Allegiance: White Walkers | Kills: 205 | Deaths: 5 | KDR: 41.0\n",
      "Allegiance: Warlocks of Qarth | Kills: 11 | Deaths: 1 | KDR: 11.0\n",
      "Allegiance: House Clegane | Kills: 54 | Deaths: 5 | KDR: 10.8\n",
      "Allegiance: House Targaryen | Kills: 1329 | Deaths: 151 | KDR: 8.8\n",
      "Allegiance: Lord of Light | Kills: 7 | Deaths: 1 | KDR: 7.0\n",
      "Allegiance: House Umber | Kills: 7 | Deaths: 2 | KDR: 3.5\n",
      "Allegiance: Faceless Men | Kills: 3 | Deaths: 1 | KDR: 3.0\n",
      "Allegiance: House Baratheon of Kings Landing | Kills: 194 | Deaths: 66 | KDR: 2.94\n",
      "Allegiance: Nights Watch | Kills: 394 | Deaths: 143 | KDR: 2.76\n",
      "Allegiance: Sand Snakes | Kills: 8 | Deaths: 3 | KDR: 2.67\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (:Character)-[death:KILLED]->(:Character)-[:LOYAL_TO]->(a:Allegiance)\n",
    "    WITH a, sum(death.count) AS deaths\n",
    "    MATCH (:Character)<-[kill:KILLED]-(:Character)-[:LOYAL_TO]->(a)\n",
    "    RETURN a AS allegiance,\n",
    "           sum(kill.count) AS kills,\n",
    "           deaths,\n",
    "           round(100 *(tofloat(sum(kill.count))/deaths))/100 AS KDR\n",
    "    ORDER BY KDR DESC\n",
    "    LIMIT 10;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Allegiance:\", result[\"allegiance\"].name, \"| Kills:\", result[\"kills\"], \n",
    "          \"| Deaths:\", result[\"deaths\"], \"| KDR:\", result[\"KDR\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "371a847a",
   "metadata": {},
   "source": [
    "### Battle of Bastards causalties - Starks vs Boltons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "52cef37b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Allegiance: House Bolton | Deaths: 49\n",
      "Allegiance: House Stark | Deaths: 42\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (c:Character)-[:LOYAL_TO]->(a:Allegiance)\n",
    "    MATCH (c)-[:VICTIM_IN]-(d:Death)-[:HAPPENED_IN]-(:Episode {name: 'Battle of the Bastards'})\n",
    "    RETURN a AS house, count(d) AS death_count\n",
    "    ORDER BY death_count DESC\n",
    "    LIMIT 2;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Allegiance:\", result[\"house\"].name, \"| Deaths:\", result[\"death_count\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02a4a57a",
   "metadata": {},
   "source": [
    "## Is Daenerys that bad?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50efe4fb",
   "metadata": {},
   "source": [
    "Let's list all characters Daenerys killed and count how many of them, along with the episode that happened in:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c82986be",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Victim: Golden Company soldier | Kill Count: 374 | Episode name: The Bells \n",
      "Victim: Kings Landing Citizen | Kill Count: 227 | Episode name: The Bells \n",
      "Victim: Lannister soldier | Kill Count: 178 | Episode name: The Spoils of War \n",
      "Victim: Lannister soldier | Kill Count: 166 | Episode name: The Bells \n",
      "Victim: Sons of the Harpy agent | Kill Count: 54 | Episode name: The Dance of Dragons\n",
      "Victim: Dothraki Khal | Kill Count: 14 | Episode name: Book of the Stranger \n",
      "Victim: The Masters Soldier | Kill Count: 12 | Episode name: Battle of the Bastards\n",
      "Victim: Greyjoy Soldier | Kill Count: 2 | Episode name: The Bells \n",
      "Victim: Horse | Kill Count: 2 | Episode name: The Spoils of War \n",
      "Victim: Xaro Xhoan Daxos | Kill Count: 1 | Episode name: Valar Morghulis \n",
      "Victim: Khal Moro | Kill Count: 1 | Episode name: Book of the Stranger \n",
      "Victim: Golden Company horse | Kill Count: 1 | Episode name: The Bells \n",
      "Victim: Pyat Pree | Kill Count: 1 | Episode name: Valar Morghulis \n",
      "Victim: Zalla | Kill Count: 1 | Episode name: The Children \n",
      "Victim: Kraznys mo Nakloz | Kill Count: 1 | Episode name: And Now His Watch Is Ended \n",
      "Victim: Varys | Kill Count: 1 | Episode name: The Bells \n",
      "Victim: Doreah | Kill Count: 1 | Episode name: Valar Morghulis \n",
      "Victim: Cersei Lannister | Kill Count: 1 | Episode name: The Bells \n",
      "Victim: Goat | Kill Count: 1 | Episode name: The Laws of Gods and Men \n",
      "Victim: Mirri Maz Duur | Kill Count: 1 | Episode name: Fire and Blood\n",
      "Victim: Khal Drogo | Kill Count: 1 | Episode name: Fire and Blood\n",
      "Victim: Dickon Tarly | Kill Count: 1 | Episode name: Eastwatch\n",
      "Victim: Randyll Tarly | Kill Count: 1 | Episode name: Eastwatch\n",
      "Victim: Jaime Lannister | Kill Count: 1 | Episode name: The Bells \n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (daenerys:Character {name: 'Daenerys Targaryen'})-[:KILLED]->(victim:Character)\n",
    "    MATCH (daenerys)-[:KILLER_IN]->(d:Death)<-[:VICTIM_IN]-(victim)\n",
    "    MATCH (d)-[:HAPPENED_IN]-(e:Episode)\n",
    "    RETURN DISTINCT victim, count(d) AS kill_count, e AS episode\n",
    "    ORDER BY kill_count DESC;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Victim:\", result[\"victim\"].name, \"| Kill Count:\", result[\"kill_count\"]\n",
    "          , \"| Episode name:\", result[\"episode\"].name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a38cecf",
   "metadata": {},
   "source": [
    "### Who is the killer influencer?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "94c66c50",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Daenerys Targaryen | BC: 0.26416716859707734\n",
      "Lannister soldier | BC: 0.11163138590238253\n",
      "Sons of the Harpy agent | BC: 0.04326120746568793\n",
      "Jon Snow | BC: 0.04152733973021257\n",
      "Wildling | BC: 0.037823960457165504\n",
      "Stark Soldier | BC: 0.02860945609559936\n",
      "Baratheon of Dragonstone soldier | BC: 0.027533816622595367\n",
      "Cersei Lannister | BC: 0.026445092829209968\n",
      "Stark soldier | BC: 0.02432818459201446\n",
      "Nights Watch brother | BC: 0.02271917772956741\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    CALL betweenness_centrality.get(False)\n",
    "    YIELD node, betweenness_centrality\n",
    "    WITH node, betweenness_centrality\n",
    "    WHERE 'Character' IN labels(node)\n",
    "    RETURN node, betweenness_centrality\n",
    "    ORDER BY betweenness_centrality DESC\n",
    "    LIMIT 10;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"node\"].name, \"| BC:\", result[\"betweenness_centrality\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "146bf9eb",
   "metadata": {},
   "source": [
    "## You know nothing, Jon Snow"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04e3f731",
   "metadata": {},
   "source": [
    "### Who would survive if Jon Snow stayed dead?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "51741a10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Victim: Bolton soldier | Kill count: 20\n",
      "Victim: Lannister soldier | Kill count: 7\n",
      "Victim: Daenerys Targaryen | Kill count: 1\n",
      "Victim: Olly | Kill count: 1\n",
      "Victim: Stark Soldier | Kill count: 1\n",
      "Victim: Alliser Thorne | Kill count: 1\n",
      "Victim: Othell Yarwyck | Kill count: 1\n",
      "Victim: Bowen Marsh | Kill count: 1\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (jon:Character {name: 'Jon Snow'})-[:KILLED]->(victim:Character)\n",
    "    MATCH (jon)-[:VICTIM_IN]->(jon_death:Death)\n",
    "    MATCH (jon)-[:KILLER_IN]->(victim_death:Death)<-[:VICTIM_IN]-(victim)\n",
    "    WHERE victim_death.order > jon_death.order\n",
    "    RETURN DISTINCT victim, count(victim_death) AS kill_count\n",
    "    ORDER BY kill_count DESC;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Victim:\", result[\"victim\"].name, \"| Kill count:\", result[\"kill_count\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdb9a1ee",
   "metadata": {},
   "source": [
    "### Who killed Jon Snow?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "65e19f9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Olly killed Jon Snow and Jon Snow killed Olly\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (jon:Character {name: 'Jon Snow'})-[v:VICTIM_IN]\n",
    "    ->(d:Death)<-[k:KILLER_IN]-(c:Character)\n",
    "    WITH jon, v, d, k, c\n",
    "    MATCH (c)-[v2:VICTIM_IN]->(d2:Death)<-[k2:KILLER_IN]-(c2:Character)\n",
    "    RETURN c, jon;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"c\"].name, \"killed\", result[\"jon\"].name, \"and\", \n",
    "          result[\"jon\"].name, \"killed\", result[\"c\"].name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79ff8390",
   "metadata": {},
   "source": [
    "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/582630e6240bec61876192174d1e746ef809a788/got-analysis/img/memgraph-lab-jon-snow-olly.png\" width=\"800\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81e7aa2d",
   "metadata": {},
   "source": [
    "### Who is the biggest traitor?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "b99e8f42",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Traitor: Jon Snow | Kill count: 9\n",
      "Traitor: Ramsay Bolton | Kill count: 4\n",
      "Traitor: Theon Greyjoy | Kill count: 4\n",
      "Traitor: Sandor the Hound Clegane | Kill count: 3\n",
      "Traitor: Reek | Kill count: 2\n",
      "Traitor: Euron Greyjoy | Kill count: 2\n",
      "Traitor: Daario Naharis | Kill count: 2\n",
      "Traitor: Gregor the Mountain Clegane | Kill count: 2\n",
      "Traitor: Daenerys Targaryen | Kill count: 2\n",
      "Traitor: Tyrion Lannister | Kill count: 2\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (killer:Character)-[:KILLED]->(victim:Character)\n",
    "    MATCH (killer)-[:LOYAL_TO]->(a:Allegiance)<-[:LOYAL_TO]-(victim)\n",
    "    RETURN killer AS traitor, count(victim) AS kill_count\n",
    "    ORDER BY kill_count DESC\n",
    "    LIMIT 10;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(\"Traitor:\", result[\"traitor\"].name, \"| Kill count:\", result[\"kill_count\"])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1fdeebd",
   "metadata": {},
   "source": [
    "## Dijkstra killing it"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f3f6dad",
   "metadata": {},
   "source": [
    "Memgraph supports graph algorithms as well. Let's use **Dijkstra's shortest path algorithm** to show the most gruesome path of kills. An example kill path is: Jon Snow killed 5 Lannister Soldiers and they killed 10 Stark soldiers with total kill_count of 15."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "cef0423c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Olly\n",
      "Jon Snow\n",
      "Daenerys Targaryen\n",
      "Golden Company soldier\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH p = (:Character)-[:KILLED * wShortest (e,v | e.count) kill_count]->(:Character)\n",
    "    RETURN nodes(p) AS kill_list, kill_count\n",
    "    ORDER BY kill_count DESC\n",
    "    LIMIT 1;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    for kill in result[\"kill_list\"]:\n",
    "        print(kill.properties[\"name\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "418bbd38",
   "metadata": {},
   "source": [
    "That's it! Hopefully, you gained new knowledge and enjoyed this workshop. If you have any questions, don't hesitate to contact me directly or [join our Discord server](https://discord.gg/memgraph)."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}