Answering Business Questions using SQL/Answering Business Questions using SQL.ipynb

{
 "metadata": {
  "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.9.2"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python392jvsc74a57bd042ffdba7f0de544957ef69efdb450b60517fb3f2f7c6065358c1f9d2622fefe6",
   "display_name": "Python 3.9.2 64-bit"
  },
  "metadata": {
   "interpreter": {
    "hash": "42ffdba7f0de544957ef69efdb450b60517fb3f2f7c6065358c1f9d2622fefe6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "source": [
    "# Answering Business Questions using SQL\n",
    "\n",
    "*Chinook* is a fictional digital music shop, similar to iTunes store. The Chinook database contains tables that include information about the products (i.e. songs or albums) sold, employees, customers, and sales. We will be analyzing these business data in order to provide recommendations to Chinook."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///chinook.db"
   ]
  },
  {
   "source": [
    "## Data overview"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\nDone.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "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')]"
      ],
      "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>"
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, \n",
    "       type\n",
    "  FROM sqlite_master\n",
    " WHERE type IN (\"table\", \"view\");"
   ]
  },
  {
   "source": [
    "There are 11 tables in the Chinook database. \n",
    "\n",
    "1. **album**: This table contains an information of each album, i.e. name and artist.\n",
    "2. **artist**: This table stores artist names. Each artist has their own ID.\n",
    "3. **customer**: This table stores customers' information and the ID of sales agent assigned to each customer.\n",
    "4. **employee**: This table stores employees' information.\n",
    "5. **genre**: This table stores a list of music genre.\n",
    "6. **invoice**: This table provides header data of each invoice. \n",
    "7. **invoice_line**: This table provides data of each invoice line, including unit price and quantity.\n",
    "8. **media_type**: This table stores a list of media type.\n",
    "9. **playlist**: This table stores a list of playlists created in Chinook store.\n",
    "10. **playlist_track**: This table provides information of which tracks are added to each playlist.\n",
    "11. **track**: This table contains information of each track available in Chinook store.\n",
    "\n",
    "The database schema is provided in the below picture.\n",
    "\n",
    "![chinook_schema](pics/schema.png)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 1\n",
    "## Which albums should Chinook add to the store?\n",
    "\n",
    "**Scenario**\n",
    "\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",
    ">| Artist name | Genre |\n",
    "|-------------|-------|\n",
    "| Regal | Hip-Hop |\n",
    "| Red Tone | Punk |\n",
    "| Meteor and the Girls | Pop |\n",
    "| Slim Jim Bites | Blues |\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.\n",
    "\n",
    "**Question**\n",
    "\n",
    "Which artist should Chinook select to add to the store? "
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\nDone.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Rock', 561, 0.53),\n",
       " ('Alternative & Punk', 130, 0.12),\n",
       " ('Metal', 124, 0.12),\n",
       " ('R&B/Soul', 53, 0.05),\n",
       " ('Blues', 36, 0.03),\n",
       " ('Alternative', 35, 0.03),\n",
       " ('Pop', 22, 0.02),\n",
       " ('Latin', 22, 0.02),\n",
       " ('Hip Hop/Rap', 20, 0.02),\n",
       " ('Jazz', 14, 0.01)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>genre</th>\n        <th>num_tracks_sold</th>\n        <th>pct_tracks_sold</th>\n    </tr>\n    <tr>\n        <td>Rock</td>\n        <td>561</td>\n        <td>0.53</td>\n    </tr>\n    <tr>\n        <td>Alternative &amp; Punk</td>\n        <td>130</td>\n        <td>0.12</td>\n    </tr>\n    <tr>\n        <td>Metal</td>\n        <td>124</td>\n        <td>0.12</td>\n    </tr>\n    <tr>\n        <td>R&amp;B/Soul</td>\n        <td>53</td>\n        <td>0.05</td>\n    </tr>\n    <tr>\n        <td>Blues</td>\n        <td>36</td>\n        <td>0.03</td>\n    </tr>\n    <tr>\n        <td>Alternative</td>\n        <td>35</td>\n        <td>0.03</td>\n    </tr>\n    <tr>\n        <td>Pop</td>\n        <td>22</td>\n        <td>0.02</td>\n    </tr>\n    <tr>\n        <td>Latin</td>\n        <td>22</td>\n        <td>0.02</td>\n    </tr>\n    <tr>\n        <td>Hip Hop/Rap</td>\n        <td>20</td>\n        <td>0.02</td>\n    </tr>\n    <tr>\n        <td>Jazz</td>\n        <td>14</td>\n        <td>0.01</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH invoice_genre_usa AS (SELECT c.country,\n",
    "                                  il.track_id,\n",
    "                                  t.name track_name,\n",
    "                                  g.name genre\n",
    "                             FROM customer c\n",
    "                                  INNER JOIN invoice i ON c.customer_id = i.customer_id\n",
    "                                  INNER JOIN invoice_line il ON i.invoice_id = il.invoice_id\n",
    "                                  INNER JOIN track t ON il.track_id = t.track_id\n",
    "                                  INNER JOIN genre g ON t.genre_id = g.genre_id \n",
    "                                  WHERE country == 'USA')\n",
    "\n",
    "SELECT genre,\n",
    "       COUNT(track_name) num_tracks_sold,\n",
    "       ROUND(CAST(COUNT(track_name) AS FLOAT) / (SELECT COUNT(track_name)\n",
    "                                                   FROM invoice_genre_usa), 2) pct_tracks_sold\n",
    "  FROM invoice_genre_usa igu\n",
    " GROUP BY genre\n",
    " ORDER BY num_tracks_sold DESC\n",
    " LIMIT 10;"
   ]
  },
  {
   "source": [
    "**Conclusion**\n",
    "\n",
    "Chinook should select albums of *Red Tone*, *Slim Jim Bites*, and *Meteor and the Girls* to add to the stores.\n",
    "\n",
    "**Analysis**\n",
    "\n",
    "When we rank the genre of the four new artists by percentage of tracks sold, punk music comes first, followed by blues, pop, and hip-hop. Chinook should pick the top 3 genres that are sold the most.\n",
    "\n",
    "**Recommendation**\n",
    "\n",
    "The percentage of the three selected genres combined only account for 17% of all tracks sold in USA. Chinook might consider focusing more on finding new rock artist(s) to add to the stores."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 2\n",
    "## Which genre is the most popular in Chinook?\n",
    "\n",
    "Now that we already take a look at USA market, we will take one step back to see the popularity of each genre for the whole Chinook store."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\nDone.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Rock', 2635, 0.55),\n",
       " ('Metal', 619, 0.13),\n",
       " ('Alternative & Punk', 492, 0.1),\n",
       " ('Latin', 167, 0.04),\n",
       " ('R&B/Soul', 159, 0.03),\n",
       " ('Blues', 124, 0.03),\n",
       " ('Jazz', 121, 0.03),\n",
       " ('Alternative', 117, 0.02),\n",
       " ('Easy Listening', 74, 0.02),\n",
       " ('Pop', 63, 0.01)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>genre</th>\n        <th>num_tracks_sold</th>\n        <th>pct_tracks_sold</th>\n    </tr>\n    <tr>\n        <td>Rock</td>\n        <td>2635</td>\n        <td>0.55</td>\n    </tr>\n    <tr>\n        <td>Metal</td>\n        <td>619</td>\n        <td>0.13</td>\n    </tr>\n    <tr>\n        <td>Alternative &amp; Punk</td>\n        <td>492</td>\n        <td>0.1</td>\n    </tr>\n    <tr>\n        <td>Latin</td>\n        <td>167</td>\n        <td>0.04</td>\n    </tr>\n    <tr>\n        <td>R&amp;B/Soul</td>\n        <td>159</td>\n        <td>0.03</td>\n    </tr>\n    <tr>\n        <td>Blues</td>\n        <td>124</td>\n        <td>0.03</td>\n    </tr>\n    <tr>\n        <td>Jazz</td>\n        <td>121</td>\n        <td>0.03</td>\n    </tr>\n    <tr>\n        <td>Alternative</td>\n        <td>117</td>\n        <td>0.02</td>\n    </tr>\n    <tr>\n        <td>Easy Listening</td>\n        <td>74</td>\n        <td>0.02</td>\n    </tr>\n    <tr>\n        <td>Pop</td>\n        <td>63</td>\n        <td>0.01</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH invoice_genre AS (SELECT c.country,\n",
    "                              il.track_id,\n",
    "                              t.name track_name,\n",
    "                              g.name genre\n",
    "                         FROM customer c\n",
    "                              INNER JOIN invoice i ON c.customer_id = i.customer_id\n",
    "                              INNER JOIN invoice_line il ON i.invoice_id = il.invoice_id\n",
    "                              INNER JOIN track t ON il.track_id = t.track_id\n",
    "                              INNER JOIN genre g ON t.genre_id = g.genre_id)\n",
    "\n",
    "SELECT genre,\n",
    "       COUNT(track_name) num_tracks_sold,\n",
    "       ROUND(CAST(COUNT(track_name) AS FLOAT) / (SELECT COUNT(track_name)\n",
    "                                                   FROM invoice_genre), 2) pct_tracks_sold\n",
    "  FROM invoice_genre\n",
    " GROUP BY genre\n",
    " ORDER BY num_tracks_sold DESC\n",
    " LIMIT 10;"
   ]
  },
  {
   "source": [
    "When looking at the whole Chinook store, rock music is still at the top of our leaderboard. However, metal music seems to be slightly more popular than alternative & punk music at global level. Interestingly, pop music (which is short for popular music) ranks at the bottom."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 3\n",
    "## Which employee performs the best?\n",
    "\n",
    "**Scenario**\n",
    "\n",
    "> Each customer for the Chinook store gets assigned to a sales support agent within the company when they first make a purchase. You have been asked to 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",
    "**Question**\n",
    "\n",
    "What's the performance of each sales support agent?"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\nDone.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Jane Peacock', 1731.51, 21, '2017-04-01 00:00:00'),\n",
       " ('Margaret Park', 1584.0, 20, '2017-05-03 00:00:00'),\n",
       " ('Steve Johnson', 1393.92, 18, '2017-10-17 00:00:00')]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>employee_name</th>\n        <th>total_sales</th>\n        <th>num_customers</th>\n        <th>hire_date</th>\n    </tr>\n    <tr>\n        <td>Jane Peacock</td>\n        <td>1731.51</td>\n        <td>21</td>\n        <td>2017-04-01 00:00:00</td>\n    </tr>\n    <tr>\n        <td>Margaret Park</td>\n        <td>1584.0</td>\n        <td>20</td>\n        <td>2017-05-03 00:00:00</td>\n    </tr>\n    <tr>\n        <td>Steve Johnson</td>\n        <td>1393.92</td>\n        <td>18</td>\n        <td>2017-10-17 00:00:00</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH invoice_by_employee AS (SELECT i.invoice_id,\n",
    "                                    i.total,\n",
    "                                    c.country AS customer_country,\n",
    "                                    c.customer_id,\n",
    "                                    c.support_rep_id,\n",
    "                                    e.first_name || \" \" || e.last_name AS employee_name,\n",
    "                                    e.hire_date,\n",
    "                                    e.country AS employee_country\n",
    "                               FROM invoice i\n",
    "                                    INNER JOIN customer c ON i.customer_id = c.customer_id\n",
    "                                    INNER JOIN employee e ON c.support_rep_id = e.employee_id)\n",
    "\n",
    "SELECT employee_name, \n",
    "       ROUND(SUM(total), 2) AS total_sales,\n",
    "       COUNT(DISTINCT(customer_id)) AS num_customers,\n",
    "       hire_date\n",
    "  FROM invoice_by_employee\n",
    " GROUP BY employee_name\n",
    " ORDER BY total_sales DESC;"
   ]
  },
  {
   "source": [
    "**Conclusion**\n",
    "\n",
    "Jane Peacock has the best performance by total sales. However, it's worth noting that Jane has been hired the longest among 3 sales support agents. For better analysis, we could find average monthly sales generated by each agent and compare those numbers. \n",
    "\n",
    "Apart from hiring date, we also check number of customers assigned to each agent. It seems that total sales positively correlates with number of customers. The agent with the highest total sales has the highest number of customers assigned to, whereas the agent with the lowest total sales has the lowest number of customers."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 4\n",
    "## Which country generates the most sales to Chinook?"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\nDone.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('USA', 13, 1040.49, 80.04, 131, 7.94),\n",
       " ('Canada', 8, 535.59, 66.95, 76, 7.05),\n",
       " ('Brazil', 5, 427.68, 85.54, 61, 7.01),\n",
       " ('France', 5, 389.07, 77.81, 50, 7.78),\n",
       " ('Germany', 4, 334.62, 83.66, 41, 8.16),\n",
       " ('Czech Republic', 2, 273.24, 136.62, 30, 9.11),\n",
       " ('United Kingdom', 3, 245.52, 81.84, 28, 8.77),\n",
       " ('Portugal', 2, 185.13, 92.57, 29, 6.38),\n",
       " ('India', 2, 183.15, 91.58, 21, 8.72),\n",
       " ('Other', 15, 1094.94, 73.0, 147, 7.45)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>country</th>\n        <th>num_customers</th>\n        <th>total_sales</th>\n        <th>avg_sales_per_customer</th>\n        <th>num_orders</th>\n        <th>avg_order_value</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>131</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>76</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>61</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>50</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>41</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>30</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>28</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>29</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.58</td>\n        <td>21</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>147</td>\n        <td>7.45</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH customer_country AS (SELECT customer_id,\n",
    "                                 country,\n",
    "                                 COUNT(DISTINCT(customer_id)) AS num_customers,\n",
    "                                 CASE\n",
    "                                    WHEN COUNT(DISTINCT(customer_id)) = 1 THEN 'Other'\n",
    "                                    ELSE country\n",
    "                                    END AS new_country\n",
    "                            FROM customer\n",
    "                        GROUP BY country),\n",
    "\n",
    "     invoice_country AS (SELECT i.*,\n",
    "                                cc.new_country\n",
    "                           FROM invoice i\n",
    "                                LEFT JOIN customer c ON i.customer_id = c.customer_id\n",
    "                                LEFT JOIN customer_country cc ON c.country = cc.country)\n",
    "\n",
    "SELECT new_country AS country,\n",
    "       COUNT(DISTINCT(customer_id)) AS num_customers,\n",
    "       ROUND(SUM(total),2) AS total_sales,\n",
    "       ROUND(SUM(total) / COUNT(DISTINCT(customer_id)), 2) AS avg_sales_per_customer,\n",
    "       COUNT(DISTINCT(invoice_id)) AS num_orders,\n",
    "       ROUND(SUM(total) / COUNT(DISTINCT(invoice_id)), 2) AS avg_order_value\n",
    "  FROM (SELECT *, \n",
    "        CASE\n",
    "            WHEN new_country = 'Other' THEN 1\n",
    "            ELSE 0\n",
    "            END AS sort\n",
    "        FROM invoice_country)\n",
    " GROUP BY new_country\n",
    " ORDER BY sort ASC, total_sales DESC;"
   ]
  },
  {
   "source": [
    "USA has the highest total sales but average value of sales per customer or per order is not as high as Czech Republic or India. Chinook should focus on retaining the customers while trying to increase sales per order/customer, to boost overall sales.\n",
    "\n",
    "Meanwhile, given current number of customers in Czech Republic and India, there are definitely opportunities for Chinook to expand the business and reach more customers while try to maintain current sales per order/customer."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 5\n",
    "## Which one is better: Albums vs Individual Tracks?\n",
    "\n",
    "**Scenario**\n",
    "\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",
    ">The store does not let customers purchase a whole album, and then add individual tracks to that same purchase (unless they do that 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 are currently considering changing their purchasing strategy to save money. The strategy they are considering is to purchase only the most popular tracks from each album from record companies, instead of purchasing every track from an album.\n",
    "\n",
    "**Question**\n",
    "\n",
    "Should the management change the purchasing strategy?"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('album_purchase', 114, 0.19), ('single_track_purchase', 500, 0.81)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>purchase_type</th>\n        <th>num_invoices</th>\n        <th>pct_invoices</th>\n    </tr>\n    <tr>\n        <td>album_purchase</td>\n        <td>114</td>\n        <td>0.19</td>\n    </tr>\n    <tr>\n        <td>single_track_purchase</td>\n        <td>500</td>\n        <td>0.81</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH \n",
    "     /* Find number of tracks in each album */\n",
    "     track_per_album AS (SELECT a.album_id,\n",
    "                                a.title,\n",
    "                                COUNT(t.track_id) AS num_tracks\n",
    "                           FROM track t\n",
    "                                INNER JOIN album a ON t.album_id = a.album_id\n",
    "                          GROUP BY a.album_id\n",
    "                          ORDER BY a.album_id),\n",
    "     \n",
    "     /* Find number of tracks of each album in each invoice */\n",
    "     track_album_invoice AS (SELECT il.invoice_id,\n",
    "                                    a.album_id,\n",
    "                                    COUNT(il.track_id) AS num_tracks\n",
    "                               FROM invoice_line il\n",
    "                                    INNER JOIN track t ON il.track_id = t.track_id\n",
    "                                    INNER JOIN album a ON t.album_id = a.album_id\n",
    "                              GROUP BY il.invoice_id, a.album_id\n",
    "                              ORDER BY il.invoice_id, a.album_id),\n",
    "\n",
    "     /* Determine if an invoice is an album purchase */\n",
    "     album_purchases AS (SELECT tai.invoice_id,\n",
    "                                tai.album_id,\n",
    "                                tai.num_tracks,\n",
    "                                tpa.num_tracks AS num_tracks_album,\n",
    "                                CASE\n",
    "                                    WHEN \n",
    "                                        ((tai.num_tracks = tpa.num_tracks)\n",
    "                                        AND\n",
    "                                        (COUNT(DISTINCT(tai.album_id)))=1)\n",
    "                                    THEN \"album_purchase\"\n",
    "                                    ELSE \"single_track_purchase\"\n",
    "                                    END AS \"purchase_type\"\n",
    "                           FROM track_album_invoice tai\n",
    "                                LEFT JOIN track_per_album tpa ON tai.album_id = tpa.album_id\n",
    "                          GROUP BY invoice_id)\n",
    "\n",
    "SELECT purchase_type,\n",
    "       COUNT(DISTINCT(invoice_id)) AS num_invoices,\n",
    "       ROUND(CAST(COUNT(DISTINCT(invoice_id)) AS FLOAT) / (SELECT COUNT(*) \n",
    "                                                            FROM invoice), 2) AS pct_invoices\n",
    "  FROM album_purchases\n",
    " GROUP BY purchase_type;"
   ]
  },
  {
   "source": [
    "Over 80% of all invoices are single-track purchases. I would suggest that Chinook management changes their purchasing strategy going forward to only purchasing the popular tracks from each album."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 6\n",
    "## How many tracks have been purchased vs not purchased?"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('not purchased', 1697, 0.48), ('purchased', 1806, 0.52)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>purchase_status</th>\n        <th>num_tracks</th>\n        <th>pct_track_purchased</th>\n    </tr>\n    <tr>\n        <td>not purchased</td>\n        <td>1697</td>\n        <td>0.48</td>\n    </tr>\n    <tr>\n        <td>purchased</td>\n        <td>1806</td>\n        <td>0.52</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT (CASE\n",
    "            WHEN EXISTS (SELECT * FROM invoice_line il\n",
    "                          WHERE il.track_id = t.track_id)\n",
    "                 THEN \"purchased\"\n",
    "            ELSE \"not purchased\"\n",
    "            END) AS purchase_status,\n",
    "        COUNT(track_id) AS num_tracks,\n",
    "        ROUND(CAST(COUNT(track_id) AS FLOAT) / (SELECT COUNT(*) \n",
    "                                                  FROM track), 2) AS pct_track_purchased\n",
    "  FROM track t\n",
    " GROUP BY purchase_status;"
   ]
  },
  {
   "source": [
    "Almost half of the tracks has never been purchased. This strengthens our previous suggestion that Chinook management should change the purchasing strategy in order to avoid carrying non-revenue-generating tracks on their server."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 7\n",
    "## Which artist is used in the most playlists?"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Eugene Ormandy', 7),\n",
       " (\"The King's Singers\", 6),\n",
       " ('English Concert & Trevor Pinnock', 6),\n",
       " ('Berliner Philharmoniker & Herbert Von Karajan', 6),\n",
       " ('Academy of St. Martin in the Fields & Sir Neville Marriner', 6),\n",
       " ('Yo-Yo Ma', 5),\n",
       " ('Wilhelm Kempff', 5),\n",
       " ('Ton Koopman', 5),\n",
       " ('Sir Georg Solti, Sumi Jo & Wiener Philharmoniker', 5),\n",
       " ('Sir Georg Solti & Wiener Philharmoniker', 5)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>artist</th>\n        <th>num_playlists</th>\n    </tr>\n    <tr>\n        <td>Eugene Ormandy</td>\n        <td>7</td>\n    </tr>\n    <tr>\n        <td>The King&#x27;s Singers</td>\n        <td>6</td>\n    </tr>\n    <tr>\n        <td>English Concert &amp; Trevor Pinnock</td>\n        <td>6</td>\n    </tr>\n    <tr>\n        <td>Berliner Philharmoniker &amp; Herbert Von Karajan</td>\n        <td>6</td>\n    </tr>\n    <tr>\n        <td>Academy of St. Martin in the Fields &amp; Sir Neville Marriner</td>\n        <td>6</td>\n    </tr>\n    <tr>\n        <td>Yo-Yo Ma</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Wilhelm Kempff</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Ton Koopman</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Sir Georg Solti, Sumi Jo &amp; Wiener Philharmoniker</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Sir Georg Solti &amp; Wiener Philharmoniker</td>\n        <td>5</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH playlist_info AS (SELECT p.playlist_id,\n",
    "                              a.name AS artist\n",
    "                         FROM playlist p\n",
    "                              INNER JOIN playlist_track pt ON p.playlist_id = pt.playlist_id\n",
    "                              INNER JOIN track t ON pt.track_id = t.track_id\n",
    "                              INNER JOIN album ON t.album_id = album.album_id\n",
    "                              INNER JOIN artist a ON album.artist_id = a.artist_id)\n",
    "\n",
    "SELECT artist,\n",
    "       COUNT(DISTINCT(playlist_id)) AS num_playlists\n",
    "  FROM playlist_info\n",
    " GROUP BY artist\n",
    " ORDER BY num_playlists DESC\n",
    " LIMIT 10;"
   ]
  },
  {
   "source": [
    "The artist that appears the most in the playlist is *Eugene Ormandy*. He appears in 7 playlists. "
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 8\n",
    "## Which track is being purchased the most?\n",
    "\n",
    "We will find out which track is being purchased the most. We will count how many times the track appears on invoices and check the quantity being invoiced."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('War Pigs', 'Alternative', 'Cake: B-Sides and Rarities', 'Cake', 33, 33),\n",
       " ('Highway Chile', 'Rock', 'Are You Experienced?', 'Jimi Hendrix', 14, 14),\n",
       " ('Changes', 'Metal', 'Black Sabbath Vol. 4 (Remaster)', 'Black Sabbath', 14, 14),\n",
       " ('Are You Experienced?', 'Rock', 'Are You Experienced?', 'Jimi Hendrix', 14, 14),\n",
       " ('Third Stone From The Sun', 'Rock', 'Are You Experienced?', 'Jimi Hendrix', 13, 13),\n",
       " ('Put The Finger On You', 'Rock', 'For Those About To Rock We Salute You', 'AC/DC', 13, 13),\n",
       " ('Hey Joe', 'Rock', 'Are You Experienced?', 'Jimi Hendrix', 13, 13),\n",
       " ('We Are The Champions', 'Rock', 'Greatest Hits I', 'Queen', 12, 12),\n",
       " ('Love Or Confusion', 'Rock', 'Are You Experienced?', 'Jimi Hendrix', 12, 12),\n",
       " ('Drain You', 'Rock', 'From The Muddy Banks Of The Wishkah [live]', 'Nirvana', 12, 12)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>track_name</th>\n        <th>genre</th>\n        <th>album_name</th>\n        <th>artist_name</th>\n        <th>num_invoices</th>\n        <th>quantity</th>\n    </tr>\n    <tr>\n        <td>War Pigs</td>\n        <td>Alternative</td>\n        <td>Cake: B-Sides and Rarities</td>\n        <td>Cake</td>\n        <td>33</td>\n        <td>33</td>\n    </tr>\n    <tr>\n        <td>Highway Chile</td>\n        <td>Rock</td>\n        <td>Are You Experienced?</td>\n        <td>Jimi Hendrix</td>\n        <td>14</td>\n        <td>14</td>\n    </tr>\n    <tr>\n        <td>Changes</td>\n        <td>Metal</td>\n        <td>Black Sabbath Vol. 4 (Remaster)</td>\n        <td>Black Sabbath</td>\n        <td>14</td>\n        <td>14</td>\n    </tr>\n    <tr>\n        <td>Are You Experienced?</td>\n        <td>Rock</td>\n        <td>Are You Experienced?</td>\n        <td>Jimi Hendrix</td>\n        <td>14</td>\n        <td>14</td>\n    </tr>\n    <tr>\n        <td>Third Stone From The Sun</td>\n        <td>Rock</td>\n        <td>Are You Experienced?</td>\n        <td>Jimi Hendrix</td>\n        <td>13</td>\n        <td>13</td>\n    </tr>\n    <tr>\n        <td>Put The Finger On You</td>\n        <td>Rock</td>\n        <td>For Those About To Rock We Salute You</td>\n        <td>AC/DC</td>\n        <td>13</td>\n        <td>13</td>\n    </tr>\n    <tr>\n        <td>Hey Joe</td>\n        <td>Rock</td>\n        <td>Are You Experienced?</td>\n        <td>Jimi Hendrix</td>\n        <td>13</td>\n        <td>13</td>\n    </tr>\n    <tr>\n        <td>We Are The Champions</td>\n        <td>Rock</td>\n        <td>Greatest Hits I</td>\n        <td>Queen</td>\n        <td>12</td>\n        <td>12</td>\n    </tr>\n    <tr>\n        <td>Love Or Confusion</td>\n        <td>Rock</td>\n        <td>Are You Experienced?</td>\n        <td>Jimi Hendrix</td>\n        <td>12</td>\n        <td>12</td>\n    </tr>\n    <tr>\n        <td>Drain You</td>\n        <td>Rock</td>\n        <td>From The Muddy Banks Of The Wishkah [live]</td>\n        <td>Nirvana</td>\n        <td>12</td>\n        <td>12</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH invoice_track AS (SELECT il.invoice_id,\n",
    "                              t.name AS track_name,\n",
    "                              g.name AS genre,\n",
    "                              album.title AS album_name,\n",
    "                              a.name AS artist_name,\n",
    "                              il.quantity\n",
    "                         FROM invoice_line il\n",
    "                              INNER JOIN track t ON il.track_id = t.track_id\n",
    "                              INNER JOIN genre g ON t.genre_id = g.genre_id\n",
    "                              INNER JOIN album ON t.album_id = album.album_id\n",
    "                              INNER JOIN artist a ON album.artist_id = a.artist_id)\n",
    "\n",
    "SELECT track_name,\n",
    "       genre,\n",
    "       album_name,\n",
    "       artist_name,\n",
    "       COUNT(DISTINCT invoice_id) AS num_invoices,\n",
    "       SUM(quantity) AS quantity\n",
    "  FROM invoice_track\n",
    " GROUP BY track_name\n",
    " ORDER BY num_invoices DESC\n",
    " LIMIT 10;"
   ]
  },
  {
   "source": [
    "Although *Eugene Ormandy* is added to the most playlists, the track that is most-purchased is *War Pigs* by Cake. The song is purchased 33 times. Tracks from *Jimi Hendrix* seem to receive lots of love from customers as 5 out of top 10 most-purchased tracks are his songs."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 9\n",
    "## Which artist sells the most songs?"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * sqlite:///chinook.db\n",
      "Done.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Queen', 'Rock', 192, 3, 43),\n",
       " ('Jimi Hendrix', 'Rock', 187, 1, 17),\n",
       " ('Red Hot Chili Peppers', 'Rock', 130, 3, 48),\n",
       " ('Nirvana', 'Rock', 130, 2, 24),\n",
       " ('Pearl Jam', 'Rock', 129, 5, 58),\n",
       " (\"Guns N' Roses\", 'Rock', 124, 3, 42),\n",
       " ('AC/DC', 'Rock', 124, 2, 18),\n",
       " ('Foo Fighters', 'Rock', 121, 4, 43),\n",
       " ('The Rolling Stones', 'Rock', 117, 3, 39),\n",
       " ('Metallica', 'Metal', 106, 10, 68)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>artist_name</th>\n        <th>genre</th>\n        <th>quantity</th>\n        <th>num_albums</th>\n        <th>num_tracks</th>\n    </tr>\n    <tr>\n        <td>Queen</td>\n        <td>Rock</td>\n        <td>192</td>\n        <td>3</td>\n        <td>43</td>\n    </tr>\n    <tr>\n        <td>Jimi Hendrix</td>\n        <td>Rock</td>\n        <td>187</td>\n        <td>1</td>\n        <td>17</td>\n    </tr>\n    <tr>\n        <td>Red Hot Chili Peppers</td>\n        <td>Rock</td>\n        <td>130</td>\n        <td>3</td>\n        <td>48</td>\n    </tr>\n    <tr>\n        <td>Nirvana</td>\n        <td>Rock</td>\n        <td>130</td>\n        <td>2</td>\n        <td>24</td>\n    </tr>\n    <tr>\n        <td>Pearl Jam</td>\n        <td>Rock</td>\n        <td>129</td>\n        <td>5</td>\n        <td>58</td>\n    </tr>\n    <tr>\n        <td>Guns N&#x27; Roses</td>\n        <td>Rock</td>\n        <td>124</td>\n        <td>3</td>\n        <td>42</td>\n    </tr>\n    <tr>\n        <td>AC/DC</td>\n        <td>Rock</td>\n        <td>124</td>\n        <td>2</td>\n        <td>18</td>\n    </tr>\n    <tr>\n        <td>Foo Fighters</td>\n        <td>Rock</td>\n        <td>121</td>\n        <td>4</td>\n        <td>43</td>\n    </tr>\n    <tr>\n        <td>The Rolling Stones</td>\n        <td>Rock</td>\n        <td>117</td>\n        <td>3</td>\n        <td>39</td>\n    </tr>\n    <tr>\n        <td>Metallica</td>\n        <td>Metal</td>\n        <td>106</td>\n        <td>10</td>\n        <td>68</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH invoice_track AS (SELECT il.invoice_id,\n",
    "                              t.name AS track_name,\n",
    "                              g.name AS genre,\n",
    "                              album.title AS album_name,\n",
    "                              a.name AS artist_name,\n",
    "                              il.quantity\n",
    "                         FROM invoice_line il\n",
    "                              INNER JOIN track t ON il.track_id = t.track_id\n",
    "                              INNER JOIN genre g ON t.genre_id = g.genre_id\n",
    "                              INNER JOIN album ON t.album_id = album.album_id\n",
    "                              INNER JOIN artist a ON album.artist_id = a.artist_id)\n",
    "\n",
    "SELECT artist_name,\n",
    "       genre,\n",
    "       SUM(quantity) AS quantity,\n",
    "       COUNT(DISTINCT album_name) AS num_albums,\n",
    "       COUNT(DISTINCT track_name) AS num_tracks\n",
    "  FROM invoice_track\n",
    "GROUP BY artist_name\n",
    "ORDER BY quantity DESC\n",
    "LIMIT 10;"
   ]
  },
  {
   "source": [
    "The songs from *Queens* are sold the most, at 192 times from 43 tracks. *Jimi Hendrix* ranks the second at 187 times from 17 tracks. Out of our top 10, 9 artists offer rock music. This is also aligned with our first finding that rock music is the most popular genre in Chinook store."
   ],
   "cell_type": "markdown",
   "metadata": {}
  }
 ]
}