{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b6612db4",
   "metadata": {},
   "source": [
    "# Answer Key\n",
    "\n",
    "Below are the answers to the exercises given in the companion notebooks but first let's setup the visualization options.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16995693-2a2c-42bf-8461-a32062ebaac3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%graph_notebook_vis_options\n",
    "{\n",
    "  \"groups\": {    \n",
    "    \"person\": {\n",
    "      \"color\": \"#9ac7bf\"\n",
    "    },\n",
    "    \"review\": {\n",
    "      \"color\": \"#f8cecc\"\n",
    "    },\n",
    "    \"city\": {\n",
    "      \"color\": \"#d5e8d4\"\n",
    "    },\n",
    "    \"state\": {\n",
    "      \"color\": \"#dae8fc\"\n",
    "    },\n",
    "    \"review_rating\": {\n",
    "      \"color\": \"#e1d5e7\"\n",
    "    },\n",
    "    \"restaurant\": {\n",
    "      \"color\": \"#ffe6cc\"\n",
    "    },\n",
    "    \"cusine\": {\n",
    "      \"color\": \"#fff2cc\"\n",
    "    }\n",
    "  }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e76dc888-a0c7-4ed1-bac5-52e96578dfea",
   "metadata": {},
   "source": [
    "## 01-Basic-Read-Queries\n",
    "\n",
    "### Exercise B-1 Find the first name of Dave's friends"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28840d3b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%oc \n",
    "\n",
    "MATCH (:person {first_name: 'Dave'})-[:friends]->(f:person) \n",
    "RETURN f.first_name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78ec7c0d",
   "metadata": {},
   "source": [
    "### Exercise B-2 Find the first name of the friends of Dave's friends"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65e46798",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%oc \n",
    "\n",
    "MATCH (:person {first_name:'Dave'})-[:friends]->()-[:friends]->(p:person) \n",
    "RETURN DISTINCT p.first_name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17a15554",
   "metadata": {},
   "source": [
    "### Exercise B-3 Find out how the friends of Dave's friends are connected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cec9a543",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "\n",
    "MATCH p=(d:person {first_name:'Dave'})-[:friends]->()-[:friends]->(:person) \n",
    "RETURN p"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d193248",
   "metadata": {},
   "source": [
    "### Exercise B-4  Which friends should we recommend for Dave?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "670edcbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "\n",
    "MATCH p=(d:person {first_name:'Dave'})-[:friends]->()-[:friends]->(foff:person) \n",
    "WHERE NOT (foff)-[:friends]->(d)\n",
    "RETURN DISTINCT foff.first_name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c222fc68",
   "metadata": {},
   "source": [
    "## 02-Variable-Length-Paths\n",
    "\n",
    "### Exercise VLP-1 Find the first name of Dave's friends"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2064f90",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "\n",
    "MATCH (:person {first_name:'Dave'})-[:friends*2]->(p:person) \n",
    "RETURN DISTINCT p.first_name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6f7229b",
   "metadata": {},
   "source": [
    "### Exercise VLP-2 Find all `person` nodes connected to Dave"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60a9d820",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "\n",
    "MATCH p=(:person {first_name:'Dave'})-[:friends*]->(:person) \n",
    "RETURN p"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "615c2be9",
   "metadata": {},
   "source": [
    "### Exercise VLP-3 Find if Dave and Denise are connected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd4b86e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "\n",
    "MATCH p=(:person {first_name:'Dave'})-[:friends*1..]-(:person {first_name:'Denise'})\n",
    "RETURN True \n",
    "LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62d52023",
   "metadata": {},
   "source": [
    "### Exercise VLP-4 Find all the ways Dave and Denise are connected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "044ea076",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "\n",
    "MATCH p=(:person {first_name:'Dave'})-[:friends*1..]-(:person {first_name:'Denise'})\n",
    "RETURN p "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76439a5a",
   "metadata": {},
   "source": [
    "## 03-Ordering-Functions-Grouping\n",
    "\n",
    "### Exercise G-1 What are the 3 highest restaurants?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43555803",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (r:restaurant)<-[:about]-(rev:review)\n",
    "WITH r, avg(rev.rating) AS avg_rating\n",
    "ORDER BY avg_rating DESC\n",
    "LIMIT 3\n",
    "MATCH (r)-[:serves]->(c:cuisine)\n",
    "RETURN r.name, c.name, avg_rating\n",
    "ORDER BY avg_rating Desc"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85285f40",
   "metadata": {},
   "source": [
    "### Exercise G-2 Find the top 3 highest rated restaurants in the city where Dave lives"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9726904",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (p:person {first_name: 'Dave'})-[:lives]->(:city)<-[:within]-(r:restaurant)<-[:about]-(v:review)\n",
    "WITH r, avg(v.rating) AS rating_average, p\n",
    "RETURN r.name AS name,\n",
    "r.address AS address, rating_average\n",
    "ORDER BY rating_average DESC \n",
    "LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d87c8a58",
   "metadata": {},
   "source": [
    "### Exercise G-3 What Mexican or Chinese restaurant near Dave that is the highest rated?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d43663c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (p:person {first_name: 'Dave'})-[:lives]->(:city)<-[:within]-(r:restaurant)-[:serves]->(c:cuisine) \n",
    "WHERE c.name IN ['Mexican', 'Chinese'] \n",
    "WITH r\n",
    "MATCH (r)<-[:about]-(v:review) \n",
    "WITH r, avg(v.rating) AS rating_average \n",
    "RETURN r.name AS name, \n",
    "    r.address AS address, rating_average\n",
    "ORDER BY rating_average DESC \n",
    "LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "323feeda",
   "metadata": {},
   "source": [
    "### Exercise G-4 What are the top 3 restaurants, recommended by his friends, where Dave lives? (Personalized Recommendation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b1f39a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (p:person {first_name: 'Dave'})-[:lives]->(c:city)\n",
    "MATCH (p)-[:friends]-()-[:wrote]-(v)-[:about]-\n",
    "(r:restaurant)-[:within]-(c) \n",
    "RETURN  r.name AS name, r.address AS address, avg(v.rating) as rating_average\n",
    "ORDER BY rating_average DESC\n",
    "LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "755abc7f",
   "metadata": {},
   "source": [
    "## 04-Creating-Updating-Delete-Queries\n",
    "\n",
    "###  Exercise M-1 Create a new person `Leonhard Euler`  and connect them to `Dave`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c04c4f37",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%oc\n",
    "CREATE (le:person {first_name: 'Leonhard', last_name: 'Euler'})\n",
    "WITH le\n",
    "MATCH (dave:person {first_name: 'Dave'})\n",
    "CREATE (le)-[:friends]->(dave)\n",
    "RETURN le"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "882bb492",
   "metadata": {},
   "source": [
    "### Exercise M-2 Upsert the a list of followers and add an edge to `Dave`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f323a03",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%oc\n",
    "WITH [{first_name: 'Taylor', last_name: 'Hall'},{first_name: 'Kelvin', last_name: 'Fernsby'},{first_name: 'Ian', last_name: 'Rochester'}] as followers\n",
    "\n",
    "UNWIND followers as f\n",
    "MERGE (n:person {first_name: f.first_name, last_name: f.last_name})-[r:follower]->(:person {first_name: 'Dave'})\n",
    "ON CREATE\n",
    "    SET r.creation='Created'\n",
    "ON MATCH\n",
    "    SET r.creation='Updated'\n",
    "RETURN r"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2643abc",
   "metadata": {},
   "source": [
    "### Exercise M-3 Delete all `followers` edges and remove any connected nodes with no other edges?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48acc3db",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%oc\n",
    "MATCH (a)-[r:followers]-()\n",
    "DELETE r\n",
    "WITH collect(DISTINCT a) as a, count(r) as edge_cnt\n",
    "UNWIND a as n\n",
    "MATCH (n)\n",
    "WHERE size((n)--()) = 0\n",
    "DELETE n\n",
    "WITH count(n) as node_cnt, edge_cnt\n",
    "RETURN node_cnt, edge_cnt"
   ]
  }
 ],
 "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.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}