Answering Business Questions Using SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Answering Business Questions Using SQL\n",
    "by Nicholas Archambault\n",
    "\n",
    "This project will answer questions about the Chinook database, a collection of eleven tables of information on a fictional, digital iTunes-like music store. The data includes information on employees, customers, purchases and product information."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///chinook.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chinook.db\n",
      "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",
       "</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')]"
      ]
     },
     "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": [
    "## Selecting Albums to Purchase\n",
    "\n",
    "The fictional scenario at hand is as follows: the Chinook record store has just signed a deal with a new record label, and we are 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 know the artist names and the genre of music they produce. The four genres from which to choose are Hip Hop, Punk, Pop, and Blues. \n",
    "\n",
    "The goal of this section is to identify which of these genres sell best in the United States, then make a recommendation for which three albums should be purchased of the four options.\n",
    "\n",
    "Our query yields the number of tracks sold in the United States by genre, in terms of both absolute numbers and percentages of total tracks sold."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>genre</th>\n",
       "        <th>tracks_sold</th>\n",
       "        <th>percentage_sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rock</td>\n",
       "        <td>561</td>\n",
       "        <td>0.5338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alternative &amp; Punk</td>\n",
       "        <td>130</td>\n",
       "        <td>0.1237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Metal</td>\n",
       "        <td>124</td>\n",
       "        <td>0.118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>R&amp;B/Soul</td>\n",
       "        <td>53</td>\n",
       "        <td>0.0504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Blues</td>\n",
       "        <td>36</td>\n",
       "        <td>0.0343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alternative</td>\n",
       "        <td>35</td>\n",
       "        <td>0.0333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Latin</td>\n",
       "        <td>22</td>\n",
       "        <td>0.0209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pop</td>\n",
       "        <td>22</td>\n",
       "        <td>0.0209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hip Hop/Rap</td>\n",
       "        <td>20</td>\n",
       "        <td>0.019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jazz</td>\n",
       "        <td>14</td>\n",
       "        <td>0.0133</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Rock', 561, 0.5338),\n",
       " ('Alternative & Punk', 130, 0.1237),\n",
       " ('Metal', 124, 0.118),\n",
       " ('R&B/Soul', 53, 0.0504),\n",
       " ('Blues', 36, 0.0343),\n",
       " ('Alternative', 35, 0.0333),\n",
       " ('Latin', 22, 0.0209),\n",
       " ('Pop', 22, 0.0209),\n",
       " ('Hip Hop/Rap', 20, 0.019),\n",
       " ('Jazz', 14, 0.0133)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH usa_tracks AS\n",
    "   (\n",
    "    SELECT il.* FROM invoice_line il\n",
    "    INNER JOIN invoice i on il.invoice_id = i.invoice_id\n",
    "    INNER JOIN customer c on i.customer_id = c.customer_id\n",
    "    WHERE c.country = \"USA\"\n",
    "   )\n",
    "\n",
    "SELECT\n",
    "      g.name genre,\n",
    "      COUNT(ut.invoice_line_id) tracks_sold,\n",
    "      ROUND(\n",
    "          CAST(COUNT(ut.invoice_line_id) AS FLOAT) / (\n",
    "            SELECT COUNT(*) FROM usa_tracks), \n",
    "          4) percentage_sold\n",
    "FROM  usa_tracks ut\n",
    "INNER JOIN track t on t.track_id = ut.track_id\n",
    "INNER JOIN genre g on g.genre_id = t.genre_id\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Based on this preliminary analysis, we should purchase the Punk, Blues, and Pop albums. We should keep in mind, though, that theses three genres make up just 17% of total tracks sold. A more promising category might be Rock, which accounts for 53% of total sales."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyzing Employee Performance\n",
    "\n",
    "Each customer for the Chinook store gets assigned to a sales support agent within the company when they first make a purchase. We can analyze the purchases of customers belonging to each employee to see if any sales support agent is performing either better or worse than the others.\n",
    "\n",
    "It might prove useful to consider whether any extra columns from the employee table explain any variance that is seen, or whether the variance might instead be indicative of employee performance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>employee</th>\n",
       "        <th>hire_date</th>\n",
       "        <th>total_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jane Peacock</td>\n",
       "        <td>2017-04-01 00:00:00</td>\n",
       "        <td>1731.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Margaret Park</td>\n",
       "        <td>2017-05-03 00:00:00</td>\n",
       "        <td>1584.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steve Johnson</td>\n",
       "        <td>2017-10-17 00:00:00</td>\n",
       "        <td>1393.92</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Jane Peacock', '2017-04-01 00:00:00', 1731.51),\n",
       " ('Margaret Park', '2017-05-03 00:00:00', 1584.0),\n",
       " ('Steve Johnson', '2017-10-17 00:00:00', 1393.92)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH emp AS\n",
    "    (\n",
    "     SELECT\n",
    "         i.customer_id,\n",
    "         c.support_rep_id,\n",
    "         SUM(i.total) total\n",
    "     FROM invoice i\n",
    "     INNER JOIN customer c ON i.customer_id = c.customer_id\n",
    "     GROUP BY 1\n",
    "    )\n",
    "\n",
    "SELECT\n",
    "    (e.first_name || \" \" || e.last_name) employee,\n",
    "    e.hire_date,\n",
    "    ROUND(SUM(emp.total),2) total_sales\n",
    "FROM emp\n",
    "INNER JOIN employee e ON e.employee_id = emp.support_rep_id\n",
    "GROUP BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Analysis shows that there is a ~20% difference in sales performance between Jane and Steve. This difference, however, corresponds to the difference between their hiring dates, suggesting that quality of the two employees' performances is similar."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyzing Sales by Country\n",
    "\n",
    "Next, we can examine country-specific metrics, specifically total customers, total sales, average value of sales per customer, and average order value. Countries with only a single customer will be grouped into an 'Other' category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>total_customers</th>\n",
       "        <th>total_sales</th>\n",
       "        <th>sales_per_customer</th>\n",
       "        <th>sales_per_order</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>13</td>\n",
       "        <td>1040.49</td>\n",
       "        <td>80.04</td>\n",
       "        <td>7.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>8</td>\n",
       "        <td>535.59</td>\n",
       "        <td>66.95</td>\n",
       "        <td>7.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>5</td>\n",
       "        <td>427.68</td>\n",
       "        <td>85.54</td>\n",
       "        <td>7.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>5</td>\n",
       "        <td>389.07</td>\n",
       "        <td>77.81</td>\n",
       "        <td>7.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>4</td>\n",
       "        <td>334.62</td>\n",
       "        <td>83.66</td>\n",
       "        <td>8.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>2</td>\n",
       "        <td>273.24</td>\n",
       "        <td>136.62</td>\n",
       "        <td>9.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>3</td>\n",
       "        <td>245.52</td>\n",
       "        <td>81.84</td>\n",
       "        <td>8.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>2</td>\n",
       "        <td>185.13</td>\n",
       "        <td>92.57</td>\n",
       "        <td>6.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>2</td>\n",
       "        <td>183.15</td>\n",
       "        <td>91.57</td>\n",
       "        <td>8.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Other</td>\n",
       "        <td>15</td>\n",
       "        <td>1094.94</td>\n",
       "        <td>73.0</td>\n",
       "        <td>7.45</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('USA', 13, 1040.49, 80.04, 7.94),\n",
       " ('Canada', 8, 535.59, 66.95, 7.05),\n",
       " ('Brazil', 5, 427.68, 85.54, 7.01),\n",
       " ('France', 5, 389.07, 77.81, 7.78),\n",
       " ('Germany', 4, 334.62, 83.66, 8.16),\n",
       " ('Czech Republic', 2, 273.24, 136.62, 9.11),\n",
       " ('United Kingdom', 3, 245.52, 81.84, 8.77),\n",
       " ('Portugal', 2, 185.13, 92.57, 6.38),\n",
       " ('India', 2, 183.15, 91.57, 8.72),\n",
       " ('Other', 15, 1094.94, 73.0, 7.45)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH country_data AS (\n",
    "    SELECT\n",
    "        c.customer_id,\n",
    "        i.total,\n",
    "        i.invoice_id,\n",
    "        CASE\n",
    "            WHEN \n",
    "                (SELECT COUNT(*) \n",
    "                 FROM customer \n",
    "                 WHERE country = c.country) = 1\n",
    "            THEN \"Other\"\n",
    "            ELSE c.country\n",
    "            END\n",
    "            AS country\n",
    "    FROM customer c\n",
    "    INNER JOIN invoice i ON c.customer_id = i.customer_id\n",
    "    )\n",
    "\n",
    "SELECT\n",
    "    country,\n",
    "    COUNT(DISTINCT(customer_id)) total_customers,\n",
    "    ROUND(SUM(total), 2) total_sales,\n",
    "    ROUND(SUM(total)/COUNT(DISTINCT(customer_id)), 2) sales_per_customer,\n",
    "    ROUND(SUM(total)/COUNT(invoice_id), 2) sales_per_order\n",
    "FROM \n",
    "    (\n",
    "    SELECT\n",
    "        cd.*,\n",
    "        CASE\n",
    "            WHEN cd.country = \"Other\" THEN 0\n",
    "            ELSE 1\n",
    "            END\n",
    "            AS sort\n",
    "    FROM country_data cd\n",
    "    )\n",
    "GROUP BY 1\n",
    "ORDER BY sort DESC, total_sales DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We find that the United States leads in total customers and sales. Based on the data, there may be opportunity for  market entry or increased advertising in Czech Republic, United Kingdom and India, all of which feature high values for average sales per customer and average sales per order."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Albums vs. Tracks\n",
    "\n",
    "Finally, we can evaluate how individual tracks are purchased. The store does not let customers purchase a whole album, and then add individual tracks to that same purchase (unless they do so by choosing each track manually). When customers purchase albums, they are charged the same price as if they had purchased each of those tracks separately.\n",
    "\n",
    "Management is currently considering changing their purchasing strategy to save money. The strategy under consideration is to purchase only the most popular tracks from each album from record companies, instead of purchasing every track from an album.\n",
    "\n",
    "We will explore 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",
    "In order to answer the question, we'll 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. We can then find the album to compare the purchase to by looking up the album that one of the purchased tracks belongs to. It doesn't matter which track we pick, since if it's an album purchase, that album will be the same for all tracks.\n",
    "\n",
    "We can use the `EXCEPT` operator wrapped in a `CASE` statement to create a new column that evaluates whether or not each invoice was an album purchase. Summing the binary totals of this column will reveal the desired table of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_purchase</th>\n",
       "        <th>invoices</th>\n",
       "        <th>pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>No</td>\n",
       "        <td>500</td>\n",
       "        <td>0.814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Yes</td>\n",
       "        <td>114</td>\n",
       "        <td>0.186</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('No', 500, 0.814), ('Yes', 114, 0.186)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH first_track_invoice AS (\n",
    "    SELECT \n",
    "        il.invoice_id invoice_id,\n",
    "        MIN(track_id) first_track\n",
    "        \n",
    "    FROM invoice_line il\n",
    "    GROUP BY 1\n",
    "    )\n",
    "SELECT \n",
    "    album_purchase,\n",
    "    COUNT(invoice_id) invoices,\n",
    "    ROUND(CAST(COUNT(invoice_id) AS FLOAT)/ (SELECT COUNT(*) FROM invoice), 3) pct\n",
    "    \n",
    "    FROM (\n",
    "        SELECT\n",
    "            fti.*,\n",
    "            CASE\n",
    "                WHEN (\n",
    "                    SELECT t.track_id\n",
    "                    FROM track t\n",
    "                    WHERE t.album_id = (SELECT t2.album_id FROM track t2\n",
    "                                        WHERE t2.track_id = fti.first_track)\n",
    "                    \n",
    "                    EXCEPT\n",
    "                    \n",
    "                    SELECT il2.track_id FROM invoice_line il2\n",
    "                    WHERE il2.invoice_id = fti.invoice_id \n",
    "                    ) IS NULL\n",
    "                AND\n",
    "                    (\n",
    "                    SELECT il2.track_id FROM invoice_line il2\n",
    "                    WHERE il2.invoice_id = fti.invoice_id\n",
    "                    \n",
    "                    \n",
    "                    EXCEPT\n",
    "        \n",
    "                    SELECT t.track_id FROM track t\n",
    "                    WHERE t.album_id = (\n",
    "                        SELECT t2.album_id FROM track t2\n",
    "                        WHERE t2.track_id = fti.first_track)\n",
    "                    ) IS NULL\n",
    "        \n",
    "            THEN \"Yes\"\n",
    "            ELSE \"No\"\n",
    "            END\n",
    "            AS \"album_purchase\"\n",
    "        FROM first_track_invoice fti\n",
    "        )\n",
    "GROUP BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "This data reveals that album purchases account for 18.6% of all purchased tracks, nearly a fifth of all revenue. Based on these findings, we can recommend that the company avoid purchasing only select tracks from albums, since that could result in a loss of one fifth of revenue."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "\n",
    "In this project, we have addressed a number of business questions using SQL queries and joins. We reached the following conclusions, to be recommended to the company:\n",
    "   * The company should purchase Pop, Punk, and Blues albums and be on the lookout for opportunities to purchase more Rock albums, as the Rock genre accounts for over half of all purchases.\n",
    "   * The company does most of its business in the United States, but new opportunities for growth could be present in Czech Republic, United Kingdom, and India.\n",
    "   * There is not an appreciable difference in the overall quality of sales employee performance.\n",
    "   * The company should refrain from changing its purchasing policy to one that buys only certain tracks from albums. Doing so risks losing ~20% of total revenue. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}