L1 E1 - Step 1 & 2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercise 1 -  Sakila Star Schema & ETL  \n",
    "\n",
    "All the database tables in this demo are based on public database samples and transformations\n",
    "- `Sakila` is a sample database created by `MySql` [Link](https://video.udacity-data.com/topher/2021/August/61120e06_pagila-3nf/pagila-3nf.png)\n",
    "- The postgresql version of it is called `Pagila` [Link](https://github.com/devrimgunduz/pagila)\n",
    "- The facts and dimension tables design is based on O'Reilly's public dimensional modelling tutorial schema [Link](https://video.udacity-data.com/topher/2021/August/61120d38_pagila-star/pagila-star.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# STEP0: Using ipython-sql\n",
    "\n",
    "- Load ipython-sql: `%load_ext sql`\n",
    "\n",
    "- To execute SQL queries you write one of the following atop of your cell: \n",
    "    - `%sql`\n",
    "        - For a one-liner SQL query\n",
    "        - You can access a python var using `$`    \n",
    "    - `%%sql`\n",
    "        - For a multi-line SQL query\n",
    "        - You can **NOT** access a python var using `$`\n",
    "\n",
    "\n",
    "- Running a connection string like:\n",
    "`postgresql://postgres:postgres@db:5432/pagila` connects to the database\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# STEP1 : Connect to the local database where Pagila is loaded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  1.1 Create the pagila db and fill it with data\n",
    "- Adding `\"!\"` at the beginning of a jupyter cell runs a command in a shell, i.e. we are not running python code but we are running the `createdb` and `psql` postgresql commmand-line utilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " setval \r\n",
      "--------\r\n",
      "    200\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "    605\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "     16\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "    600\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "    109\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "    599\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "   1000\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "   4581\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "      6\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "  32098\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "  16049\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "      2\r\n",
      "(1 row)\r\n",
      "\r\n",
      " setval \r\n",
      "--------\r\n",
      "      2\r\n",
      "(1 row)\r\n",
      "\r\n"
     ]
    }
   ],
   "source": [
    "!PGPASSWORD=student createdb -h 127.0.0.1 -U student pagila\n",
    "!PGPASSWORD=student psql -q -h 127.0.0.1 -U student -d pagila -f Data/pagila-schema.sql\n",
    "!PGPASSWORD=student psql -q -h 127.0.0.1 -U student -d pagila -f Data/pagila-data.sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 Connect to the newly created db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-11T17:33:34.685025Z",
     "start_time": "2019-02-11T17:33:34.514916Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://student:student@127.0.0.1:5432/pagila\n"
     ]
    }
   ],
   "source": [
    "DB_ENDPOINT = \"127.0.0.1\"\n",
    "DB = 'pagila'\n",
    "DB_USER = 'student'\n",
    "DB_PASSWORD = 'student'\n",
    "DB_PORT = '5432'\n",
    "\n",
    "# postgresql://username:password@host:port/database\n",
    "conn_string = \"postgresql://{}:{}@{}:{}/{}\" \\\n",
    "                        .format(DB_USER, DB_PASSWORD, DB_ENDPOINT, DB_PORT, DB)\n",
    "\n",
    "print(conn_string)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-11T17:33:40.572334Z",
     "start_time": "2019-02-11T17:33:39.203283Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: student@pagila'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $conn_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# STEP2 : Explore the  3NF Schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"./pagila-3nf.png\" width=\"50%\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 How much? What data sizes are we looking at?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n",
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n",
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n",
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n",
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n",
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n",
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n",
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n",
      "nFilms\t\t= 1000\n",
      "nCustomers\t= 599\n",
      "nRentals\t= 16044\n",
      "nPayment\t= 16049\n",
      "nStaff\t\t= 2\n",
      "nStores\t\t= 2\n",
      "nCities\t\t= 600\n",
      "nCountry\t\t= 109\n"
     ]
    }
   ],
   "source": [
    "nStores = %sql select count(*) from store;\n",
    "nFilms = %sql select count(*) from film;\n",
    "nCustomers = %sql select count(*) from customer;\n",
    "nRentals = %sql select count(*) from rental;\n",
    "nPayment = %sql select count(*) from payment;\n",
    "nStaff = %sql select count(*) from staff;\n",
    "nCity = %sql select count(*) from city;\n",
    "nCountry = %sql select count(*) from country;\n",
    "\n",
    "print(\"nFilms\\t\\t=\", nFilms[0][0])\n",
    "print(\"nCustomers\\t=\", nCustomers[0][0])\n",
    "print(\"nRentals\\t=\", nRentals[0][0])\n",
    "print(\"nPayment\\t=\", nPayment[0][0])\n",
    "print(\"nStaff\\t\\t=\", nStaff[0][0])\n",
    "print(\"nStores\\t\\t=\", nStores[0][0])\n",
    "print(\"nCities\\t\\t=\", nCity[0][0])\n",
    "print(\"nCountry\\t\\t=\", nCountry[0][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 When? What time period are we talking about?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>start</th>\n",
       "        <th>end</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-01-24 21:21:56.996577+00:00</td>\n",
       "        <td>2017-05-14 13:44:29.996577+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2017, 1, 24, 21, 21, 56, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), datetime.datetime(2017, 5, 14, 13, 44, 29, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select min(payment_date) as start, max(payment_date) as end from payment;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 Where? Where do events in this database occur?\n",
    "TODO: Write a query that displays the number of addresses by district in the address table. Limit the table to the top 10 districts. Your results should match the table below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>district</th>\n",
       "        <th>n</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shandong</td>\n",
       "        <td>3237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>England</td>\n",
       "        <td>2974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>So Paulo</td>\n",
       "        <td>2952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>West Bengali</td>\n",
       "        <td>2623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Buenos Aires</td>\n",
       "        <td>2572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uttar Pradesh</td>\n",
       "        <td>2462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>California</td>\n",
       "        <td>2444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Southern Tagalog</td>\n",
       "        <td>1931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tamil Nadu</td>\n",
       "        <td>1807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hubei</td>\n",
       "        <td>1790</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shandong', 3237),\n",
       " ('England', 2974),\n",
       " ('So Paulo', 2952),\n",
       " ('West Bengali', 2623),\n",
       " ('Buenos Aires', 2572),\n",
       " ('Uttar Pradesh', 2462),\n",
       " ('California', 2444),\n",
       " ('Southern Tagalog', 1931),\n",
       " ('Tamil Nadu', 1807),\n",
       " ('Hubei', 1790)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select district, sum(city_id) as n from address \n",
    "group by district \n",
    "order by n desc \n",
    "limit 10;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"p-Widget jp-RenderedHTMLCommon jp-RenderedHTML jp-OutputArea-output jp-OutputArea-executeResult\" data-mime-type=\"text/html\"><table>\n",
    "    <tbody><tr>\n",
    "        <th>district</th>\n",
    "        <th>n</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Shandong</td>\n",
    "        <td>3237</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>England</td>\n",
    "        <td>2974</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>So Paulo</td>\n",
    "        <td>2952</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>West Bengali</td>\n",
    "        <td>2623</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Buenos Aires</td>\n",
    "        <td>2572</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Uttar Pradesh</td>\n",
    "        <td>2462</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>California</td>\n",
    "        <td>2444</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Southern Tagalog</td>\n",
    "        <td>1931</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Tamil Nadu</td>\n",
    "        <td>1807</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Hubei</td>\n",
    "        <td>1790</td>\n",
    "    </tr>\n",
    "</tbody></table></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# STEP3: Perform some simple data analysis\n",
    "\n",
    "Start by connecting to the database by running the cells below. If you are coming back to this exercise, then uncomment and run the first cell to recreate the database. If you recently completed steps 1 and 2, then skip to the second cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !PGPASSWORD=student createdb -h 127.0.0.1 -U student pagila\n",
    "# !PGPASSWORD=student psql -q -h 127.0.0.1 -U student -d pagila -f Data/pagila-schema.sql\n",
    "# !PGPASSWORD=student psql -q -h 127.0.0.1 -U student -d pagila -f Data/pagila-data.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n",
      "postgresql://student:student@127.0.0.1:5432/pagila\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: student@pagila'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "\n",
    "DB_ENDPOINT = \"127.0.0.1\"\n",
    "DB = 'pagila'\n",
    "DB_USER = 'student'\n",
    "DB_PASSWORD = 'student'\n",
    "DB_PORT = '5432'\n",
    "\n",
    "# postgresql://username:password@host:port/database\n",
    "conn_string = \"postgresql://{}:{}@{}:{}/{}\" \\\n",
    "                        .format(DB_USER, DB_PASSWORD, DB_ENDPOINT, DB_PORT, DB)\n",
    "\n",
    "print(conn_string)\n",
    "%sql $conn_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3NF - Entity Relationship Diagram\n",
    "\n",
    "<img src=\"./pagila-3nf.png\" width=\"50%\"/>\n",
    "\n",
    "## 3.1 Insight 1:   Top Grossing Movies \n",
    "- Payments amounts are in table `payment`\n",
    "- Movies are in table `film`\n",
    "- They are not directly linked, `payment` refers to a `rental`, `rental` refers to an `inventory` item and `inventory` item refers to a `film`\n",
    "- `payment` &rarr; `rental` &rarr; `inventory` &rarr; `film`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1.1 Films"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>film_id</th>\n",
       "        <th>title</th>\n",
       "        <th>release_year</th>\n",
       "        <th>rental_rate</th>\n",
       "        <th>rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>ACADEMY DINOSAUR</td>\n",
       "        <td>2006</td>\n",
       "        <td>0.99</td>\n",
       "        <td>PG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>ACE GOLDFINGER</td>\n",
       "        <td>2006</td>\n",
       "        <td>4.99</td>\n",
       "        <td>G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>ADAPTATION HOLES</td>\n",
       "        <td>2006</td>\n",
       "        <td>2.99</td>\n",
       "        <td>NC-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>AFFAIR PREJUDICE</td>\n",
       "        <td>2006</td>\n",
       "        <td>2.99</td>\n",
       "        <td>G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>AFRICAN EGG</td>\n",
       "        <td>2006</td>\n",
       "        <td>2.99</td>\n",
       "        <td>G</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'ACADEMY DINOSAUR', 2006, Decimal('0.99'), 'PG'),\n",
       " (2, 'ACE GOLDFINGER', 2006, Decimal('4.99'), 'G'),\n",
       " (3, 'ADAPTATION HOLES', 2006, Decimal('2.99'), 'NC-17'),\n",
       " (4, 'AFFAIR PREJUDICE', 2006, Decimal('2.99'), 'G'),\n",
       " (5, 'AFRICAN EGG', 2006, Decimal('2.99'), 'G')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select film_id, title, release_year, rental_rate, rating  from film limit 5;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1.2 Payments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>payment_id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>staff_id</th>\n",
       "        <th>rental_id</th>\n",
       "        <th>amount</th>\n",
       "        <th>payment_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16050</td>\n",
       "        <td>269</td>\n",
       "        <td>2</td>\n",
       "        <td>7</td>\n",
       "        <td>1.99</td>\n",
       "        <td>2017-01-24 21:40:19.996577+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16051</td>\n",
       "        <td>269</td>\n",
       "        <td>1</td>\n",
       "        <td>98</td>\n",
       "        <td>0.99</td>\n",
       "        <td>2017-01-25 15:16:50.996577+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16052</td>\n",
       "        <td>269</td>\n",
       "        <td>2</td>\n",
       "        <td>678</td>\n",
       "        <td>6.99</td>\n",
       "        <td>2017-01-28 21:44:14.996577+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16053</td>\n",
       "        <td>269</td>\n",
       "        <td>2</td>\n",
       "        <td>703</td>\n",
       "        <td>0.99</td>\n",
       "        <td>2017-01-29 00:58:02.996577+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16054</td>\n",
       "        <td>269</td>\n",
       "        <td>1</td>\n",
       "        <td>750</td>\n",
       "        <td>4.99</td>\n",
       "        <td>2017-01-29 08:10:06.996577+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(16050, 269, 2, 7, Decimal('1.99'), datetime.datetime(2017, 1, 24, 21, 40, 19, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
       " (16051, 269, 1, 98, Decimal('0.99'), datetime.datetime(2017, 1, 25, 15, 16, 50, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
       " (16052, 269, 2, 678, Decimal('6.99'), datetime.datetime(2017, 1, 28, 21, 44, 14, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
       " (16053, 269, 2, 703, Decimal('0.99'), datetime.datetime(2017, 1, 29, 0, 58, 2, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
       " (16054, 269, 1, 750, Decimal('4.99'), datetime.datetime(2017, 1, 29, 8, 10, 6, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)))]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from payment limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1.3 Inventory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>inventory_id</th>\n",
       "        <th>film_id</th>\n",
       "        <th>store_id</th>\n",
       "        <th>last_update</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>2017-02-15 10:09:17+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>2017-02-15 10:09:17+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>2017-02-15 10:09:17+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>2017-02-15 10:09:17+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>2017-02-15 10:09:17+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 1, datetime.datetime(2017, 2, 15, 10, 9, 17, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
       " (2, 1, 1, datetime.datetime(2017, 2, 15, 10, 9, 17, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
       " (3, 1, 1, datetime.datetime(2017, 2, 15, 10, 9, 17, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
       " (4, 1, 1, datetime.datetime(2017, 2, 15, 10, 9, 17, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
       " (5, 1, 2, datetime.datetime(2017, 2, 15, 10, 9, 17, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)))]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from inventory limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1.4 Get the movie of every payment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "        <th>amount</th>\n",
       "        <th>payment_date</th>\n",
       "        <th>customer_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SWARM GOLD</td>\n",
       "        <td>1.99</td>\n",
       "        <td>2017-01-24 21:40:19.996577+00:00</td>\n",
       "        <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PACKER MADIGAN</td>\n",
       "        <td>0.99</td>\n",
       "        <td>2017-01-25 15:16:50.996577+00:00</td>\n",
       "        <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOMETHING DUCK</td>\n",
       "        <td>6.99</td>\n",
       "        <td>2017-01-28 21:44:14.996577+00:00</td>\n",
       "        <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DRACULA CRYSTAL</td>\n",
       "        <td>0.99</td>\n",
       "        <td>2017-01-29 00:58:02.996577+00:00</td>\n",
       "        <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CLOSER BANG</td>\n",
       "        <td>4.99</td>\n",
       "        <td>2017-01-29 08:10:06.996577+00:00</td>\n",
       "        <td>269</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SWARM GOLD', Decimal('1.99'), datetime.datetime(2017, 1, 24, 21, 40, 19, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 269),\n",
       " ('PACKER MADIGAN', Decimal('0.99'), datetime.datetime(2017, 1, 25, 15, 16, 50, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 269),\n",
       " ('SOMETHING DUCK', Decimal('6.99'), datetime.datetime(2017, 1, 28, 21, 44, 14, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 269),\n",
       " ('DRACULA CRYSTAL', Decimal('0.99'), datetime.datetime(2017, 1, 29, 0, 58, 2, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 269),\n",
       " ('CLOSER BANG', Decimal('4.99'), datetime.datetime(2017, 1, 29, 8, 10, 6, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 269)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT f.title, p.amount, p.payment_date, p.customer_id                                            \n",
    "FROM payment p\n",
    "JOIN rental r    ON ( p.rental_id = r.rental_id )\n",
    "JOIN inventory i ON ( r.inventory_id = i.inventory_id )\n",
    "JOIN film f ON ( i.film_id = f.film_id)\n",
    "limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1.5 sum movie rental revenue\n",
    "TODO: Write a query that displays the amount of revenue from each title. Limit the results to the top 10 grossing titles. Your results should match the table below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "        <th>revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TELEGRAPH VOYAGE</td>\n",
       "        <td>231.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WIFE TURN</td>\n",
       "        <td>223.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ZORRO ARK</td>\n",
       "        <td>214.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GOODFELLAS SALUTE</td>\n",
       "        <td>209.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SATURDAY LAMBS</td>\n",
       "        <td>204.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TITANS JERK</td>\n",
       "        <td>201.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TORQUE BOUND</td>\n",
       "        <td>198.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HARRY IDAHO</td>\n",
       "        <td>195.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INNOCENT USUAL</td>\n",
       "        <td>191.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HUSTLER PARTY</td>\n",
       "        <td>190.78</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('TELEGRAPH VOYAGE', Decimal('231.73')),\n",
       " ('WIFE TURN', Decimal('223.69')),\n",
       " ('ZORRO ARK', Decimal('214.69')),\n",
       " ('GOODFELLAS SALUTE', Decimal('209.69')),\n",
       " ('SATURDAY LAMBS', Decimal('204.72')),\n",
       " ('TITANS JERK', Decimal('201.71')),\n",
       " ('TORQUE BOUND', Decimal('198.72')),\n",
       " ('HARRY IDAHO', Decimal('195.70')),\n",
       " ('INNOCENT USUAL', Decimal('191.74')),\n",
       " ('HUSTLER PARTY', Decimal('190.78'))]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT f.title, sum(p.amount) as revenue                                            \n",
    "FROM payment p\n",
    "JOIN rental r    ON ( p.rental_id = r.rental_id )\n",
    "JOIN inventory i ON ( r.inventory_id = i.inventory_id )\n",
    "JOIN film f ON ( i.film_id = f.film_id)\n",
    "GROUP BY f.title\n",
    "ORDER BY revenue DESC\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"p-Widget jp-RenderedHTMLCommon jp-RenderedHTML jp-OutputArea-output jp-OutputArea-executeResult\" data-mime-type=\"text/html\"><table>\n",
    "    <tbody><tr>\n",
    "        <th>title</th>\n",
    "        <th>revenue</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>TELEGRAPH VOYAGE</td>\n",
    "        <td>231.73</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>WIFE TURN</td>\n",
    "        <td>223.69</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>ZORRO ARK</td>\n",
    "        <td>214.69</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>GOODFELLAS SALUTE</td>\n",
    "        <td>209.69</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>SATURDAY LAMBS</td>\n",
    "        <td>204.72</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>TITANS JERK</td>\n",
    "        <td>201.71</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>TORQUE BOUND</td>\n",
    "        <td>198.72</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>HARRY IDAHO</td>\n",
    "        <td>195.70</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>INNOCENT USUAL</td>\n",
    "        <td>191.74</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>HUSTLER PARTY</td>\n",
    "        <td>190.78</td>\n",
    "    </tr>\n",
    "</tbody></table></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2 Insight 2:   Top grossing cities \n",
    "- Payments amounts are in table `payment`\n",
    "- Cities are in table `cities`\n",
    "- `payment` &rarr; `customer` &rarr; `address` &rarr; `city`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2.1 Get the city of each payment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>customer_id</th>\n",
       "        <th>rental_id</th>\n",
       "        <th>amount</th>\n",
       "        <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>130</td>\n",
       "        <td>1</td>\n",
       "        <td>2.99</td>\n",
       "        <td>guas Lindas de Gois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>459</td>\n",
       "        <td>2</td>\n",
       "        <td>2.99</td>\n",
       "        <td>Qomsheh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>408</td>\n",
       "        <td>3</td>\n",
       "        <td>3.99</td>\n",
       "        <td>Jaffna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>333</td>\n",
       "        <td>4</td>\n",
       "        <td>4.99</td>\n",
       "        <td>Baku</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>222</td>\n",
       "        <td>5</td>\n",
       "        <td>6.99</td>\n",
       "        <td>Jaroslavl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>549</td>\n",
       "        <td>6</td>\n",
       "        <td>0.99</td>\n",
       "        <td>Santiago de Compostela</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>269</td>\n",
       "        <td>7</td>\n",
       "        <td>1.99</td>\n",
       "        <td>Salinas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>239</td>\n",
       "        <td>8</td>\n",
       "        <td>4.99</td>\n",
       "        <td>Ciomas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>126</td>\n",
       "        <td>9</td>\n",
       "        <td>4.99</td>\n",
       "        <td>Po</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>399</td>\n",
       "        <td>10</td>\n",
       "        <td>5.99</td>\n",
       "        <td>Okara</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(130, 1, Decimal('2.99'), 'guas Lindas de Gois'),\n",
       " (459, 2, Decimal('2.99'), 'Qomsheh'),\n",
       " (408, 3, Decimal('3.99'), 'Jaffna'),\n",
       " (333, 4, Decimal('4.99'), 'Baku'),\n",
       " (222, 5, Decimal('6.99'), 'Jaroslavl'),\n",
       " (549, 6, Decimal('0.99'), 'Santiago de Compostela'),\n",
       " (269, 7, Decimal('1.99'), 'Salinas'),\n",
       " (239, 8, Decimal('4.99'), 'Ciomas'),\n",
       " (126, 9, Decimal('4.99'), 'Po'),\n",
       " (399, 10, Decimal('5.99'), 'Okara')]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT p.customer_id, p.rental_id, p.amount, ci.city                            \n",
    "FROM payment p\n",
    "JOIN customer c  ON ( p.customer_id = c.customer_id )\n",
    "JOIN address a ON ( c.address_id = a.address_id )\n",
    "JOIN city ci ON ( a.city_id = ci.city_id )\n",
    "order by p.payment_date\n",
    "limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2.2 Top grossing cities\n",
    "TODO: Write a query that returns the total amount of revenue by city as measured by the `amount` variable in the `payment` table. Limit the results to the top 10 cities. Your result should match the table below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cape Coral</td>\n",
       "        <td>221.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint-Denis</td>\n",
       "        <td>216.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Aurora</td>\n",
       "        <td>198.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Molodetno</td>\n",
       "        <td>195.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Apeldoorn</td>\n",
       "        <td>194.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Santa Brbara dOeste</td>\n",
       "        <td>194.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Qomsheh</td>\n",
       "        <td>186.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>180.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ourense (Orense)</td>\n",
       "        <td>177.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bijapur</td>\n",
       "        <td>175.61</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Cape Coral', Decimal('221.55')),\n",
       " ('Saint-Denis', Decimal('216.54')),\n",
       " ('Aurora', Decimal('198.50')),\n",
       " ('Molodetno', Decimal('195.58')),\n",
       " ('Apeldoorn', Decimal('194.61')),\n",
       " ('Santa Brbara dOeste', Decimal('194.61')),\n",
       " ('Qomsheh', Decimal('186.62')),\n",
       " ('London', Decimal('180.52')),\n",
       " ('Ourense (Orense)', Decimal('177.60')),\n",
       " ('Bijapur', Decimal('175.61'))]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ci.city, sum(p.amount) as revenue                           \n",
    "FROM payment p\n",
    "JOIN customer c  ON ( p.customer_id = c.customer_id )\n",
    "JOIN address a ON ( c.address_id = a.address_id )\n",
    "JOIN city ci ON ( a.city_id = ci.city_id )\n",
    "GROUP BY ci.city\n",
    "ORDER BY revenue DESC\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"p-Widget jp-RenderedHTMLCommon jp-RenderedHTML jp-mod-trusted jp-OutputArea-output jp-OutputArea-executeResult\" data-mime-type=\"text/html\"><table>\n",
    "    <tbody><tr>\n",
    "        <th>city</th>\n",
    "        <th>revenue</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Cape Coral</td>\n",
    "        <td>221.55</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Saint-Denis</td>\n",
    "        <td>216.54</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Aurora</td>\n",
    "        <td>198.50</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Molodetno</td>\n",
    "        <td>195.58</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Apeldoorn</td>\n",
    "        <td>194.61</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Santa Brbara dOeste</td>\n",
    "        <td>194.61</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Qomsheh</td>\n",
    "        <td>186.62</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>London</td>\n",
    "        <td>180.52</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Ourense (Orense)</td>\n",
    "        <td>177.60</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Bijapur</td>\n",
    "        <td>175.61</td>\n",
    "    </tr>\n",
    "</tbody></table></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.3 Insight 3 : Revenue of a movie by customer city and by month "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.1 Total revenue by month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>revenue</th>\n",
       "        <th>month</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28559.46</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23886.56</td>\n",
       "        <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9631.88</td>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4824.43</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>514.18</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('28559.46'), 4.0),\n",
       " (Decimal('23886.56'), 3.0),\n",
       " (Decimal('9631.88'), 2.0),\n",
       " (Decimal('4824.43'), 1.0),\n",
       " (Decimal('514.18'), 5.0)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT sum(p.amount) as revenue, EXTRACT(month FROM p.payment_date) as month\n",
    "from payment p\n",
    "group by month\n",
    "order by revenue desc\n",
    "limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.2 Each movie by customer city and by month (data cube)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "        <th>amount</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>city</th>\n",
       "        <th>payment_date</th>\n",
       "        <th>month</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BLANKET BEVERLY</td>\n",
       "        <td>2.99</td>\n",
       "        <td>130</td>\n",
       "        <td>guas Lindas de Gois</td>\n",
       "        <td>2017-01-24 21:21:56.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FREAKY POCUS</td>\n",
       "        <td>2.99</td>\n",
       "        <td>459</td>\n",
       "        <td>Qomsheh</td>\n",
       "        <td>2017-01-24 21:22:59.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GRADUATE LORD</td>\n",
       "        <td>3.99</td>\n",
       "        <td>408</td>\n",
       "        <td>Jaffna</td>\n",
       "        <td>2017-01-24 21:32:05.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOVE SUICIDES</td>\n",
       "        <td>4.99</td>\n",
       "        <td>333</td>\n",
       "        <td>Baku</td>\n",
       "        <td>2017-01-24 21:33:07.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>IDOLS SNATCHERS</td>\n",
       "        <td>6.99</td>\n",
       "        <td>222</td>\n",
       "        <td>Jaroslavl</td>\n",
       "        <td>2017-01-24 21:33:47.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MYSTIC TRUMAN</td>\n",
       "        <td>0.99</td>\n",
       "        <td>549</td>\n",
       "        <td>Santiago de Compostela</td>\n",
       "        <td>2017-01-24 21:36:33.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SWARM GOLD</td>\n",
       "        <td>1.99</td>\n",
       "        <td>269</td>\n",
       "        <td>Salinas</td>\n",
       "        <td>2017-01-24 21:40:19.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LAWLESS VISION</td>\n",
       "        <td>4.99</td>\n",
       "        <td>239</td>\n",
       "        <td>Ciomas</td>\n",
       "        <td>2017-01-24 22:00:12.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MATRIX SNOWMAN</td>\n",
       "        <td>4.99</td>\n",
       "        <td>126</td>\n",
       "        <td>Po</td>\n",
       "        <td>2017-01-24 22:29:06.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HANGING DEEP</td>\n",
       "        <td>5.99</td>\n",
       "        <td>399</td>\n",
       "        <td>Okara</td>\n",
       "        <td>2017-01-24 22:30:47.996577+00:00</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('BLANKET BEVERLY', Decimal('2.99'), 130, 'guas Lindas de Gois', datetime.datetime(2017, 1, 24, 21, 21, 56, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('FREAKY POCUS', Decimal('2.99'), 459, 'Qomsheh', datetime.datetime(2017, 1, 24, 21, 22, 59, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('GRADUATE LORD', Decimal('3.99'), 408, 'Jaffna', datetime.datetime(2017, 1, 24, 21, 32, 5, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('LOVE SUICIDES', Decimal('4.99'), 333, 'Baku', datetime.datetime(2017, 1, 24, 21, 33, 7, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('IDOLS SNATCHERS', Decimal('6.99'), 222, 'Jaroslavl', datetime.datetime(2017, 1, 24, 21, 33, 47, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('MYSTIC TRUMAN', Decimal('0.99'), 549, 'Santiago de Compostela', datetime.datetime(2017, 1, 24, 21, 36, 33, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('SWARM GOLD', Decimal('1.99'), 269, 'Salinas', datetime.datetime(2017, 1, 24, 21, 40, 19, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('LAWLESS VISION', Decimal('4.99'), 239, 'Ciomas', datetime.datetime(2017, 1, 24, 22, 0, 12, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('MATRIX SNOWMAN', Decimal('4.99'), 126, 'Po', datetime.datetime(2017, 1, 24, 22, 29, 6, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0),\n",
       " ('HANGING DEEP', Decimal('5.99'), 399, 'Okara', datetime.datetime(2017, 1, 24, 22, 30, 47, 996577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1.0)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT f.title, p.amount, p.customer_id, ci.city, p.payment_date,EXTRACT(month FROM p.payment_date) as month\n",
    "FROM payment p\n",
    "JOIN rental r    ON ( p.rental_id = r.rental_id )\n",
    "JOIN inventory i ON ( r.inventory_id = i.inventory_id )\n",
    "JOIN film f ON ( i.film_id = f.film_id)\n",
    "JOIN customer c  ON ( p.customer_id = c.customer_id )\n",
    "JOIN address a ON ( c.address_id = a.address_id )\n",
    "JOIN city ci ON ( a.city_id = ci.city_id )\n",
    "order by p.payment_date\n",
    "limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.3 Sum of revenue of each movie by customer city and by month\n",
    "\n",
    "TODO: Write a query that returns the total amount of revenue for each movie by customer city and by month. Limit the results to the top 10 movies. Your result should match the table below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "        <th>city</th>\n",
       "        <th>month</th>\n",
       "        <th>revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SHOW LORD</td>\n",
       "        <td>Mannheim</td>\n",
       "        <td>1.0</td>\n",
       "        <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AMERICAN CIRCUS</td>\n",
       "        <td>Callao</td>\n",
       "        <td>1.0</td>\n",
       "        <td>10.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CASUALTIES ENCINO</td>\n",
       "        <td>Warren</td>\n",
       "        <td>1.0</td>\n",
       "        <td>10.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TELEGRAPH VOYAGE</td>\n",
       "        <td>Naala-Porto</td>\n",
       "        <td>1.0</td>\n",
       "        <td>10.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>KISSING DOLLS</td>\n",
       "        <td>Toulon</td>\n",
       "        <td>1.0</td>\n",
       "        <td>10.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MILLION ACE</td>\n",
       "        <td>Bergamo</td>\n",
       "        <td>1.0</td>\n",
       "        <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TITANS JERK</td>\n",
       "        <td>Kimberley</td>\n",
       "        <td>1.0</td>\n",
       "        <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DARKO DORADO</td>\n",
       "        <td>Bhilwara</td>\n",
       "        <td>1.0</td>\n",
       "        <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SUNRISE LEAGUE</td>\n",
       "        <td>Nagareyama</td>\n",
       "        <td>1.0</td>\n",
       "        <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MILLION ACE</td>\n",
       "        <td>Gaziantep</td>\n",
       "        <td>1.0</td>\n",
       "        <td>9.99</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SHOW LORD', 'Mannheim', 1.0, Decimal('11.99')),\n",
       " ('AMERICAN CIRCUS', 'Callao', 1.0, Decimal('10.99')),\n",
       " ('CASUALTIES ENCINO', 'Warren', 1.0, Decimal('10.99')),\n",
       " ('TELEGRAPH VOYAGE', 'Naala-Porto', 1.0, Decimal('10.99')),\n",
       " ('KISSING DOLLS', 'Toulon', 1.0, Decimal('10.99')),\n",
       " ('MILLION ACE', 'Bergamo', 1.0, Decimal('9.99')),\n",
       " ('TITANS JERK', 'Kimberley', 1.0, Decimal('9.99')),\n",
       " ('DARKO DORADO', 'Bhilwara', 1.0, Decimal('9.99')),\n",
       " ('SUNRISE LEAGUE', 'Nagareyama', 1.0, Decimal('9.99')),\n",
       " ('MILLION ACE', 'Gaziantep', 1.0, Decimal('9.99'))]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT f.title, ci.city, EXTRACT(month FROM p.payment_date) as month, sum(p.amount) as revenue\n",
    "FROM payment p\n",
    "JOIN rental r    ON ( p.rental_id = r.rental_id )\n",
    "JOIN inventory i ON ( r.inventory_id = i.inventory_id )\n",
    "JOIN film f ON ( i.film_id = f.film_id)\n",
    "JOIN customer c  ON ( p.customer_id = c.customer_id )\n",
    "JOIN address a ON ( c.address_id = a.address_id )\n",
    "JOIN city ci ON ( a.city_id = ci.city_id )\n",
    "GROUP BY f.title,ci.city,month\n",
    "ORDER BY month ASC, revenue DESC\n",
    "limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"p-Widget jp-RenderedHTMLCommon jp-RenderedHTML jp-mod-trusted jp-OutputArea-output jp-OutputArea-executeResult\" data-mime-type=\"text/html\"><table>\n",
    "    <tbody><tr>\n",
    "        <th>title</th>\n",
    "        <th>city</th>\n",
    "        <th>month</th>\n",
    "        <th>revenue</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>SHOW LORD</td>\n",
    "        <td>Mannheim</td>\n",
    "        <td>1.0</td>\n",
    "        <td>11.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>AMERICAN CIRCUS</td>\n",
    "        <td>Callao</td>\n",
    "        <td>1.0</td>\n",
    "        <td>10.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>CASUALTIES ENCINO</td>\n",
    "        <td>Warren</td>\n",
    "        <td>1.0</td>\n",
    "        <td>10.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>TELEGRAPH VOYAGE</td>\n",
    "        <td>Naala-Porto</td>\n",
    "        <td>1.0</td>\n",
    "        <td>10.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>KISSING DOLLS</td>\n",
    "        <td>Toulon</td>\n",
    "        <td>1.0</td>\n",
    "        <td>10.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>MILLION ACE</td>\n",
    "        <td>Bergamo</td>\n",
    "        <td>1.0</td>\n",
    "        <td>9.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>TITANS JERK</td>\n",
    "        <td>Kimberley</td>\n",
    "        <td>1.0</td>\n",
    "        <td>9.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>DARKO DORADO</td>\n",
    "        <td>Bhilwara</td>\n",
    "        <td>1.0</td>\n",
    "        <td>9.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>SUNRISE LEAGUE</td>\n",
    "        <td>Nagareyama</td>\n",
    "        <td>1.0</td>\n",
    "        <td>9.99</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>MILLION ACE</td>\n",
    "        <td>Gaziantep</td>\n",
    "        <td>1.0</td>\n",
    "        <td>9.99</td>\n",
    "    </tr>\n",
    "</tbody></table></div>"
   ]
  }
 ],
 "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.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}