Week_6b.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Week 6b - Data Analytics for Engineers "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Wednesday 21st of March 2018* "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Check Your Match + Early Start Poster Session Next Week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Check your Match event (28th of March 11:00 - 14:00) - overlaps with poster session\n",
    "\n",
    "# Therefore revised schedule: \n",
    "# - Extended Deadline GA2 (Tuesday 27th of March 15:00)\n",
    "# - Share Poster with Groupmates (by email) (Tuesday 27th of March 15:00)\n",
    "# - Submit Individual Qualitative Feedback - Using Online (Type)Form  (deadline: Wed. 28th of March 10:00)\n",
    "# - Group Discussion + Paper Rubrics (28th of March 10:15 - 11:30) - (early start + mandatory presence)\n",
    "\n",
    "# More details by email"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. SQL Challenges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Practice on paper (much more challenging!)\n",
    "# Step-by-step demo afterwards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "import shutil as sh\n",
    "%matplotlib inline\n",
    "\n",
    "# import data\n",
    "conn = sqlite3.connect('../Week6/datasets/movies/movies.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>mid</th>\n",
       "      <th>movie_title</th>\n",
       "      <th>Release Date</th>\n",
       "      <th>Production Budget</th>\n",
       "      <th>Domestic Gross</th>\n",
       "      <th>Worldwide Gross</th>\n",
       "      <th>genre</th>\n",
       "      <th>color</th>\n",
       "      <th>director_name</th>\n",
       "      <th>...</th>\n",
       "      <th>cast_total_facebook_likes</th>\n",
       "      <th>facenumber_in_poster</th>\n",
       "      <th>plot_keywords</th>\n",
       "      <th>num_user_for_reviews</th>\n",
       "      <th>language</th>\n",
       "      <th>country</th>\n",
       "      <th>content_rating</th>\n",
       "      <th>imdb_score</th>\n",
       "      <th>aspect_ratio</th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>2009-12-18</td>\n",
       "      <td>425000000</td>\n",
       "      <td>760507625</td>\n",
       "      <td>2783918982</td>\n",
       "      <td>Action</td>\n",
       "      <td>Color</td>\n",
       "      <td>James Cameron</td>\n",
       "      <td>...</td>\n",
       "      <td>4834.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>avatar|future|marine|native|paraplegic</td>\n",
       "      <td>3054.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>7.9</td>\n",
       "      <td>1.78</td>\n",
       "      <td>33000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Star Wars Ep. VII: The Force Awakens</td>\n",
       "      <td>2015-12-18</td>\n",
       "      <td>306000000</td>\n",
       "      <td>936662225</td>\n",
       "      <td>2058662225</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Pirates of the Caribbean: At World's End</td>\n",
       "      <td>2007-05-24</td>\n",
       "      <td>300000000</td>\n",
       "      <td>309420425</td>\n",
       "      <td>963420425</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Color</td>\n",
       "      <td>Gore Verbinski</td>\n",
       "      <td>...</td>\n",
       "      <td>48350.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>goddess|marriage ceremony|marriage proposal|pi...</td>\n",
       "      <td>1238.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>7.1</td>\n",
       "      <td>2.35</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Spectre</td>\n",
       "      <td>2015-11-06</td>\n",
       "      <td>300000000</td>\n",
       "      <td>200074175</td>\n",
       "      <td>879620923</td>\n",
       "      <td>Action</td>\n",
       "      <td>Color</td>\n",
       "      <td>Sam Mendes</td>\n",
       "      <td>...</td>\n",
       "      <td>11700.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>bomb|espionage|sequel|spy|terrorist</td>\n",
       "      <td>994.0</td>\n",
       "      <td>English</td>\n",
       "      <td>UK</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>6.8</td>\n",
       "      <td>2.35</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>The Dark Knight Rises</td>\n",
       "      <td>2012-07-20</td>\n",
       "      <td>275000000</td>\n",
       "      <td>448139099</td>\n",
       "      <td>1084439099</td>\n",
       "      <td>Action</td>\n",
       "      <td>Color</td>\n",
       "      <td>Christopher Nolan</td>\n",
       "      <td>...</td>\n",
       "      <td>106759.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deception|imprisonment|lawlessness|police offi...</td>\n",
       "      <td>2701.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>8.5</td>\n",
       "      <td>2.35</td>\n",
       "      <td>164000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  mid                               movie_title Release Date  \\\n",
       "0      0    0                                    Avatar   2009-12-18   \n",
       "1      1    1      Star Wars Ep. VII: The Force Awakens   2015-12-18   \n",
       "2      2    2  Pirates of the Caribbean: At World's End   2007-05-24   \n",
       "3      3    3                                   Spectre   2015-11-06   \n",
       "4      4    4                     The Dark Knight Rises   2012-07-20   \n",
       "\n",
       "   Production Budget  Domestic Gross  Worldwide Gross      genre  color  \\\n",
       "0          425000000       760507625       2783918982     Action  Color   \n",
       "1          306000000       936662225       2058662225  Adventure   None   \n",
       "2          300000000       309420425        963420425  Adventure  Color   \n",
       "3          300000000       200074175        879620923     Action  Color   \n",
       "4          275000000       448139099       1084439099     Action  Color   \n",
       "\n",
       "       director_name          ...           cast_total_facebook_likes  \\\n",
       "0      James Cameron          ...                              4834.0   \n",
       "1               None          ...                                 NaN   \n",
       "2     Gore Verbinski          ...                             48350.0   \n",
       "3         Sam Mendes          ...                             11700.0   \n",
       "4  Christopher Nolan          ...                            106759.0   \n",
       "\n",
       "   facenumber_in_poster                                      plot_keywords  \\\n",
       "0                   0.0             avatar|future|marine|native|paraplegic   \n",
       "1                   NaN                                               None   \n",
       "2                   0.0  goddess|marriage ceremony|marriage proposal|pi...   \n",
       "3                   1.0                bomb|espionage|sequel|spy|terrorist   \n",
       "4                   0.0  deception|imprisonment|lawlessness|police offi...   \n",
       "\n",
       "   num_user_for_reviews language  country  content_rating  imdb_score  \\\n",
       "0                3054.0  English      USA           PG-13         7.9   \n",
       "1                   NaN     None     None            None         NaN   \n",
       "2                1238.0  English      USA           PG-13         7.1   \n",
       "3                 994.0  English       UK           PG-13         6.8   \n",
       "4                2701.0  English      USA           PG-13         8.5   \n",
       "\n",
       "  aspect_ratio  movie_facebook_likes  \n",
       "0         1.78                 33000  \n",
       "1          NaN                  None  \n",
       "2         2.35                     0  \n",
       "3         2.35                 85000  \n",
       "4         2.35                164000  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test if database is correctly connected\n",
    "query = '''SELECT * \n",
    "           FROM movies '''\n",
    "\n",
    "pd.read_sql(query, conn).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Determine the movie titles with the biggest loss (worldwide gross - production budget) by genre of which the movie language was not French, German or Spanish and where the loss was at least 10 million dollar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "### STEP BY STEP SOLUTION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Determine the movie titles with the **biggest loss (worldwide gross - production budget)** by genre of which the movie language was not French, German or Spanish and where the loss was at least 10 million dollar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>\"Worldwide Gross\" - \"Production Budget\"</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avatar</td>\n",
       "      <td>2358918982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Star Wars Ep. VII: The Force Awakens</td>\n",
       "      <td>1752662225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pirates of the Caribbean: At World's End</td>\n",
       "      <td>663420425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Spectre</td>\n",
       "      <td>579620923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Dark Knight Rises</td>\n",
       "      <td>809439099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Lone Ranger</td>\n",
       "      <td>-14997885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>John Carter</td>\n",
       "      <td>7778100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Tangled</td>\n",
       "      <td>326581936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Spider-Man 3</td>\n",
       "      <td>636860230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>1158218722</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                movie_title  \\\n",
       "0                                    Avatar   \n",
       "1      Star Wars Ep. VII: The Force Awakens   \n",
       "2  Pirates of the Caribbean: At World's End   \n",
       "3                                   Spectre   \n",
       "4                     The Dark Knight Rises   \n",
       "5                           The Lone Ranger   \n",
       "6                               John Carter   \n",
       "7                                   Tangled   \n",
       "8                              Spider-Man 3   \n",
       "9                   Avengers: Age of Ultron   \n",
       "\n",
       "   \"Worldwide Gross\" - \"Production Budget\"  \n",
       "0                               2358918982  \n",
       "1                               1752662225  \n",
       "2                                663420425  \n",
       "3                                579620923  \n",
       "4                                809439099  \n",
       "5                                -14997885  \n",
       "6                                  7778100  \n",
       "7                                326581936  \n",
       "8                                636860230  \n",
       "9                               1158218722  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calcualte profit / loss per movie\n",
    "query1 = '''SELECT movie_title, \\\"Worldwide Gross\\\" - \\\"Production Budget\\\"\n",
    "            FROM movies;    \n",
    "'''\n",
    "\n",
    "pd.read_sql(query1, conn).head(10) # only show first 10 records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Determine the movie titles with the biggest loss (worldwide gross - production budget) **by genre of which the movie language was not French, German or Spanish** and where the loss was at least 10 million dollar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>\"Worldwide Gross\" - \"Production Budget\"</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avatar</td>\n",
       "      <td>2358918982</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pirates of the Caribbean: At World's End</td>\n",
       "      <td>663420425</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Spectre</td>\n",
       "      <td>579620923</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Dark Knight Rises</td>\n",
       "      <td>809439099</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Lone Ranger</td>\n",
       "      <td>-14997885</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>John Carter</td>\n",
       "      <td>7778100</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tangled</td>\n",
       "      <td>326581936</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Spider-Man 3</td>\n",
       "      <td>636860230</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>1158218722</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Captain America: Civil War</td>\n",
       "      <td>903304495</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                movie_title  \\\n",
       "0                                    Avatar   \n",
       "1  Pirates of the Caribbean: At World's End   \n",
       "2                                   Spectre   \n",
       "3                     The Dark Knight Rises   \n",
       "4                           The Lone Ranger   \n",
       "5                               John Carter   \n",
       "6                                   Tangled   \n",
       "7                              Spider-Man 3   \n",
       "8                   Avengers: Age of Ultron   \n",
       "9                Captain America: Civil War   \n",
       "\n",
       "   \"Worldwide Gross\" - \"Production Budget\" language  \n",
       "0                               2358918982  English  \n",
       "1                                663420425  English  \n",
       "2                                579620923  English  \n",
       "3                                809439099  English  \n",
       "4                                -14997885  English  \n",
       "5                                  7778100  English  \n",
       "6                                326581936  English  \n",
       "7                                636860230  English  \n",
       "8                               1158218722  English  \n",
       "9                                903304495  English  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# exclude French, German, Spanish movies \n",
    "query1 = '''SELECT movie_title, \\\"Worldwide Gross\\\" - \\\"Production Budget\\\", language\n",
    "            FROM movies\n",
    "            WHERE (language != \"French\") AND (language != \"German\") AND (language != \"Spanish\");\n",
    "'''\n",
    "\n",
    "pd.read_sql(query1, conn).head(10) #Star Wars Ep. VII: The Force Awakens is now excluded (why?)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>\"Worldwide Gross\" - \"Production Budget\"</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avatar</td>\n",
       "      <td>2358918982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pirates of the Caribbean: At World's End</td>\n",
       "      <td>663420425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Spectre</td>\n",
       "      <td>579620923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Dark Knight Rises</td>\n",
       "      <td>809439099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Lone Ranger</td>\n",
       "      <td>-14997885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>John Carter</td>\n",
       "      <td>7778100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tangled</td>\n",
       "      <td>326581936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Spider-Man 3</td>\n",
       "      <td>636860230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>1158218722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Captain America: Civil War</td>\n",
       "      <td>903304495</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                movie_title  \\\n",
       "0                                    Avatar   \n",
       "1  Pirates of the Caribbean: At World's End   \n",
       "2                                   Spectre   \n",
       "3                     The Dark Knight Rises   \n",
       "4                           The Lone Ranger   \n",
       "5                               John Carter   \n",
       "6                                   Tangled   \n",
       "7                              Spider-Man 3   \n",
       "8                   Avengers: Age of Ultron   \n",
       "9                Captain America: Civil War   \n",
       "\n",
       "   \"Worldwide Gross\" - \"Production Budget\"  \n",
       "0                               2358918982  \n",
       "1                                663420425  \n",
       "2                                579620923  \n",
       "3                                809439099  \n",
       "4                                -14997885  \n",
       "5                                  7778100  \n",
       "6                                326581936  \n",
       "7                                636860230  \n",
       "8                               1158218722  \n",
       "9                                903304495  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# exclude French, German, Spanish movies using NOT IN (preferred way - why?)\n",
    "query1 = '''SELECT movie_title, \\\"Worldwide Gross\\\" - \\\"Production Budget\\\"\n",
    "            FROM movies\n",
    "            WHERE language NOT IN (\"French\", \"German\", \"Spanish\");           \n",
    "'''\n",
    "\n",
    "pd.read_sql(query1, conn).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Determine the movie titles with the biggest loss (worldwide gross - production budget) **by genre** of which the movie language was not French, German or Spanish and where the loss was at least 10 million dollar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>genre</th>\n",
       "      <th>MIN(\"Worldwide Gross\" - \"Production Budget\")</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Sound of Thunder</td>\n",
       "      <td>Action</td>\n",
       "      <td>-73699549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mars Needs Moms</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>-110450242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Town &amp; Country</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>-94635231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Metallica Through the Never</td>\n",
       "      <td>Concert/Performance</td>\n",
       "      <td>-8917094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Home</td>\n",
       "      <td>Documentary</td>\n",
       "      <td>-10379944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Promise</td>\n",
       "      <td>Drama</td>\n",
       "      <td>-79955220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Exorcist: The Beginning</td>\n",
       "      <td>Horror</td>\n",
       "      <td>-34042459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>The Nutcracker in 3D</td>\n",
       "      <td>Musical</td>\n",
       "      <td>-69533984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Blackhat</td>\n",
       "      <td>Thriller/Suspense</td>\n",
       "      <td>-50451581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>The Alamo</td>\n",
       "      <td>Western</td>\n",
       "      <td>-68088638</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   movie_title                genre  \\\n",
       "0           A Sound of Thunder               Action   \n",
       "1              Mars Needs Moms            Adventure   \n",
       "2               Town & Country               Comedy   \n",
       "3  Metallica Through the Never  Concert/Performance   \n",
       "4                         Home          Documentary   \n",
       "5                  The Promise                Drama   \n",
       "6      Exorcist: The Beginning               Horror   \n",
       "7         The Nutcracker in 3D              Musical   \n",
       "8                     Blackhat    Thriller/Suspense   \n",
       "9                    The Alamo              Western   \n",
       "\n",
       "   MIN(\"Worldwide Gross\" - \"Production Budget\")  \n",
       "0                                     -73699549  \n",
       "1                                    -110450242  \n",
       "2                                     -94635231  \n",
       "3                                      -8917094  \n",
       "4                                     -10379944  \n",
       "5                                     -79955220  \n",
       "6                                     -34042459  \n",
       "7                                     -69533984  \n",
       "8                                     -50451581  \n",
       "9                                     -68088638  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select movie with biggest losss per genre\n",
    "query1 = '''SELECT movie_title, genre, MIN(\\\"Worldwide Gross\\\" - \\\"Production Budget\\\") \n",
    "            FROM movies\n",
    "            WHERE language NOT IN (\"French\", \"German\", \"Spanish\")\n",
    "            GROUP BY genre;\n",
    "'''\n",
    "pd.read_sql(query1, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Determine the movie titles with the biggest loss (worldwide gross - production budget) by genre of which the movie language was not French, German or Spanish and where **the loss was at least 10 million dollar.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>genre</th>\n",
       "      <th>MIN(\"Worldwide Gross\" - \"Production Budget\")</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Sound of Thunder</td>\n",
       "      <td>Action</td>\n",
       "      <td>-73699549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mars Needs Moms</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>-110450242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Town &amp; Country</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>-94635231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Home</td>\n",
       "      <td>Documentary</td>\n",
       "      <td>-10379944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Promise</td>\n",
       "      <td>Drama</td>\n",
       "      <td>-79955220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Exorcist: The Beginning</td>\n",
       "      <td>Horror</td>\n",
       "      <td>-34042459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>The Nutcracker in 3D</td>\n",
       "      <td>Musical</td>\n",
       "      <td>-69533984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Blackhat</td>\n",
       "      <td>Thriller/Suspense</td>\n",
       "      <td>-50451581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>The Alamo</td>\n",
       "      <td>Western</td>\n",
       "      <td>-68088638</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               movie_title              genre  \\\n",
       "0       A Sound of Thunder             Action   \n",
       "1          Mars Needs Moms          Adventure   \n",
       "2           Town & Country             Comedy   \n",
       "3                     Home        Documentary   \n",
       "4              The Promise              Drama   \n",
       "5  Exorcist: The Beginning             Horror   \n",
       "6     The Nutcracker in 3D            Musical   \n",
       "7                 Blackhat  Thriller/Suspense   \n",
       "8                The Alamo            Western   \n",
       "\n",
       "   MIN(\"Worldwide Gross\" - \"Production Budget\")  \n",
       "0                                     -73699549  \n",
       "1                                    -110450242  \n",
       "2                                     -94635231  \n",
       "3                                     -10379944  \n",
       "4                                     -79955220  \n",
       "5                                     -34042459  \n",
       "6                                     -69533984  \n",
       "7                                     -50451581  \n",
       "8                                     -68088638  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# only look at genres of which the loss is greater than 10M\n",
    "query1 = '''SELECT movie_title, genre, MIN(\\\"Worldwide Gross\\\" - \\\"Production Budget\\\")\n",
    "            FROM movies\n",
    "            WHERE language NOT IN (\"French\", \"German\", \"Spanish\")\n",
    "            GROUP BY genre\n",
    "            HAVING MIN(\\\"Worldwide Gross\\\" - \\\"Production Budget\\\") < -10000000;\n",
    "''' \n",
    "\n",
    "pd.read_sql(query1, conn) # why is there a minus symbol in front of 10M? which genre did we exclude? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>genre</th>\n",
       "      <th>profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Sound of Thunder</td>\n",
       "      <td>Action</td>\n",
       "      <td>-73699549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mars Needs Moms</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>-110450242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Town &amp; Country</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>-94635231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Home</td>\n",
       "      <td>Documentary</td>\n",
       "      <td>-10379944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Promise</td>\n",
       "      <td>Drama</td>\n",
       "      <td>-79955220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Exorcist: The Beginning</td>\n",
       "      <td>Horror</td>\n",
       "      <td>-34042459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>The Nutcracker in 3D</td>\n",
       "      <td>Musical</td>\n",
       "      <td>-69533984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Blackhat</td>\n",
       "      <td>Thriller/Suspense</td>\n",
       "      <td>-50451581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>The Alamo</td>\n",
       "      <td>Western</td>\n",
       "      <td>-68088638</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               movie_title              genre     profit\n",
       "0       A Sound of Thunder             Action  -73699549\n",
       "1          Mars Needs Moms          Adventure -110450242\n",
       "2           Town & Country             Comedy  -94635231\n",
       "3                     Home        Documentary  -10379944\n",
       "4              The Promise              Drama  -79955220\n",
       "5  Exorcist: The Beginning             Horror  -34042459\n",
       "6     The Nutcracker in 3D            Musical  -69533984\n",
       "7                 Blackhat  Thriller/Suspense  -50451581\n",
       "8                The Alamo            Western  -68088638"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change column name to profit\n",
    "query1 = '''SELECT movie_title, genre, MIN(\\\"Worldwide Gross\\\" - \\\"Production Budget\\\") AS profit\n",
    "            FROM movies\n",
    "            WHERE language NOT IN (\"French\", \"German\", \"Spanish\")\n",
    "            GROUP BY genre\n",
    "            HAVING MIN(\\\"Worldwide Gross\\\" - \\\"Production Budget\\\") < -10000000;\n",
    "'''\n",
    "\n",
    "pd.read_sql(query1, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. What are the top 3 movies released in 2016 which were not profitable but got the highest IMDB-score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "### STEP BY STEP SOLUTION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the top 3 **movies released in 2016** which were not profitable but got the highest IMDB-score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>mid</th>\n",
       "      <th>movie_title</th>\n",
       "      <th>Release Date</th>\n",
       "      <th>Production Budget</th>\n",
       "      <th>Domestic Gross</th>\n",
       "      <th>Worldwide Gross</th>\n",
       "      <th>genre</th>\n",
       "      <th>color</th>\n",
       "      <th>director_name</th>\n",
       "      <th>...</th>\n",
       "      <th>cast_total_facebook_likes</th>\n",
       "      <th>facenumber_in_poster</th>\n",
       "      <th>plot_keywords</th>\n",
       "      <th>num_user_for_reviews</th>\n",
       "      <th>language</th>\n",
       "      <th>country</th>\n",
       "      <th>content_rating</th>\n",
       "      <th>imdb_score</th>\n",
       "      <th>aspect_ratio</th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>Captain America: Civil War</td>\n",
       "      <td>2016-05-06</td>\n",
       "      <td>250000000</td>\n",
       "      <td>408084349</td>\n",
       "      <td>1153304495</td>\n",
       "      <td>Action</td>\n",
       "      <td>Color</td>\n",
       "      <td>Anthony Russo</td>\n",
       "      <td>...</td>\n",
       "      <td>64798.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>based on comic book|knife|marvel cinematic uni...</td>\n",
       "      <td>1022.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>8.2</td>\n",
       "      <td>2.35</td>\n",
       "      <td>72000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>Batman v Superman: Dawn of Justice</td>\n",
       "      <td>2016-03-25</td>\n",
       "      <td>250000000</td>\n",
       "      <td>330360194</td>\n",
       "      <td>868160194</td>\n",
       "      <td>Action</td>\n",
       "      <td>Color</td>\n",
       "      <td>Zack Snyder</td>\n",
       "      <td>...</td>\n",
       "      <td>24450.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>based on comic book|batman|sequel to a reboot|...</td>\n",
       "      <td>3018.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2.35</td>\n",
       "      <td>197000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41</td>\n",
       "      <td>37</td>\n",
       "      <td>Rogue One: A Star Wars Story</td>\n",
       "      <td>2016-12-16</td>\n",
       "      <td>200000000</td>\n",
       "      <td>532177324</td>\n",
       "      <td>1050988488</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>42</td>\n",
       "      <td>38</td>\n",
       "      <td>Finding Dory</td>\n",
       "      <td>2016-06-17</td>\n",
       "      <td>200000000</td>\n",
       "      <td>486295561</td>\n",
       "      <td>1022617376</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "      <td>Star Trek Beyond</td>\n",
       "      <td>2016-07-22</td>\n",
       "      <td>185000000</td>\n",
       "      <td>158848340</td>\n",
       "      <td>336398312</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Color</td>\n",
       "      <td>Justin Lin</td>\n",
       "      <td>...</td>\n",
       "      <td>1327.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>hatred|sequel|space opera|star trek|third part</td>\n",
       "      <td>432.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>7.5</td>\n",
       "      <td>2.35</td>\n",
       "      <td>30000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  mid                         movie_title Release Date  \\\n",
       "0     11   10          Captain America: Civil War   2016-05-06   \n",
       "1     12   11  Batman v Superman: Dawn of Justice   2016-03-25   \n",
       "2     41   37        Rogue One: A Star Wars Story   2016-12-16   \n",
       "3     42   38                        Finding Dory   2016-06-17   \n",
       "4     78   70                    Star Trek Beyond   2016-07-22   \n",
       "\n",
       "   Production Budget  Domestic Gross  Worldwide Gross      genre  color  \\\n",
       "0          250000000       408084349       1153304495     Action  Color   \n",
       "1          250000000       330360194        868160194     Action  Color   \n",
       "2          200000000       532177324       1050988488  Adventure   None   \n",
       "3          200000000       486295561       1022617376  Adventure   None   \n",
       "4          185000000       158848340        336398312  Adventure  Color   \n",
       "\n",
       "   director_name          ...           cast_total_facebook_likes  \\\n",
       "0  Anthony Russo          ...                             64798.0   \n",
       "1    Zack Snyder          ...                             24450.0   \n",
       "2           None          ...                                 NaN   \n",
       "3           None          ...                                 NaN   \n",
       "4     Justin Lin          ...                              1327.0   \n",
       "\n",
       "   facenumber_in_poster                                      plot_keywords  \\\n",
       "0                   0.0  based on comic book|knife|marvel cinematic uni...   \n",
       "1                   0.0  based on comic book|batman|sequel to a reboot|...   \n",
       "2                   NaN                                               None   \n",
       "3                   NaN                                               None   \n",
       "4                   4.0     hatred|sequel|space opera|star trek|third part   \n",
       "\n",
       "   num_user_for_reviews language  country  content_rating  imdb_score  \\\n",
       "0                1022.0  English      USA           PG-13         8.2   \n",
       "1                3018.0  English      USA           PG-13         6.9   \n",
       "2                   NaN     None     None            None         NaN   \n",
       "3                   NaN     None     None            None         NaN   \n",
       "4                 432.0  English      USA           PG-13         7.5   \n",
       "\n",
       "  aspect_ratio  movie_facebook_likes  \n",
       "0         2.35                 72000  \n",
       "1         2.35                197000  \n",
       "2          NaN                  None  \n",
       "3          NaN                  None  \n",
       "4         2.35                 30000  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query2 = '''SELECT *\n",
    "            FROM movies\n",
    "            WHERE \\\"Release Date\\\" >= '2016-01-01' AND \\\"Release Date\\\" < '2017-01-01';\n",
    "'''\n",
    "pd.read_sql(query2, conn).head() # how can we easily check whether we did this correctly?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>mid</th>\n",
       "      <th>movie_title</th>\n",
       "      <th>Release Date</th>\n",
       "      <th>Production Budget</th>\n",
       "      <th>Domestic Gross</th>\n",
       "      <th>Worldwide Gross</th>\n",
       "      <th>genre</th>\n",
       "      <th>color</th>\n",
       "      <th>director_name</th>\n",
       "      <th>...</th>\n",
       "      <th>cast_total_facebook_likes</th>\n",
       "      <th>facenumber_in_poster</th>\n",
       "      <th>plot_keywords</th>\n",
       "      <th>num_user_for_reviews</th>\n",
       "      <th>language</th>\n",
       "      <th>country</th>\n",
       "      <th>content_rating</th>\n",
       "      <th>imdb_score</th>\n",
       "      <th>aspect_ratio</th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2251</td>\n",
       "      <td>2182</td>\n",
       "      <td>The Butterfly God</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>22000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Drama</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2535</td>\n",
       "      <td>2467</td>\n",
       "      <td>Reagan</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>25000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Drama</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4375</td>\n",
       "      <td>4460</td>\n",
       "      <td>Airlift (2016)</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>4400000</td>\n",
       "      <td>0</td>\n",
       "      <td>9969237</td>\n",
       "      <td>Drama</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4624</td>\n",
       "      <td>4763</td>\n",
       "      <td>Chemical Cut</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>1000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Drama</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4989</td>\n",
       "      <td>5222</td>\n",
       "      <td>Crabs!</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>500000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index   mid        movie_title Release Date  Production Budget  \\\n",
       "0   2251  2182  The Butterfly God   2016-12-31           22000000   \n",
       "1   2535  2467             Reagan   2016-12-31           25000000   \n",
       "2   4375  4460     Airlift (2016)   2016-12-31            4400000   \n",
       "3   4624  4763       Chemical Cut   2016-12-31            1000000   \n",
       "4   4989  5222             Crabs!   2016-12-31             500000   \n",
       "\n",
       "   Domestic Gross  Worldwide Gross   genre color director_name  \\\n",
       "0               0                0   Drama  None          None   \n",
       "1               0                0   Drama  None          None   \n",
       "2               0          9969237   Drama  None          None   \n",
       "3               0                0   Drama  None          None   \n",
       "4               0                0  Comedy  None          None   \n",
       "\n",
       "           ...           cast_total_facebook_likes  facenumber_in_poster  \\\n",
       "0          ...                                 NaN                   NaN   \n",
       "1          ...                                 NaN                   NaN   \n",
       "2          ...                                 NaN                   NaN   \n",
       "3          ...                                 NaN                   NaN   \n",
       "4          ...                                 NaN                   NaN   \n",
       "\n",
       "   plot_keywords  num_user_for_reviews language  country  content_rating  \\\n",
       "0           None                   NaN     None     None            None   \n",
       "1           None                   NaN     None     None            None   \n",
       "2           None                   NaN     None     None            None   \n",
       "3           None                   NaN     None     None            None   \n",
       "4           None                   NaN     None     None            None   \n",
       "\n",
       "   imdb_score aspect_ratio  movie_facebook_likes  \n",
       "0         NaN          NaN                  None  \n",
       "1         NaN          NaN                  None  \n",
       "2         NaN          NaN                  None  \n",
       "3         NaN          NaN                  None  \n",
       "4         NaN          NaN                  None  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find latest / most recent dates\n",
    "query2 = '''SELECT *\n",
    "            FROM movies\n",
    "            WHERE \\\"Release Date\\\" >= '2016-01-01' AND \\\"Release Date\\\" < '2017-01-01'\n",
    "            ORDER BY \\\"Release Date\\\" DESC;\n",
    "'''\n",
    "pd.read_sql(query2, conn).head() # order ascending/descending"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the top 3 movies released in 2016 which were **not profitable** but got the highest IMDB-score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>Release Date</th>\n",
       "      <th>\"Worldwide Gross\" - \"Production Budget\"</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Snowden</td>\n",
       "      <td>2016-09-16</td>\n",
       "      <td>-15698050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Free State of Jones</td>\n",
       "      <td>2016-06-24</td>\n",
       "      <td>-26847938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Deepwater Horizon</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>-33062908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gods of Egypt</td>\n",
       "      <td>2016-02-26</td>\n",
       "      <td>-6446536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Billy Lynn's Long Halftime Walk</td>\n",
       "      <td>2016-11-11</td>\n",
       "      <td>-14136542</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       movie_title Release Date  \\\n",
       "0                          Snowden   2016-09-16   \n",
       "1              Free State of Jones   2016-06-24   \n",
       "2                Deepwater Horizon   2016-09-30   \n",
       "3                    Gods of Egypt   2016-02-26   \n",
       "4  Billy Lynn's Long Halftime Walk   2016-11-11   \n",
       "\n",
       "   \"Worldwide Gross\" - \"Production Budget\"  \n",
       "0                                -15698050  \n",
       "1                                -26847938  \n",
       "2                                -33062908  \n",
       "3                                 -6446536  \n",
       "4                                -14136542  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query2 = '''SELECT movie_title, \\\"Release Date\\\", \\\"Worldwide Gross\\\" - \\\"Production Budget\\\"\n",
    "           FROM movies\n",
    "           WHERE \\\"Release Date\\\" >= '2016-01-01' AND \\\"Release Date\\\" < '2017-01-01' AND \\\"Worldwide Gross\\\" - \\\"Production Budget\\\" < 0;\n",
    "\n",
    "'''\n",
    "pd.read_sql(query2, conn).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Snowden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Free State of Jones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Deepwater Horizon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gods of Egypt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Billy Lynn's Long Halftime Walk</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       movie_title\n",
       "0                          Snowden\n",
       "1              Free State of Jones\n",
       "2                Deepwater Horizon\n",
       "3                    Gods of Egypt\n",
       "4  Billy Lynn's Long Halftime Walk"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we don't need to see the profit (i.e. loss), neither the release date\n",
    "query2 = '''SELECT movie_title\n",
    "           FROM movies\n",
    "           WHERE \\\"Release Date\\\" >= '2016-01-01' AND \\\"Release Date\\\" < '2017-01-01' AND \\\"Worldwide Gross\\\" - \\\"Production Budget\\\" < 0; \n",
    "'''\n",
    "pd.read_sql(query2, conn).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>imdb_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Snowden</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Free State of Jones</td>\n",
       "      <td>6.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Deepwater Horizon</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gods of Egypt</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Billy Lynn's Long Halftime Walk</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       movie_title  imdb_score\n",
       "0                          Snowden         NaN\n",
       "1              Free State of Jones         6.7\n",
       "2                Deepwater Horizon         NaN\n",
       "3                    Gods of Egypt         5.5\n",
       "4  Billy Lynn's Long Halftime Walk         NaN"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we do need the imdb_score column though\n",
    "query2 = '''SELECT movie_title, imdb_score\n",
    "           FROM movies\n",
    "           WHERE \\\"Release Date\\\" >= '2016-01-01' AND \\\"Release Date\\\" < '2017-01-01' AND \\\"Worldwide Gross\\\" - \\\"Production Budget\\\" < 0;\n",
    "'''\n",
    "pd.read_sql(query2, conn).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the top 3 movies released in 2016 which were not profitable but got the **highest IMDB-score**?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>imdb_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Snowden</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Deepwater Horizon</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Billy Lynn's Long Halftime Walk</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USS Indianapolis: Men of Courage</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Whiskey Tango Foxtrot</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        movie_title  imdb_score\n",
       "0                           Snowden         NaN\n",
       "1                 Deepwater Horizon         NaN\n",
       "2   Billy Lynn's Long Halftime Walk         NaN\n",
       "3  USS Indianapolis: Men of Courage         NaN\n",
       "4             Whiskey Tango Foxtrot         NaN"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find movies with highest imdb_score\n",
    "query2 = '''SELECT movie_title, imdb_score\n",
    "            FROM movies\n",
    "            WHERE \\\"Release Date\\\" >= '2016-01-01' AND \\\"Release Date\\\" < '2017-01-01' AND \\\"Worldwide Gross\\\" - \\\"Production Budget\\\" < 0\n",
    "            ORDER BY imdb_score;\n",
    "'''\n",
    "pd.read_sql(query2, conn).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>imdb_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kicks</td>\n",
       "      <td>7.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>They Will Have to Kill Us First</td>\n",
       "      <td>7.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Une Femme Marie</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Infiltrator</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hands of Stone</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mi America</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Green Room</td>\n",
       "      <td>7.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1982</td>\n",
       "      <td>7.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Time to Choose</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>The Neon Demon</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Reign of Assassins</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>The Finest Hours</td>\n",
       "      <td>6.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Free State of Jones</td>\n",
       "      <td>6.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Midnight Special</td>\n",
       "      <td>6.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Sharkskin</td>\n",
       "      <td>6.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Forsaken</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Antibirth</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ben-Hur</td>\n",
       "      <td>6.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Pride and Prejudice and Zombies</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Jane Got a Gun</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Gods of Egypt</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Creative Control</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Unsullied</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>The Young Messiah</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Misconduct</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>I Am Wrath</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Fight Valley</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>The Rise of the Krays</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Adulterers</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Yoga Hosers</td>\n",
       "      <td>4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Code of Honor</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Snowden</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Deepwater Horizon</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Billy Lynn's Long Halftime Walk</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>USS Indianapolis: Men of Courage</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Whiskey Tango Foxtrot</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Live by Night</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Silence</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Rules Don't Apply</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>The Brothers Grimsby</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Savva. Serdtse voyna</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>The Butterfly God</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>The Light Between Oceans</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Popstar: Never Stop Never Stopping</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Ratchet and Clank</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Reagan</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>The History of Love</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Genius</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Bleed for This</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Greater</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Dheepan</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Miss Sloane</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Everybody Wants Some</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Morgan</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>The Hatching</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>The Invitation</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Chemical Cut</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>The Wrong Side of Right</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Crabs!</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>The Fits</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           movie_title  imdb_score\n",
       "0                                Kicks         7.8\n",
       "1      They Will Have to Kill Us First         7.7\n",
       "2                      Une Femme Marie         7.4\n",
       "3                      The Infiltrator         7.3\n",
       "4                       Hands of Stone         7.2\n",
       "5                           Mi America         7.2\n",
       "6                           Green Room         7.1\n",
       "7                                 1982         7.1\n",
       "8                       Time to Choose         7.0\n",
       "9                       The Neon Demon         7.0\n",
       "10                  Reign of Assassins         6.9\n",
       "11                    The Finest Hours         6.8\n",
       "12                 Free State of Jones         6.7\n",
       "13                    Midnight Special         6.7\n",
       "14                           Sharkskin         6.7\n",
       "15                            Forsaken         6.3\n",
       "16                           Antibirth         6.3\n",
       "17                             Ben-Hur         6.1\n",
       "18     Pride and Prejudice and Zombies         5.8\n",
       "19                      Jane Got a Gun         5.8\n",
       "20                       Gods of Egypt         5.5\n",
       "21                    Creative Control         5.5\n",
       "22                           Unsullied         5.5\n",
       "23                   The Young Messiah         5.4\n",
       "24                          Misconduct         5.3\n",
       "25                          I Am Wrath         5.2\n",
       "26                        Fight Valley         5.0\n",
       "27               The Rise of the Krays         5.0\n",
       "28                          Adulterers         4.9\n",
       "29                         Yoga Hosers         4.8\n",
       "..                                 ...         ...\n",
       "32                       Code of Honor         4.2\n",
       "33                             Snowden         NaN\n",
       "34                   Deepwater Horizon         NaN\n",
       "35     Billy Lynn's Long Halftime Walk         NaN\n",
       "36    USS Indianapolis: Men of Courage         NaN\n",
       "37               Whiskey Tango Foxtrot         NaN\n",
       "38                       Live by Night         NaN\n",
       "39                             Silence         NaN\n",
       "40                   Rules Don't Apply         NaN\n",
       "41                The Brothers Grimsby         NaN\n",
       "42                Savva. Serdtse voyna         NaN\n",
       "43                   The Butterfly God         NaN\n",
       "44            The Light Between Oceans         NaN\n",
       "45  Popstar: Never Stop Never Stopping         NaN\n",
       "46                   Ratchet and Clank         NaN\n",
       "47                              Reagan         NaN\n",
       "48                 The History of Love         NaN\n",
       "49                              Genius         NaN\n",
       "50                      Bleed for This         NaN\n",
       "51                             Greater         NaN\n",
       "52                             Dheepan         NaN\n",
       "53                         Miss Sloane         NaN\n",
       "54                Everybody Wants Some         NaN\n",
       "55                              Morgan         NaN\n",
       "56                        The Hatching         NaN\n",
       "57                      The Invitation         NaN\n",
       "58                        Chemical Cut         NaN\n",
       "59             The Wrong Side of Right         NaN\n",
       "60                              Crabs!         NaN\n",
       "61                            The Fits         NaN\n",
       "\n",
       "[62 rows x 2 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find movies with highest imdb_score (descending order)\n",
    "query2 = '''SELECT movie_title, imdb_score\n",
    "            FROM movies\n",
    "            WHERE \\\"Release Date\\\" >= '2016-01-01' AND \\\"Release Date\\\" < '2017-01-01' AND \\\"Worldwide Gross\\\" - \\\"Production Budget\\\" < 0\n",
    "            ORDER BY imdb_score DESC;\n",
    "'''\n",
    "pd.read_sql(query2, conn) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the **top 3** movies released in 2016 which were not profitable but got the highest IMDB-score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_title</th>\n",
       "      <th>imdb_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kicks</td>\n",
       "      <td>7.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>They Will Have to Kill Us First</td>\n",
       "      <td>7.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Une Femme Marie</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       movie_title  imdb_score\n",
       "0                            Kicks         7.8\n",
       "1  They Will Have to Kill Us First         7.7\n",
       "2                  Une Femme Marie         7.4"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# only pick the top 3 results\n",
    "query2 = '''SELECT movie_title, imdb_score\n",
    "            FROM movies\n",
    "            WHERE \\\"Release Date\\\" >= '2016-01-01' AND \\\"Release Date\\\" < '2017-01-01' AND \\\"Worldwide Gross\\\" - \\\"Production Budget\\\" < 0 \n",
    "            ORDER BY imdb_score DESC\n",
    "            LIMIT 3;\n",
    "'''\n",
    "pd.read_sql(query2, conn) # disclaimer: what could be a problem using this approach (i.e. LIMIT)?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. What is the worldwide gross revenue per weekday for Action movies for which the number of Facebook likes is known (i.e. is different than None)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# STEP BY STEP SOLUTION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the **worldwide gross revenue per weekday** for Action movies for which the number of Facebook likes is known (i.e. is different than None)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>mid</th>\n",
       "      <th>movie_title</th>\n",
       "      <th>Release Date</th>\n",
       "      <th>Production Budget</th>\n",
       "      <th>Domestic Gross</th>\n",
       "      <th>Worldwide Gross</th>\n",
       "      <th>genre</th>\n",
       "      <th>color</th>\n",
       "      <th>director_name</th>\n",
       "      <th>...</th>\n",
       "      <th>cast_total_facebook_likes</th>\n",
       "      <th>facenumber_in_poster</th>\n",
       "      <th>plot_keywords</th>\n",
       "      <th>num_user_for_reviews</th>\n",
       "      <th>language</th>\n",
       "      <th>country</th>\n",
       "      <th>content_rating</th>\n",
       "      <th>imdb_score</th>\n",
       "      <th>aspect_ratio</th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>2009-12-18</td>\n",
       "      <td>425000000</td>\n",
       "      <td>760507625</td>\n",
       "      <td>2783918982</td>\n",
       "      <td>Action</td>\n",
       "      <td>Color</td>\n",
       "      <td>James Cameron</td>\n",
       "      <td>...</td>\n",
       "      <td>4834.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>avatar|future|marine|native|paraplegic</td>\n",
       "      <td>3054.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>7.9</td>\n",
       "      <td>1.78</td>\n",
       "      <td>33000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Star Wars Ep. VII: The Force Awakens</td>\n",
       "      <td>2015-12-18</td>\n",
       "      <td>306000000</td>\n",
       "      <td>936662225</td>\n",
       "      <td>2058662225</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Pirates of the Caribbean: At World's End</td>\n",
       "      <td>2007-05-24</td>\n",
       "      <td>300000000</td>\n",
       "      <td>309420425</td>\n",
       "      <td>963420425</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Color</td>\n",
       "      <td>Gore Verbinski</td>\n",
       "      <td>...</td>\n",
       "      <td>48350.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>goddess|marriage ceremony|marriage proposal|pi...</td>\n",
       "      <td>1238.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>7.1</td>\n",
       "      <td>2.35</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Spectre</td>\n",
       "      <td>2015-11-06</td>\n",
       "      <td>300000000</td>\n",
       "      <td>200074175</td>\n",
       "      <td>879620923</td>\n",
       "      <td>Action</td>\n",
       "      <td>Color</td>\n",
       "      <td>Sam Mendes</td>\n",
       "      <td>...</td>\n",
       "      <td>11700.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>bomb|espionage|sequel|spy|terrorist</td>\n",
       "      <td>994.0</td>\n",
       "      <td>English</td>\n",
       "      <td>UK</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>6.8</td>\n",
       "      <td>2.35</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>The Dark Knight Rises</td>\n",
       "      <td>2012-07-20</td>\n",
       "      <td>275000000</td>\n",
       "      <td>448139099</td>\n",
       "      <td>1084439099</td>\n",
       "      <td>Action</td>\n",
       "      <td>Color</td>\n",
       "      <td>Christopher Nolan</td>\n",
       "      <td>...</td>\n",
       "      <td>106759.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>deception|imprisonment|lawlessness|police offi...</td>\n",
       "      <td>2701.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>8.5</td>\n",
       "      <td>2.35</td>\n",
       "      <td>164000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  mid                               movie_title Release Date  \\\n",
       "0      0    0                                    Avatar   2009-12-18   \n",
       "1      1    1      Star Wars Ep. VII: The Force Awakens   2015-12-18   \n",
       "2      2    2  Pirates of the Caribbean: At World's End   2007-05-24   \n",
       "3      3    3                                   Spectre   2015-11-06   \n",
       "4      4    4                     The Dark Knight Rises   2012-07-20   \n",
       "\n",
       "   Production Budget  Domestic Gross  Worldwide Gross      genre  color  \\\n",
       "0          425000000       760507625       2783918982     Action  Color   \n",
       "1          306000000       936662225       2058662225  Adventure   None   \n",
       "2          300000000       309420425        963420425  Adventure  Color   \n",
       "3          300000000       200074175        879620923     Action  Color   \n",
       "4          275000000       448139099       1084439099     Action  Color   \n",
       "\n",
       "       director_name          ...           cast_total_facebook_likes  \\\n",
       "0      James Cameron          ...                              4834.0   \n",
       "1               None          ...                                 NaN   \n",
       "2     Gore Verbinski          ...                             48350.0   \n",
       "3         Sam Mendes          ...                             11700.0   \n",
       "4  Christopher Nolan          ...                            106759.0   \n",
       "\n",
       "   facenumber_in_poster                                      plot_keywords  \\\n",
       "0                   0.0             avatar|future|marine|native|paraplegic   \n",
       "1                   NaN                                               None   \n",
       "2                   0.0  goddess|marriage ceremony|marriage proposal|pi...   \n",
       "3                   1.0                bomb|espionage|sequel|spy|terrorist   \n",
       "4                   0.0  deception|imprisonment|lawlessness|police offi...   \n",
       "\n",
       "   num_user_for_reviews language  country  content_rating  imdb_score  \\\n",
       "0                3054.0  English      USA           PG-13         7.9   \n",
       "1                   NaN     None     None            None         NaN   \n",
       "2                1238.0  English      USA           PG-13         7.1   \n",
       "3                 994.0  English       UK           PG-13         6.8   \n",
       "4                2701.0  English      USA           PG-13         8.5   \n",
       "\n",
       "  aspect_ratio  movie_facebook_likes  \n",
       "0         1.78                 33000  \n",
       "1          NaN                  None  \n",
       "2         2.35                     0  \n",
       "3         2.35                 85000  \n",
       "4         2.35                164000  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# movie table\n",
    "query3 = '''SELECT *\n",
    "            FROM movies;\n",
    "''' \n",
    "pd.read_sql(query3, conn).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>movie</th>\n",
       "      <th>Date</th>\n",
       "      <th>Gross</th>\n",
       "      <th>Theaters</th>\n",
       "      <th>Per Theater</th>\n",
       "      <th>Days</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>166385</th>\n",
       "      <td>166385</td>\n",
       "      <td>5399</td>\n",
       "      <td>2012-06-25</td>\n",
       "      <td>654</td>\n",
       "      <td>6</td>\n",
       "      <td>109.0</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166386</th>\n",
       "      <td>166386</td>\n",
       "      <td>5399</td>\n",
       "      <td>2012-06-26</td>\n",
       "      <td>633</td>\n",
       "      <td>6</td>\n",
       "      <td>106.0</td>\n",
       "      <td>61</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166387</th>\n",
       "      <td>166387</td>\n",
       "      <td>5399</td>\n",
       "      <td>2012-06-27</td>\n",
       "      <td>720</td>\n",
       "      <td>6</td>\n",
       "      <td>120.0</td>\n",
       "      <td>62</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166388</th>\n",
       "      <td>166388</td>\n",
       "      <td>5399</td>\n",
       "      <td>2012-06-28</td>\n",
       "      <td>394</td>\n",
       "      <td>6</td>\n",
       "      <td>66.0</td>\n",
       "      <td>63</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166389</th>\n",
       "      <td>166389</td>\n",
       "      <td>5402</td>\n",
       "      <td>2006-05-29</td>\n",
       "      <td>3398</td>\n",
       "      <td>2</td>\n",
       "      <td>1699.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         index  movie        Date  Gross  Theaters  Per Theater  Days  weekday\n",
       "166385  166385   5399  2012-06-25    654         6        109.0    60        0\n",
       "166386  166386   5399  2012-06-26    633         6        106.0    61        1\n",
       "166387  166387   5399  2012-06-27    720         6        120.0    62        2\n",
       "166388  166388   5399  2012-06-28    394         6         66.0    63        3\n",
       "166389  166389   5402  2006-05-29   3398         2       1699.0     4        0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# daily table\n",
    "query3 = '''SELECT *\n",
    "            FROM daily;\n",
    "''' \n",
    "pd.read_sql(query3, conn).tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>mid</th>\n",
       "      <th>movie_title</th>\n",
       "      <th>Release Date</th>\n",
       "      <th>Production Budget</th>\n",
       "      <th>Domestic Gross</th>\n",
       "      <th>Worldwide Gross</th>\n",
       "      <th>genre</th>\n",
       "      <th>color</th>\n",
       "      <th>director_name</th>\n",
       "      <th>...</th>\n",
       "      <th>aspect_ratio</th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "      <th>index</th>\n",
       "      <th>movie</th>\n",
       "      <th>Date</th>\n",
       "      <th>Gross</th>\n",
       "      <th>Theaters</th>\n",
       "      <th>Per Theater</th>\n",
       "      <th>Days</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>166117</th>\n",
       "      <td>5126</td>\n",
       "      <td>5399</td>\n",
       "      <td>Sound of My Voice</td>\n",
       "      <td>2012-04-27</td>\n",
       "      <td>135000</td>\n",
       "      <td>408015</td>\n",
       "      <td>429448</td>\n",
       "      <td>Thriller/Suspense</td>\n",
       "      <td>Color</td>\n",
       "      <td>Zal Batmanglij</td>\n",
       "      <td>...</td>\n",
       "      <td>1.85</td>\n",
       "      <td>0</td>\n",
       "      <td>166385</td>\n",
       "      <td>5399</td>\n",
       "      <td>2012-06-25</td>\n",
       "      <td>654</td>\n",
       "      <td>6</td>\n",
       "      <td>109.0</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166118</th>\n",
       "      <td>5126</td>\n",
       "      <td>5399</td>\n",
       "      <td>Sound of My Voice</td>\n",
       "      <td>2012-04-27</td>\n",
       "      <td>135000</td>\n",
       "      <td>408015</td>\n",
       "      <td>429448</td>\n",
       "      <td>Thriller/Suspense</td>\n",
       "      <td>Color</td>\n",
       "      <td>Zal Batmanglij</td>\n",
       "      <td>...</td>\n",
       "      <td>1.85</td>\n",
       "      <td>0</td>\n",
       "      <td>166386</td>\n",
       "      <td>5399</td>\n",
       "      <td>2012-06-26</td>\n",
       "      <td>633</td>\n",
       "      <td>6</td>\n",
       "      <td>106.0</td>\n",
       "      <td>61</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166119</th>\n",
       "      <td>5126</td>\n",
       "      <td>5399</td>\n",
       "      <td>Sound of My Voice</td>\n",
       "      <td>2012-04-27</td>\n",
       "      <td>135000</td>\n",
       "      <td>408015</td>\n",
       "      <td>429448</td>\n",
       "      <td>Thriller/Suspense</td>\n",
       "      <td>Color</td>\n",
       "      <td>Zal Batmanglij</td>\n",
       "      <td>...</td>\n",
       "      <td>1.85</td>\n",
       "      <td>0</td>\n",
       "      <td>166387</td>\n",
       "      <td>5399</td>\n",
       "      <td>2012-06-27</td>\n",
       "      <td>720</td>\n",
       "      <td>6</td>\n",
       "      <td>120.0</td>\n",
       "      <td>62</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166120</th>\n",
       "      <td>5126</td>\n",
       "      <td>5399</td>\n",
       "      <td>Sound of My Voice</td>\n",
       "      <td>2012-04-27</td>\n",
       "      <td>135000</td>\n",
       "      <td>408015</td>\n",
       "      <td>429448</td>\n",
       "      <td>Thriller/Suspense</td>\n",
       "      <td>Color</td>\n",
       "      <td>Zal Batmanglij</td>\n",
       "      <td>...</td>\n",
       "      <td>1.85</td>\n",
       "      <td>0</td>\n",
       "      <td>166388</td>\n",
       "      <td>5399</td>\n",
       "      <td>2012-06-28</td>\n",
       "      <td>394</td>\n",
       "      <td>6</td>\n",
       "      <td>66.0</td>\n",
       "      <td>63</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166121</th>\n",
       "      <td>5128</td>\n",
       "      <td>5402</td>\n",
       "      <td>Cavite</td>\n",
       "      <td>2006-05-26</td>\n",
       "      <td>7000</td>\n",
       "      <td>70071</td>\n",
       "      <td>71644</td>\n",
       "      <td>Thriller/Suspense</td>\n",
       "      <td>Color</td>\n",
       "      <td>Neill Dela Llana</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>166389</td>\n",
       "      <td>5402</td>\n",
       "      <td>2006-05-29</td>\n",
       "      <td>3398</td>\n",
       "      <td>2</td>\n",
       "      <td>1699.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        index   mid        movie_title Release Date  Production Budget  \\\n",
       "166117   5126  5399  Sound of My Voice   2012-04-27             135000   \n",
       "166118   5126  5399  Sound of My Voice   2012-04-27             135000   \n",
       "166119   5126  5399  Sound of My Voice   2012-04-27             135000   \n",
       "166120   5126  5399  Sound of My Voice   2012-04-27             135000   \n",
       "166121   5128  5402             Cavite   2006-05-26               7000   \n",
       "\n",
       "        Domestic Gross  Worldwide Gross              genre  color  \\\n",
       "166117          408015           429448  Thriller/Suspense  Color   \n",
       "166118          408015           429448  Thriller/Suspense  Color   \n",
       "166119          408015           429448  Thriller/Suspense  Color   \n",
       "166120          408015           429448  Thriller/Suspense  Color   \n",
       "166121           70071            71644  Thriller/Suspense  Color   \n",
       "\n",
       "           director_name   ...     aspect_ratio  movie_facebook_likes   index  \\\n",
       "166117    Zal Batmanglij   ...             1.85                     0  166385   \n",
       "166118    Zal Batmanglij   ...             1.85                     0  166386   \n",
       "166119    Zal Batmanglij   ...             1.85                     0  166387   \n",
       "166120    Zal Batmanglij   ...             1.85                     0  166388   \n",
       "166121  Neill Dela Llana   ...              NaN                    74  166389   \n",
       "\n",
       "        movie        Date  Gross  Theaters  Per Theater Days  weekday  \n",
       "166117   5399  2012-06-25    654         6        109.0   60        0  \n",
       "166118   5399  2012-06-26    633         6        106.0   61        1  \n",
       "166119   5399  2012-06-27    720         6        120.0   62        2  \n",
       "166120   5399  2012-06-28    394         6         66.0   63        3  \n",
       "166121   5402  2006-05-29   3398         2       1699.0    4        0  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# join tables\n",
    "query3 = '''SELECT *\n",
    "            FROM movies m, daily d\n",
    "            WHERE m.mid = d.movie\n",
    "''' \n",
    "pd.read_sql(query3, conn).tail() # 166121 records! Why is that? Is that the same as in the Daily table? Why?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the **worldwide gross revenue per weekday** for Action movies for which the number of Facebook likes is known (i.e. is different than None)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(\"Worldwide Gross\")</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5009763369414</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4902551986425</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4933776173426</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4923690163044</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4949073481622</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4931005220738</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5028645388248</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(\"Worldwide Gross\")  weekday\n",
       "0           5009763369414        0\n",
       "1           4902551986425        1\n",
       "2           4933776173426        2\n",
       "3           4923690163044        3\n",
       "4           4949073481622        4\n",
       "5           4931005220738        5\n",
       "6           5028645388248        6"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sum by weekday\n",
    "query3 = '''SELECT SUM(\\\"Worldwide Gross\\\"), weekday\n",
    "            FROM movies m, daily d\n",
    "            WHERE m.mid = d.movie \n",
    "            GROUP BY weekday;\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the worldwide gross revenue per weekday **for Action movies** for which the number of Facebook likes is known (i.e. is different than None)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(\"Worldwide Gross\")</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1190285944090</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1173175112150</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1175987868340</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1175455791936</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1198329931417</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1192077174902</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1211900927914</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(\"Worldwide Gross\")  weekday\n",
       "0           1190285944090        0\n",
       "1           1173175112150        1\n",
       "2           1175987868340        2\n",
       "3           1175455791936        3\n",
       "4           1198329931417        4\n",
       "5           1192077174902        5\n",
       "6           1211900927914        6"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query3 = '''SELECT SUM(\\\"Worldwide Gross\\\"), weekday\n",
    "            FROM movies m, daily d\n",
    "            WHERE m.mid = d.movie AND genre=\"Action\" \n",
    "            GROUP BY weekday;\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the worldwide gross revenue per weekday for Action movies for which the **number of Facebook likes is known (i.e. is different than None)**?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>164000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>48000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>24000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>29000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>118000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>72000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>197000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>166000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>83000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>58000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>123000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>118000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>56000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4928</th>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4929</th>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4930</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4931</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4932</th>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4933</th>\n",
       "      <td>406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4934</th>\n",
       "      <td>21000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4935</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4936</th>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4937</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4938</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4939</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4940</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4941</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4942</th>\n",
       "      <td>26000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4943</th>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4944</th>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4945</th>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4946</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4947</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4948</th>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4949</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4950</th>\n",
       "      <td>667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4951</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4952</th>\n",
       "      <td>19000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4953</th>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4954</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4955</th>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4956</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4957</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4958 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     movie_facebook_likes\n",
       "0                   33000\n",
       "1                    None\n",
       "2                       0\n",
       "3                   85000\n",
       "4                  164000\n",
       "5                   48000\n",
       "6                   24000\n",
       "7                   29000\n",
       "8                       0\n",
       "9                  118000\n",
       "10                  72000\n",
       "11                 197000\n",
       "12                 166000\n",
       "13                  10000\n",
       "14                  83000\n",
       "15                  65000\n",
       "16                  58000\n",
       "17                   None\n",
       "18                      0\n",
       "19                   None\n",
       "20                      0\n",
       "21                 123000\n",
       "22                   5000\n",
       "23                 118000\n",
       "24                      0\n",
       "25                  56000\n",
       "26                   None\n",
       "27                 150000\n",
       "28                  40000\n",
       "29                      0\n",
       "...                   ...\n",
       "4928                   65\n",
       "4929                  142\n",
       "4930                    0\n",
       "4931                 None\n",
       "4932                   88\n",
       "4933                  406\n",
       "4934                21000\n",
       "4935                 None\n",
       "4936                  197\n",
       "4937                 None\n",
       "4938                 None\n",
       "4939                 None\n",
       "4940                    0\n",
       "4941                    0\n",
       "4942                26000\n",
       "4943                  132\n",
       "4944                  215\n",
       "4945                  116\n",
       "4946                 None\n",
       "4947                    0\n",
       "4948                  207\n",
       "4949                 None\n",
       "4950                  667\n",
       "4951                    0\n",
       "4952                19000\n",
       "4953                   74\n",
       "4954                 None\n",
       "4955                   84\n",
       "4956                   16\n",
       "4957                 None\n",
       "\n",
       "[4958 rows x 1 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query3 = '''SELECT movie_facebook_likes\n",
    "            FROM movies;\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>164000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>24000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>118000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>72000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>197000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>166000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>83000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>58000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>123000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>118000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>56000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>56000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>17000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>44000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3953</th>\n",
       "      <td>569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3954</th>\n",
       "      <td>443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3955</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3956</th>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3957</th>\n",
       "      <td>246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3958</th>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3959</th>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3960</th>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3961</th>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3962</th>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3963</th>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3964</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3965</th>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3966</th>\n",
       "      <td>406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3967</th>\n",
       "      <td>21000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3968</th>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3969</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3970</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3971</th>\n",
       "      <td>26000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3972</th>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3973</th>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3974</th>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3975</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3976</th>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3977</th>\n",
       "      <td>667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3978</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3979</th>\n",
       "      <td>19000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3980</th>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3981</th>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3982</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3983 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     movie_facebook_likes\n",
       "0                   33000\n",
       "1                       0\n",
       "2                   85000\n",
       "3                  164000\n",
       "4                   48000\n",
       "5                   24000\n",
       "6                   29000\n",
       "7                       0\n",
       "8                  118000\n",
       "9                   72000\n",
       "10                 197000\n",
       "11                 166000\n",
       "12                  10000\n",
       "13                  83000\n",
       "14                  65000\n",
       "15                  58000\n",
       "16                      0\n",
       "17                      0\n",
       "18                 123000\n",
       "19                   5000\n",
       "20                 118000\n",
       "21                      0\n",
       "22                  56000\n",
       "23                 150000\n",
       "24                  40000\n",
       "25                      0\n",
       "26                  56000\n",
       "27                      0\n",
       "28                  17000\n",
       "29                  44000\n",
       "...                   ...\n",
       "3953                  569\n",
       "3954                  443\n",
       "3955                    0\n",
       "3956                   44\n",
       "3957                  246\n",
       "3958                   83\n",
       "3959                   66\n",
       "3960                   42\n",
       "3961                   38\n",
       "3962                   65\n",
       "3963                  142\n",
       "3964                    0\n",
       "3965                   88\n",
       "3966                  406\n",
       "3967                21000\n",
       "3968                  197\n",
       "3969                    0\n",
       "3970                    0\n",
       "3971                26000\n",
       "3972                  132\n",
       "3973                  215\n",
       "3974                  116\n",
       "3975                    0\n",
       "3976                  207\n",
       "3977                  667\n",
       "3978                    0\n",
       "3979                19000\n",
       "3980                   74\n",
       "3981                   84\n",
       "3982                   16\n",
       "\n",
       "[3983 rows x 1 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query3 = '''SELECT movie_facebook_likes\n",
    "            FROM movies\n",
    "            WHERE movie_facebook_likes != \"None\"\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>164000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>24000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>118000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>72000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>197000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>166000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>83000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>58000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>123000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>118000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>56000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>56000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>17000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>44000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3953</th>\n",
       "      <td>569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3954</th>\n",
       "      <td>443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3955</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3956</th>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3957</th>\n",
       "      <td>246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3958</th>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3959</th>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3960</th>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3961</th>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3962</th>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3963</th>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3964</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3965</th>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3966</th>\n",
       "      <td>406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3967</th>\n",
       "      <td>21000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3968</th>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3969</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3970</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3971</th>\n",
       "      <td>26000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3972</th>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3973</th>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3974</th>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3975</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3976</th>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3977</th>\n",
       "      <td>667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3978</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3979</th>\n",
       "      <td>19000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3980</th>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3981</th>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3982</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3983 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     movie_facebook_likes\n",
       "0                   33000\n",
       "1                       0\n",
       "2                   85000\n",
       "3                  164000\n",
       "4                   48000\n",
       "5                   24000\n",
       "6                   29000\n",
       "7                       0\n",
       "8                  118000\n",
       "9                   72000\n",
       "10                 197000\n",
       "11                 166000\n",
       "12                  10000\n",
       "13                  83000\n",
       "14                  65000\n",
       "15                  58000\n",
       "16                      0\n",
       "17                      0\n",
       "18                 123000\n",
       "19                   5000\n",
       "20                 118000\n",
       "21                      0\n",
       "22                  56000\n",
       "23                 150000\n",
       "24                  40000\n",
       "25                      0\n",
       "26                  56000\n",
       "27                      0\n",
       "28                  17000\n",
       "29                  44000\n",
       "...                   ...\n",
       "3953                  569\n",
       "3954                  443\n",
       "3955                    0\n",
       "3956                   44\n",
       "3957                  246\n",
       "3958                   83\n",
       "3959                   66\n",
       "3960                   42\n",
       "3961                   38\n",
       "3962                   65\n",
       "3963                  142\n",
       "3964                    0\n",
       "3965                   88\n",
       "3966                  406\n",
       "3967                21000\n",
       "3968                  197\n",
       "3969                    0\n",
       "3970                    0\n",
       "3971                26000\n",
       "3972                  132\n",
       "3973                  215\n",
       "3974                  116\n",
       "3975                    0\n",
       "3976                  207\n",
       "3977                  667\n",
       "3978                    0\n",
       "3979                19000\n",
       "3980                   74\n",
       "3981                   84\n",
       "3982                   16\n",
       "\n",
       "[3983 rows x 1 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# alternatively\n",
    "query3 = '''SELECT movie_facebook_likes\n",
    "            FROM movies\n",
    "            WHERE movie_facebook_likes NOT NULL\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(\"Worldwide Gross\")</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>967954823800</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>953114144240</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>954160378024</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>956547395921</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>977894193546</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>973462004601</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>985891707437</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(\"Worldwide Gross\")  weekday\n",
       "0            967954823800        0\n",
       "1            953114144240        1\n",
       "2            954160378024        2\n",
       "3            956547395921        3\n",
       "4            977894193546        4\n",
       "5            973462004601        5\n",
       "6            985891707437        6"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# order results by Worldwide Gross (without SUM) does NOT give the desired result:\n",
    "query3 = '''SELECT SUM(\\\"Worldwide Gross\\\"), weekday\n",
    "            FROM movies m, daily d\n",
    "            WHERE m.mid = d.movie AND genre=\"Action\" AND movie_facebook_likes NOT NULL\n",
    "            GROUP BY weekday\n",
    "            ORDER BY \\\"Worldwide Gross\\\";\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(\"Worldwide Gross\")</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>953114144240</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>954160378024</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>956547395921</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>967954823800</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>973462004601</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>977894193546</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>985891707437</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(\"Worldwide Gross\")  weekday\n",
       "0            953114144240        1\n",
       "1            954160378024        2\n",
       "2            956547395921        3\n",
       "3            967954823800        0\n",
       "4            973462004601        5\n",
       "5            977894193546        4\n",
       "6            985891707437        6"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# order results by SUM(Worldwide Gross) does\n",
    "query3 = '''SELECT SUM(\\\"Worldwide Gross\\\"), weekday\n",
    "            FROM movies m, daily d\n",
    "            WHERE m.mid = d.movie AND genre=\"Action\" AND movie_facebook_likes NOT NULL\n",
    "            GROUP BY weekday\n",
    "            ORDER BY SUM(\\\"Worldwide Gross\\\");\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(\"Worldwide Gross\")</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>985891707437</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>977894193546</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>973462004601</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>967954823800</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>956547395921</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>954160378024</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>953114144240</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(\"Worldwide Gross\")  weekday\n",
       "0            985891707437        6\n",
       "1            977894193546        4\n",
       "2            973462004601        5\n",
       "3            967954823800        0\n",
       "4            956547395921        3\n",
       "5            954160378024        2\n",
       "6            953114144240        1"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change ordering from ascending to descending\n",
    "query3 = '''SELECT SUM(\\\"Worldwide Gross\\\"), weekday\n",
    "            FROM movies m, daily d\n",
    "            WHERE m.mid = d.movie AND genre=\"Action\" AND movie_facebook_likes NOT NULL\n",
    "            GROUP BY weekday\n",
    "            ORDER BY SUM(\\\"Worldwide Gross\\\") DESC;\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Worldwide Gross Revenue</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>985891707437</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>977894193546</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>973462004601</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>967954823800</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>956547395921</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>954160378024</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>953114144240</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Worldwide Gross Revenue  weekday\n",
       "0             985891707437        6\n",
       "1             977894193546        4\n",
       "2             973462004601        5\n",
       "3             967954823800        0\n",
       "4             956547395921        3\n",
       "5             954160378024        2\n",
       "6             953114144240        1"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change column name (alias)\n",
    "query3 = '''SELECT SUM(\\\"Worldwide Gross\\\") AS 'Worldwide Gross Revenue', weekday\n",
    "            FROM movies m, daily d\n",
    "            WHERE m.mid = d.movie AND genre=\"Action\" AND movie_facebook_likes NOT NULL\n",
    "            GROUP BY weekday\n",
    "            ORDER BY SUM(\\\"Worldwide Gross\\\") DESC;\n",
    "'''\n",
    "pd.read_sql(query3, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where can I find these Jupyter Notebooks? \n",
    "https://github.com/RoyKlaasseBos/Data-Analytics-for-Engineers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What am I supposed to do now? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 1. Programming Exercises HYP\n",
    "# 2. Theory Exercises HYP\n",
    "# 3. Graded Assignment 2 (module 6)\n",
    "# 4. Watch Video Lecture HYP"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}