Answering Business Questions Using SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Using our SQL skills to answer business questions\n",
    "We will be using the **Chinook database** which is provided as a SQLite database file called `chinook.db`. A copy of the database schema (Dataquest version) is below. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect Jupyter Notebook to the database file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: None@chinook.db'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///chinook.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's query the database and see all tables and views in `chinook.db`\n",
    "Write a query to return information on the tables and views in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>album</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>artist</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>customer</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employee</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>genre</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>invoice</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>invoice_line</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>media_type</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>playlist</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>playlist_track</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>track</td>\n",
       "        <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tracks_by_album</td>\n",
       "        <td>view</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tracks_grpdby_album_id</td>\n",
       "        <td>view</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tracks_grpdby_invoice_id</td>\n",
       "        <td>view</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tracks_by_invoice</td>\n",
       "        <td>view</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>album_purchase_count</td>\n",
       "        <td>view</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('album', 'table'),\n",
       " ('artist', 'table'),\n",
       " ('customer', 'table'),\n",
       " ('employee', 'table'),\n",
       " ('genre', 'table'),\n",
       " ('invoice', 'table'),\n",
       " ('invoice_line', 'table'),\n",
       " ('media_type', 'table'),\n",
       " ('playlist', 'table'),\n",
       " ('playlist_track', 'table'),\n",
       " ('track', 'table'),\n",
       " ('tracks_by_album', 'view'),\n",
       " ('tracks_grpdby_album_id', 'view'),\n",
       " ('tracks_grpdby_invoice_id', 'view'),\n",
       " ('tracks_by_invoice', 'view'),\n",
       " ('album_purchase_count', 'view')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    name,\n",
    "    type\n",
    "FROM sqlite_master\n",
    "WHERE type IN ('table', 'view');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write one or two queries to get familiar with the tables and to practice running SQL in this interface. Use the schema diagram for reference.\n",
    "\n",
    "### 1 - Each Country's Best Customer\n",
    "\n",
    "Create a query to find the **customer from each country that has spent the most money at our store**, ordered alphabetically by country. Your query should return the following columns, in order:\n",
    "\n",
    "* `country` - The name of each country that we have a customer from.\n",
    "* `customer_name` - The first_name and last_name of the customer from that country with the most total purchases, separated by a space, eg `Luke Skywalker`.\n",
    "* `total_purchased` - The total dollar amount that customer has purchased."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>customer_name</th>\n",
       "        <th>total_purchased</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>Diego Gutiérrez</td>\n",
       "        <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>Mark Taylor</td>\n",
       "        <td>81.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austria</td>\n",
       "        <td>Astrid Gruber</td>\n",
       "        <td>69.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgium</td>\n",
       "        <td>Daan Peeters</td>\n",
       "        <td>60.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>Luís Gonçalves</td>\n",
       "        <td>108.9</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Argentina', 'Diego Gutiérrez', 39.6),\n",
       " ('Australia', 'Mark Taylor', 81.18),\n",
       " ('Austria', 'Astrid Gruber', 69.3),\n",
       " ('Belgium', 'Daan Peeters', 60.39),\n",
       " ('Brazil', 'Luís Gonçalves', 108.9)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH \n",
    "    all_customers AS (SELECT * FROM customer),\n",
    "\n",
    "    purchases AS (SELECT \n",
    "                 SUM(il.quantity * il.unit_price) total_purchases, \n",
    "                 c.customer_id\n",
    "                 FROM invoice_line il \n",
    "                 INNER JOIN invoice i ON il.invoice_id = i.invoice_id\n",
    "                 INNER JOIN customer c ON c.customer_id = i.customer_id\n",
    "                 GROUP BY i.customer_id)\n",
    "                 \n",
    "SELECT \n",
    "    all_cus.country country,\n",
    "    all_cus.first_name || ' ' || all_cus.last_name AS customer_name,\n",
    "    ROUND(MAX(p.total_purchases), 2) total_purchased \n",
    "    FROM all_customers all_cus\n",
    "    INNER JOIN purchases p ON p.customer_id = all_cus.customer_id\n",
    "    GROUP BY country \n",
    "    ORDER BY country\n",
    "    LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 Country's Best Customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>customer_name</th>\n",
       "        <th>total_purchased</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>František Wichterlová</td>\n",
       "        <td>144.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ireland</td>\n",
       "        <td>Hugh O&#x27;Reilly</td>\n",
       "        <td>114.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>Manoj Pareek</td>\n",
       "        <td>111.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>Luís Gonçalves</td>\n",
       "        <td>108.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>João Fernandes</td>\n",
       "        <td>102.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>François Tremblay</td>\n",
       "        <td>99.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Wyatt Girard</td>\n",
       "        <td>99.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>Enrique Muñoz</td>\n",
       "        <td>98.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>Jack Smith</td>\n",
       "        <td>98.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>Phil Hughes</td>\n",
       "        <td>98.01</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Czech Republic', 'František Wichterlová', 144.54),\n",
       " ('Ireland', \"Hugh O'Reilly\", 114.84),\n",
       " ('India', 'Manoj Pareek', 111.87),\n",
       " ('Brazil', 'Luís Gonçalves', 108.9),\n",
       " ('Portugal', 'João Fernandes', 102.96),\n",
       " ('Canada', 'François Tremblay', 99.99),\n",
       " ('France', 'Wyatt Girard', 99.99),\n",
       " ('Spain', 'Enrique Muñoz', 98.01),\n",
       " ('USA', 'Jack Smith', 98.01),\n",
       " ('United Kingdom', 'Phil Hughes', 98.01)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH \n",
    "    all_customers AS (SELECT * FROM customer),\n",
    "\n",
    "    purchases AS (SELECT \n",
    "                 SUM(il.quantity * il.unit_price) total_purchases, \n",
    "                 c.customer_id\n",
    "                 FROM invoice_line il \n",
    "                 INNER JOIN invoice i ON il.invoice_id = i.invoice_id\n",
    "                 INNER JOIN customer c ON c.customer_id = i.customer_id\n",
    "                 GROUP BY i.customer_id)\n",
    "                 \n",
    "SELECT \n",
    "    all_cus.country country,\n",
    "    all_cus.first_name || ' ' || all_cus.last_name AS customer_name,\n",
    "    ROUND(MAX(p.total_purchases), 2) total_purchased \n",
    "    FROM all_customers all_cus\n",
    "    INNER JOIN purchases p ON p.customer_id = all_cus.customer_id\n",
    "    GROUP BY country \n",
    "    ORDER BY total_purchased DESC\n",
    "    LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bottom 10 Country's Best Customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>customer_name</th>\n",
       "        <th>total_purchased</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Denmark</td>\n",
       "        <td>Kara Nielsen</td>\n",
       "        <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>Diego Gutiérrez</td>\n",
       "        <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>Lucas Mancini</td>\n",
       "        <td>50.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgium</td>\n",
       "        <td>Daan Peeters</td>\n",
       "        <td>60.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Netherlands</td>\n",
       "        <td>Johannes Van der Berg</td>\n",
       "        <td>65.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austria</td>\n",
       "        <td>Astrid Gruber</td>\n",
       "        <td>69.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norway</td>\n",
       "        <td>Bjørn Hansen</td>\n",
       "        <td>72.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sweden</td>\n",
       "        <td>Joakim Johansson</td>\n",
       "        <td>75.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Stanisław Wójcik</td>\n",
       "        <td>76.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hungary</td>\n",
       "        <td>Ladislav Kovács</td>\n",
       "        <td>78.21</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Denmark', 'Kara Nielsen', 37.62),\n",
       " ('Argentina', 'Diego Gutiérrez', 39.6),\n",
       " ('Italy', 'Lucas Mancini', 50.49),\n",
       " ('Belgium', 'Daan Peeters', 60.39),\n",
       " ('Netherlands', 'Johannes Van der Berg', 65.34),\n",
       " ('Austria', 'Astrid Gruber', 69.3),\n",
       " ('Norway', 'Bjørn Hansen', 72.27),\n",
       " ('Sweden', 'Joakim Johansson', 75.24),\n",
       " ('Poland', 'Stanisław Wójcik', 76.23),\n",
       " ('Hungary', 'Ladislav Kovács', 78.21)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH \n",
    "    all_customers AS (SELECT * FROM customer),\n",
    "\n",
    "    purchases AS (SELECT \n",
    "                 SUM(il.quantity * il.unit_price) total_purchases, \n",
    "                 c.customer_id\n",
    "                 FROM invoice_line il \n",
    "                 INNER JOIN invoice i ON il.invoice_id = i.invoice_id\n",
    "                 INNER JOIN customer c ON c.customer_id = i.customer_id\n",
    "                 GROUP BY i.customer_id)\n",
    "                 \n",
    "SELECT \n",
    "    all_cus.country country,\n",
    "    all_cus.first_name || ' ' || all_cus.last_name AS customer_name,\n",
    "    ROUND(MAX(p.total_purchases), 2) total_purchased \n",
    "    FROM all_customers all_cus\n",
    "    INNER JOIN purchases p ON p.customer_id = all_cus.customer_id\n",
    "    GROUP BY country \n",
    "    ORDER BY total_purchased\n",
    "    LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2 - Combining Multiple Joins with Subqueries\n",
    "\n",
    "Write a query that returns the **top 5 albums**, as calculated by the **number of times a track from that album has been purchased**. Your query should be sorted from most tracks purchased to least tracks purchased and return the following columns, in order:\n",
    "* `album`, the title of the album\n",
    "* `artist`, the artist who produced the album\n",
    "* `tracks_purchased` the total number of tracks purchased from that album"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album</th>\n",
       "        <th>artist</th>\n",
       "        <th>tracks_purchased</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Are You Experienced?</td>\n",
       "        <td>Jimi Hendrix</td>\n",
       "        <td>187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Faceless</td>\n",
       "        <td>Godsmack</td>\n",
       "        <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mezmerize</td>\n",
       "        <td>System Of A Down</td>\n",
       "        <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Get Born</td>\n",
       "        <td>JET</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Doors</td>\n",
       "        <td>The Doors</td>\n",
       "        <td>83</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Are You Experienced?', 'Jimi Hendrix', 187),\n",
       " ('Faceless', 'Godsmack', 96),\n",
       " ('Mezmerize', 'System Of A Down', 93),\n",
       " ('Get Born', 'JET', 90),\n",
       " ('The Doors', 'The Doors', 83)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH new_table AS (SELECT \n",
    "        il.track_id, \n",
    "        tm.tname track_name, \n",
    "        aa.name artist_name, \n",
    "        tm.mname track_type, \n",
    "        il.unit_price, \n",
    "        il.quantity, \n",
    "        aa.title album \n",
    "    FROM invoice_line il \n",
    "    INNER JOIN (SELECT \n",
    "                    t.track_id, \n",
    "                    t.name tname, \n",
    "                    m.name mname \n",
    "                FROM track t \n",
    "                INNER JOIN media_type m ON t.media_type_id = m.media_type_id) tm \n",
    "    ON il.track_id = tm.track_id \n",
    "    INNER JOIN (SELECT \n",
    "                    at.name, \n",
    "                    al.title, \n",
    "                    t.track_id \n",
    "                    FROM artist at \n",
    "                    INNER JOIN album al ON at.artist_id = al.artist_id \n",
    "                    INNER JOIN track t ON al.album_id = t.album_id) aa \n",
    "    ON tm.track_id = aa.track_id)\n",
    "\n",
    "SELECT \n",
    "    album, \n",
    "    artist_name artist, \n",
    "    COUNT(*) tracks_purchased \n",
    "FROM new_table\n",
    "GROUP BY album \n",
    "ORDER BY tracks_purchased DESC \n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting Albums to Purchase\n",
    "The Chinook record store has just signed a deal with a new record label, and you've been tasked with selecting the **first three albums** that will be added to the store, **from a list of four**. All four albums are by artists that don't have any tracks in the store right now - we have the artist names, and the genre of music they produce:\n",
    "\n",
    "    --------------------------------------\n",
    "    |     Artist Name       |    Genre   |\n",
    "    --------------------------------------\n",
    "    |     Regal             |   Hip-Hop  |\n",
    "    --------------------------------------\n",
    "    |    Real Tone          |    Punk    |\n",
    "    --------------------------------------\n",
    "    | Meteror and the Girls |     Pop    |\n",
    "    --------------------------------------\n",
    "    |    Slim Jim Bites     |    Blues   |\n",
    "    --------------------------------------\n",
    "    \n",
    "The record label specializes in artists from the **USA**, and they have given Chinook some money to advertise the new albums in the USA, so **we're interested in finding out which `genres` sell more in the USA**.\n",
    "\n",
    "1. Write a query that returns each **genre**, with the **number of tracks sold in the USA**:\n",
    "    * in **absolute numbers**\n",
    "    * in **percentages**.\n",
    "2. Write a paragraph that interprets the data and makes a recommendation for the three artists whose albums we should purchase for the store, based on sales of tracks from their genres."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>genre</th>\n",
       "        <th>number_of_tracks</th>\n",
       "        <th>pct_of_track_sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rock</td>\n",
       "        <td>561</td>\n",
       "        <td>53.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alternative &amp; Punk</td>\n",
       "        <td>130</td>\n",
       "        <td>12.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Metal</td>\n",
       "        <td>124</td>\n",
       "        <td>11.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>R&amp;B/Soul</td>\n",
       "        <td>53</td>\n",
       "        <td>5.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Blues</td>\n",
       "        <td>36</td>\n",
       "        <td>3.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alternative</td>\n",
       "        <td>35</td>\n",
       "        <td>3.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Latin</td>\n",
       "        <td>22</td>\n",
       "        <td>2.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pop</td>\n",
       "        <td>22</td>\n",
       "        <td>2.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hip Hop/Rap</td>\n",
       "        <td>20</td>\n",
       "        <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jazz</td>\n",
       "        <td>14</td>\n",
       "        <td>1.33</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Rock', 561, 53.38),\n",
       " ('Alternative & Punk', 130, 12.37),\n",
       " ('Metal', 124, 11.8),\n",
       " ('R&B/Soul', 53, 5.04),\n",
       " ('Blues', 36, 3.43),\n",
       " ('Alternative', 35, 3.33),\n",
       " ('Latin', 22, 2.09),\n",
       " ('Pop', 22, 2.09),\n",
       " ('Hip Hop/Rap', 20, 1.9),\n",
       " ('Jazz', 14, 1.33)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH \n",
    "    genre_usa AS\n",
    "            (SELECT \n",
    "                g.name genre,\n",
    "                g.genre_id genre_id\n",
    "            FROM genre g\n",
    "            INNER JOIN track t ON t.genre_id = g.genre_id\n",
    "            INNER JOIN invoice_line il ON t.track_id = il.track_id\n",
    "            INNER JOIN invoice i ON il.invoice_id = i.invoice_id\n",
    "            WHERE i.billing_country = 'USA'\n",
    "            )\n",
    "SELECT\n",
    "        genre,\n",
    "        COUNT(genre_id) number_of_tracks,\n",
    "        ROUND(CAST(COUNT(genre_id) AS Float)/(SELECT COUNT(*) FROM genre_usa) * 100, 2) pct_of_track_sold\n",
    "        FROM genre_usa\n",
    "        GROUP BY genre\n",
    "        ORDER by number_of_tracks DESC\n",
    "        LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Among the **top 10 genres** are **Alternative & Punk (with `12.37%`market share)**, **Blues (with `3.43%`market share)**, **Pop (with `2.09%`market share)** and **Hip Hop/Rap (with `1.9%`market share)** in this order. We can therefore recommend the following **three artists** whose albums can be purchased for the store:\n",
    "\n",
    "1. **Red Tone** (genre Punk)\n",
    "2. **Slim Jim Bites** (genre Blues)\n",
    "3. **Meteor and the Girls** (genre Pop)\n",
    "\n",
    "## Analyzing Employee Sales Performance\n",
    "**Each customer for the Chinook store gets assigned to a sales support agent** within the company when they first make a purchase. Analyze the purchases of customers belonging to each employee (sales support agent) to see if **any sales support agent is performing either better or worse than the others**.\n",
    "\n",
    "1. Write a query that finds the **total dollar amount of sales assigned to each sales support agent** within the company. Add any extra attributes for that employee that you find are relevant to the analysis.\n",
    "2. Write a short statement describing your results, and providing a possible interpretation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>employee_name</th>\n",
       "        <th>total_dollar_generated</th>\n",
       "        <th>num_customers_per_agent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jane Peacock</td>\n",
       "        <td>1731.51</td>\n",
       "        <td>212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Margaret Park</td>\n",
       "        <td>1584.0</td>\n",
       "        <td>214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steve Johnson</td>\n",
       "        <td>1393.92</td>\n",
       "        <td>188</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Jane Peacock', 1731.51, 212),\n",
       " ('Margaret Park', 1584.0, 214),\n",
       " ('Steve Johnson', 1393.92, 188)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    e.first_name ||' '||e.last_name employee_name,\n",
    "    ROUND(SUM(i.total), 2) total_dollar_generated,\n",
    "    COUNT(c.customer_id) num_customers_per_agent \n",
    "\n",
    "FROM employee e \n",
    "INNER JOIN customer c ON e.employee_id = c.support_rep_id\n",
    "INNER JOIN invoice i ON i.customer_id = c.customer_id \n",
    "GROUP BY employee_name\n",
    "HAVING e.title = 'Sales Support Agent' \n",
    "ORDER BY total_dollar_generated DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary\n",
    "* The Sales Agent **Jane Peacock** has genrated **`$1731.51`** from **`212`** customers. \n",
    "* The Sales Agent **Margaret Park** follows with **`$1584.0`** from **`214`** customers. \n",
    "* While Sales Agent **Steve Johnson** has genrated **`$1393.92`** from **`188`** customers.\n",
    "\n",
    "**The analysis could be extended to calculated the percentage of income generated by each Sales agent. The time period taken to genrate the dollar amount could also be taken into consideration.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Alternatively\n",
    "Without the pre-calculated **total** column in the **invoice table** this would be the approach to follow (***my opinion***)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>employee_name</th>\n",
       "        <th>total_dollar_generated</th>\n",
       "        <th>num_customers_per_agent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jane Peacock</td>\n",
       "        <td>1731.51</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Margaret Park</td>\n",
       "        <td>1584.0</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steve Johnson</td>\n",
       "        <td>1393.92</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Jane Peacock', 1731.51, 21),\n",
       " ('Margaret Park', 1584.0, 20),\n",
       " ('Steve Johnson', 1393.92, 18)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH \n",
    "    purchases AS (SELECT \n",
    "                 SUM(il.quantity * il.unit_price) total, \n",
    "                 c.customer_id,\n",
    "                 c.support_rep_id\n",
    "                 FROM invoice_line il \n",
    "                 INNER JOIN invoice i ON il.invoice_id = i.invoice_id\n",
    "                 INNER JOIN customer c ON c.customer_id = i.customer_id\n",
    "                 GROUP BY c.customer_id)\n",
    "    \n",
    "SELECT \n",
    "    e.first_name ||' '||e.last_name employee_name,\n",
    "    ROUND(SUM(p.total), 2) total_dollar_generated,\n",
    "    COUNT(p.customer_id) num_customers_per_agent \n",
    "\n",
    "FROM employee e \n",
    "INNER JOIN purchases p ON e.employee_id = p.support_rep_id \n",
    "GROUP BY employee_name\n",
    "HAVING e.title = 'Sales Support Agent' \n",
    "ORDER BY total_dollar_generated DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The logic above needs some checking...especially the number of customers per sales agent**\n",
    "\n",
    "## Analyzing Sales by Country\n",
    "The next task is to **analyze the sales data for customers from each different country**. You have been given guidance to **use the country value from the customers table**, and **ignore the country from the billing address in the invoice table**. For each country, determine:\n",
    "\n",
    "* total number of customers\n",
    "* total value of sales\n",
    "* average value of sales per customer\n",
    "* average order value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>number_of_customers</th>\n",
       "        <th>total_sales_value</th>\n",
       "        <th>avg_sales_per_customer</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>13</td>\n",
       "        <td>72.27</td>\n",
       "        <td>5.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>8</td>\n",
       "        <td>75.24</td>\n",
       "        <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>5</td>\n",
       "        <td>106.92</td>\n",
       "        <td>21.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>5</td>\n",
       "        <td>73.26</td>\n",
       "        <td>14.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>4</td>\n",
       "        <td>73.26</td>\n",
       "        <td>18.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>3</td>\n",
       "        <td>79.2</td>\n",
       "        <td>26.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>2</td>\n",
       "        <td>128.7</td>\n",
       "        <td>64.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>2</td>\n",
       "        <td>71.28</td>\n",
       "        <td>35.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>2</td>\n",
       "        <td>82.17</td>\n",
       "        <td>41.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>1</td>\n",
       "        <td>39.6</td>\n",
       "        <td>39.6</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('USA', 13, 72.27, 5.56),\n",
       " ('Canada', 8, 75.24, 9.4),\n",
       " ('Brazil', 5, 106.92, 21.38),\n",
       " ('France', 5, 73.26, 14.65),\n",
       " ('Germany', 4, 73.26, 18.32),\n",
       " ('United Kingdom', 3, 79.2, 26.4),\n",
       " ('Czech Republic', 2, 128.7, 64.35),\n",
       " ('India', 2, 71.28, 35.64),\n",
       " ('Portugal', 2, 82.17, 41.09),\n",
       " ('Argentina', 1, 39.6, 39.6)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH \n",
    "    customers_by_country AS (SELECT \n",
    "                                 customer_id,\n",
    "                                 country, \n",
    "                                 COUNT(customer_id) number_of_customers \n",
    "                             FROM customer\n",
    "                             GROUP BY country\n",
    "                             ORDER BY number_of_customers\n",
    "                            ),\n",
    "\n",
    "    purchase AS (SELECT \n",
    "                 ROUND(SUM(il.quantity * il.unit_price), 2) total_sales_value,\n",
    "                 \n",
    "                 c.customer_id\n",
    "                 FROM invoice_line il \n",
    "                 INNER JOIN invoice i ON il.invoice_id = i.invoice_id\n",
    "                 INNER JOIN customer c ON c.customer_id = i.customer_id\n",
    "                 GROUP BY c.customer_id)\n",
    "                 \n",
    "SELECT \n",
    "    c_by_c.country,\n",
    "    c_by_c.number_of_customers,\n",
    "    p.total_sales_value,\n",
    "    ROUND(p.total_sales_value / c_by_c.number_of_customers, 2) avg_sales_per_customer\n",
    "    FROM customers_by_country c_by_c\n",
    "    INNER JOIN purchase p ON p.customer_id = c_by_c.customer_id\n",
    "    GROUP BY c_by_c.country\n",
    "    ORDER BY number_of_customers DESC\n",
    "    LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Albums vs Individual Tracks\n",
    "The Chinook store is setup in a way that allows customer to make purchases in one of the two ways:\n",
    "\n",
    "1. purchase a whole album\n",
    "2. purchase a collection of one or more individual tracks.\n",
    "\n",
    "\n",
    "* The store does not allow customers purchase a whole album, and then add individual tracks to that same purchase (but customers can purchase all tracks in an album by choosing each track manually. This case is rare and should be ignored in the analysis).\n",
    "\n",
    "\n",
    "* Albums with very few tracks should also be ignored in the analysis since most customers might tend to buy such whole albums.\n",
    "\n",
    "\n",
    "* When customers purchase albums they are charged the same price as if they had purchased each of those tracks separately.\n",
    "\n",
    "\n",
    "* To save money, companies are considering to purchase only the most popular tracks from each album instead of purchasing every track from an album.\n",
    "\n",
    "\n",
    "**We have been asked to find out `what percentage of purchases are individual tracks vs whole albums`, so that management can use this data to understand the effect this decision might have on overall revenue.**\n",
    "\n",
    "\n",
    "* In order to answer the question, we're going to have to **identify whether each invoice has all the tracks from an album**. We can do this by getting the list of tracks from an invoice and comparing it to the list of tracks from an album.\n",
    "\n",
    "\n",
    "* We can find an album by looking up the album that one of the purchased tracks belongs to.\n",
    "\n",
    "### The Approach\n",
    "1. Write a query that categorizes each invoice as either an album purchase or not, and calculates the following summary statistics:\n",
    "\n",
    "    * Number of invoices\n",
    "    * Percentage of invoices \n",
    "    \n",
    "2. Write one to two sentences explaining your findings, and making a prospective recommendation on whether the Chinook store should continue to buy full albums from record companies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>number_of_invoices</th>\n",
       "        <th>pct_of_invoices</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>4.89</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(30, 4.89)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS tracks_by_invoice;\n",
    "\n",
    "CREATE VIEW tracks_by_invoice AS\n",
    "    SELECT \n",
    "        il.invoice_id,\n",
    "        COUNT(t.track_id) Tracks_on_Invoice,\n",
    "        t.album_id album_id\n",
    "    FROM invoice_line il\n",
    "    INNER JOIN track t on t.track_id = il.track_id\n",
    "    GROUP BY 1,3 ORDER BY 1;\n",
    "\n",
    "SELECT\n",
    "    tracks_by_invoice.*,\n",
    "    tracks_by_album.Tracks_on_Album\n",
    "    FROM tracks_by_invoice\n",
    "    INNER JOIN tracks_by_album on tracks_by_album.album_id = tracks_by_invoice.album_id;\n",
    "\n",
    "DROP VIEW IF EXISTS album_purchase_count;\n",
    "\n",
    "CREATE VIEW album_purchase_count AS\n",
    "    SELECT \n",
    "        tracks_by_invoice.invoice_id, \n",
    "        CASE    \n",
    "            WHEN(\n",
    "                tracks_by_invoice.Tracks_on_Invoice = tracks_by_album.Tracks_on_Album\n",
    "                AND NOT EXISTS (\n",
    "                    SELECT 1 FROM invoice_line il\n",
    "                    LEFT JOIN track t ON t.track_id = il.track_id\n",
    "                    WHERE il.invoice_id = tracks_by_invoice.invoice_id\n",
    "                    AND t.album_id = tracks_by_album.album_id\n",
    "                    AND t.track_id NOT IN (\n",
    "                        SELECT track_id FROM invoice_line\n",
    "                        WHERE invoice_id = tracks_by_invoice.invoice_id\n",
    "                    )\n",
    "                )\n",
    "            ) THEN 'Album Purchase'       \n",
    "            ELSE 'Individual Tracks'    \n",
    "        END AS album_purchase_or_not\n",
    "    FROM tracks_by_invoice\n",
    "    INNER JOIN tracks_by_album ON tracks_by_album.album_id = tracks_by_invoice.album_id;\n",
    "    \n",
    "\n",
    "SELECT \n",
    "    COUNT(invoice_id) number_of_invoices,\n",
    "    ROUND(CAST(COUNT(invoice_id) AS FLOAT)/ (SELECT COUNT(invoice_id)\n",
    "                       FROM invoice) * 100, 2) pct_of_invoices\n",
    "    FROM album_purchase_count\n",
    "    WHERE album_purchase_or_not = 'Album Purchase';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}