Leveraging SQL for Business Decisions of a Record Score.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Leveraging SQL for Business Decisions of a Record Store\n",
    "\n",
    "### The Database\n",
    "SQL remains one of the most important tools in connecting with databases and answering business questions. In this project, I use SQL to connect to the [Chinook database](https://github.com/lerocha/chinook-database), a sample database available for MySQL, SQL Server, Oracle, etc.\n",
    "\n",
    "The Chinook data model represents a digital media store which includes tables for artists, albums, media tracks, invoices and customers.\n",
    "\n",
    "### The Goal\n",
    "\n",
    "I intend to write SQL queries and subqueries to analyze the data found in the Chinook database to answer some hypothetical business questions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: None@chinook.db'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# connecting Jupyter to the database file\n",
    "\n",
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///chinook.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quick Query for Data Exploration\n",
    "\n",
    "I'll start by getting familiar with the data. I can query the database to get a list of all tables and views in the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "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",
       "</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",
    "\n",
    "SELECT\n",
    "    name,\n",
    "    type\n",
    "FROM sqlite_master\n",
    "WHERE type IN (\"table\",\"view\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selecting Albums to Offer in Store\n",
    "\n",
    "### The Problem\n",
    "\n",
    "The Chinook record store has just signed a deal with a new record label, and I'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 - I have the artist names, and the genre of music they produce:\n",
    "\n",
    "#### Artist Name\t| 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, so I'm interested in finding out which genres sell the best in the USA.\n",
    "\n",
    "**I need to write a query to find out which genres sell the most tracks in the USA and make a recommendation for the three artists whose albums we should purchase for the store.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>genre_id</th>\n",
       "        <th>genre</th>\n",
       "        <th>tracks_sold</th>\n",
       "        <th>percentage_sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>1</td>\n",
       "        <td>Rock</td>\n",
       "        <td>561</td>\n",
       "        <td>43.154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>4</td>\n",
       "        <td>Alternative &amp; Punk</td>\n",
       "        <td>130</td>\n",
       "        <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>3</td>\n",
       "        <td>Metal</td>\n",
       "        <td>124</td>\n",
       "        <td>9.538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>14</td>\n",
       "        <td>R&amp;B/Soul</td>\n",
       "        <td>53</td>\n",
       "        <td>4.077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>6</td>\n",
       "        <td>Blues</td>\n",
       "        <td>36</td>\n",
       "        <td>2.769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>23</td>\n",
       "        <td>Alternative</td>\n",
       "        <td>35</td>\n",
       "        <td>2.692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>7</td>\n",
       "        <td>Latin</td>\n",
       "        <td>22</td>\n",
       "        <td>1.692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>9</td>\n",
       "        <td>Pop</td>\n",
       "        <td>22</td>\n",
       "        <td>1.692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>17</td>\n",
       "        <td>Hip Hop/Rap</td>\n",
       "        <td>20</td>\n",
       "        <td>1.538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>2</td>\n",
       "        <td>Jazz</td>\n",
       "        <td>14</td>\n",
       "        <td>1.077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>12</td>\n",
       "        <td>Easy Listening</td>\n",
       "        <td>13</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>8</td>\n",
       "        <td>Reggae</td>\n",
       "        <td>6</td>\n",
       "        <td>0.462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>15</td>\n",
       "        <td>Electronica/Dance</td>\n",
       "        <td>5</td>\n",
       "        <td>0.385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>24</td>\n",
       "        <td>Classical</td>\n",
       "        <td>4</td>\n",
       "        <td>0.308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>13</td>\n",
       "        <td>Heavy Metal</td>\n",
       "        <td>3</td>\n",
       "        <td>0.231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>10</td>\n",
       "        <td>Soundtrack</td>\n",
       "        <td>2</td>\n",
       "        <td>0.154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>19</td>\n",
       "        <td>TV Shows</td>\n",
       "        <td>1</td>\n",
       "        <td>0.077</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('USA', 1, 'Rock', 561, 43.154),\n",
       " ('USA', 4, 'Alternative & Punk', 130, 10.0),\n",
       " ('USA', 3, 'Metal', 124, 9.538),\n",
       " ('USA', 14, 'R&B/Soul', 53, 4.077),\n",
       " ('USA', 6, 'Blues', 36, 2.769),\n",
       " ('USA', 23, 'Alternative', 35, 2.692),\n",
       " ('USA', 7, 'Latin', 22, 1.692),\n",
       " ('USA', 9, 'Pop', 22, 1.692),\n",
       " ('USA', 17, 'Hip Hop/Rap', 20, 1.538),\n",
       " ('USA', 2, 'Jazz', 14, 1.077),\n",
       " ('USA', 12, 'Easy Listening', 13, 1.0),\n",
       " ('USA', 8, 'Reggae', 6, 0.462),\n",
       " ('USA', 15, 'Electronica/Dance', 5, 0.385),\n",
       " ('USA', 24, 'Classical', 4, 0.308),\n",
       " ('USA', 13, 'Heavy Metal', 3, 0.231),\n",
       " ('USA', 10, 'Soundtrack', 2, 0.154),\n",
       " ('USA', 19, 'TV Shows', 1, 0.077)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH\n",
    "    usa_tracks AS\n",
    "        (SELECT\n",
    "            *\n",
    "            FROM customer AS c\n",
    "            WHERE country = 'USA'),\n",
    "    tracks_sold_by_genre AS\n",
    "        (SELECT\n",
    "            usa.country AS country,\n",
    "            g.genre_id AS genre_id,\n",
    "            g.name AS genre,\n",
    "            COUNT(il.invoice_line_id) AS tracks_sold\n",
    "        FROM usa_tracks AS usa\n",
    "            LEFT JOIN invoice AS i\n",
    "                ON i.customer_id = usa.customer_id\n",
    "            LEFT JOIN invoice_line AS il\n",
    "                ON il.invoice_id = i.invoice_id\n",
    "            LEFT JOIN track AS t\n",
    "                ON t.track_id = il.track_id\n",
    "            LEFT JOIN genre AS g\n",
    "                ON g.genre_id = t.genre_id\n",
    "        GROUP BY genre)\n",
    "\n",
    "SELECT\n",
    "    tsg.country,\n",
    "    tsg.genre_id,\n",
    "    tsg.genre,\n",
    "    tsg.tracks_sold,\n",
    "    ROUND(\n",
    "            CAST(tsg.tracks_sold AS FLOAT) \n",
    "            / \n",
    "            (SELECT COUNT(*) FROM usa_tracks)\n",
    "                                                , 3)\n",
    "        AS percentage_sold\n",
    "FROM tracks_sold_by_genre AS tsg\n",
    "\n",
    "GROUP BY tsg.genre\n",
    "ORDER BY percentage_sold DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Solution\n",
    "\n",
    "Above, one can see which genres are the most popular in the USA. Rock is the most popular genre by some distance.\n",
    "\n",
    "Based on the popularity of certain genres above, of the four artists listed earlier I should recommend the purchase of albums from:\n",
    "- Red Tone (Punk)\n",
    "- Slim Jim Bites (Blues)\n",
    "- Meteor and the Girls (Pop)\n",
    "\n",
    "The album from Regal (Hip-Hop) would not be purchased from the list of four artists on the basis of genre popularity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyzing Sales Support Agent Performance\n",
    "\n",
    "### The Problem\n",
    "\n",
    "Each customer for the Chinook store gets assigned to a sales support agent within the company when they first make a purchase. I will 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's important to consider whether any extra columns from the employee table will explain any variance I will see or whether the variance might instead be indicative of employee performance.\n",
    "\n",
    "**I will write a query that finds the total dollar amount of sales assigned to each sales support agent within the company.** I'll add any extra attributes for that employee that I find are relevant."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>employee_number</th>\n",
       "        <th>employee_name</th>\n",
       "        <th>total_sales</th>\n",
       "        <th>date_hired</th>\n",
       "        <th>birthday</th>\n",
       "        <th>job_title</th>\n",
       "        <th>supervised_by</th>\n",
       "        <th>postal_code</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Jane Peacock</td>\n",
       "        <td>1731.510000000004</td>\n",
       "        <td>2017-04-01 00:00:00</td>\n",
       "        <td>1973-08-29 00:00:00</td>\n",
       "        <td>Sales Support Agent</td>\n",
       "        <td>2</td>\n",
       "        <td>T2P 5M5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Margaret Park</td>\n",
       "        <td>1584.0000000000032</td>\n",
       "        <td>2017-05-03 00:00:00</td>\n",
       "        <td>1947-09-19 00:00:00</td>\n",
       "        <td>Sales Support Agent</td>\n",
       "        <td>2</td>\n",
       "        <td>T2P 5G3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Steve Johnson</td>\n",
       "        <td>1393.9200000000028</td>\n",
       "        <td>2017-10-17 00:00:00</td>\n",
       "        <td>1965-03-03 00:00:00</td>\n",
       "        <td>Sales Support Agent</td>\n",
       "        <td>2</td>\n",
       "        <td>T3B 1Y7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3, 'Jane Peacock', 1731.510000000004, '2017-04-01 00:00:00', '1973-08-29 00:00:00', 'Sales Support Agent', 2, 'T2P 5M5'),\n",
       " (4, 'Margaret Park', 1584.0000000000032, '2017-05-03 00:00:00', '1947-09-19 00:00:00', 'Sales Support Agent', 2, 'T2P 5G3'),\n",
       " (5, 'Steve Johnson', 1393.9200000000028, '2017-10-17 00:00:00', '1965-03-03 00:00:00', 'Sales Support Agent', 2, 'T3B 1Y7')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    c.support_rep_id AS employee_number,\n",
    "    e.first_name || ' ' || e.last_name AS employee_name,\n",
    "    SUM(i.total) AS total_sales,\n",
    "    e.hire_date AS date_hired,\n",
    "    e.birthdate AS birthday,\n",
    "    e.title AS job_title,\n",
    "    e.reports_to AS supervised_by,\n",
    "    e.postal_code AS postal_code\n",
    "FROM customer AS c\n",
    "    LEFT JOIN employee AS e\n",
    "        ON c.support_rep_id = e.employee_id\n",
    "    LEFT JOIN invoice AS i\n",
    "        ON i.customer_id = c.customer_id\n",
    "GROUP BY employee_number\n",
    "ORDER BY total_sales DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Solution\n",
    "\n",
    "At a glance, the amount of sales per each of the sales support agents seems to be based on the date hired. Other factors do not seem to impact the 'total_sales' column nearly as strongly."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Customer Sales by Country\n",
    "\n",
    "### The Problem\n",
    "\n",
    "My next task is to analyze the sales data for customers from each different country. I 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.\n",
    "\n",
    "**In particular, I have been directed to calculate data, for each country, on the:**\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": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>total_customers</th>\n",
       "        <th>total_sales</th>\n",
       "        <th>average_value_per_customer</th>\n",
       "        <th>average_order_value</th>\n",
       "        <th>sort</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USA</td>\n",
       "        <td>13</td>\n",
       "        <td>1040.490000000008</td>\n",
       "        <td>80.03769230769292</td>\n",
       "        <td>7.942671755725252</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>8</td>\n",
       "        <td>535.5900000000034</td>\n",
       "        <td>66.94875000000043</td>\n",
       "        <td>7.047236842105309</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>5</td>\n",
       "        <td>427.68000000000245</td>\n",
       "        <td>85.53600000000048</td>\n",
       "        <td>7.011147540983647</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>5</td>\n",
       "        <td>389.0700000000021</td>\n",
       "        <td>77.81400000000042</td>\n",
       "        <td>7.781400000000042</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>4</td>\n",
       "        <td>334.6200000000016</td>\n",
       "        <td>83.6550000000004</td>\n",
       "        <td>8.161463414634186</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>2</td>\n",
       "        <td>273.24000000000103</td>\n",
       "        <td>136.62000000000052</td>\n",
       "        <td>9.108000000000034</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>3</td>\n",
       "        <td>245.52000000000078</td>\n",
       "        <td>81.84000000000026</td>\n",
       "        <td>8.768571428571457</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>2</td>\n",
       "        <td>185.13000000000022</td>\n",
       "        <td>92.56500000000011</td>\n",
       "        <td>6.3837931034482835</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>2</td>\n",
       "        <td>183.1500000000002</td>\n",
       "        <td>91.5750000000001</td>\n",
       "        <td>8.72142857142858</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Other</td>\n",
       "        <td>15</td>\n",
       "        <td>1094.9400000000085</td>\n",
       "        <td>72.99600000000056</td>\n",
       "        <td>7.448571428571486</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('USA', 13, 1040.490000000008, 80.03769230769292, 7.942671755725252, 1),\n",
       " ('Canada', 8, 535.5900000000034, 66.94875000000043, 7.047236842105309, 1),\n",
       " ('Brazil', 5, 427.68000000000245, 85.53600000000048, 7.011147540983647, 1),\n",
       " ('France', 5, 389.0700000000021, 77.81400000000042, 7.781400000000042, 1),\n",
       " ('Germany', 4, 334.6200000000016, 83.6550000000004, 8.161463414634186, 1),\n",
       " ('Czech Republic', 2, 273.24000000000103, 136.62000000000052, 9.108000000000034, 1),\n",
       " ('United Kingdom', 3, 245.52000000000078, 81.84000000000026, 8.768571428571457, 1),\n",
       " ('Portugal', 2, 185.13000000000022, 92.56500000000011, 6.3837931034482835, 1),\n",
       " ('India', 2, 183.1500000000002, 91.5750000000001, 8.72142857142858, 1),\n",
       " ('Other', 15, 1094.9400000000085, 72.99600000000056, 7.448571428571486, 0)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH country_vs_other AS\n",
    "    (SELECT\n",
    "        CASE\n",
    "            WHEN (SELECT count(*) FROM customer WHERE country = c.country) = 1 \n",
    "                THEN 'Other'\n",
    "            ELSE c.country\n",
    "            END AS country,\n",
    "        c.customer_id,\n",
    "        il.*\n",
    "    FROM invoice_line AS il\n",
    "        INNER JOIN invoice i ON i.invoice_id = il.invoice_id\n",
    "        INNER JOIN customer c ON c.customer_id = i.customer_id)\n",
    "    \n",
    "SELECT\n",
    "    country,\n",
    "    COUNT(DISTINCT customer_id) AS total_customers,\n",
    "    SUM(unit_price) AS total_sales,\n",
    "    SUM(unit_price) / COUNT(DISTINCT customer_id) AS average_value_per_customer,\n",
    "    SUM(unit_price) / COUNT(DISTINCT invoice_id) AS average_order_value,\n",
    "    sort\n",
    "FROM\n",
    "    (\n",
    "    SELECT\n",
    "        cvo.*,\n",
    "        CASE\n",
    "            WHEN cvo.country = 'Other'\n",
    "                THEN 0\n",
    "                ELSE 1\n",
    "            END AS sort\n",
    "    FROM\n",
    "    country_vs_other AS cvo        \n",
    "    )\n",
    "GROUP BY country\n",
    "ORDER BY sort DESC, total_sales DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Solution\n",
    "\n",
    "Above I've generated the number of total customers by country, their total sales, the average value per customer by country, and the average order value of customers by country."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Informing the Music Purchasing Strategy\n",
    "\n",
    "### The Problem\n",
    "\n",
    "The Chinook store is setup in a way that allows customer to make purchases in one of the two ways:\n",
    "\n",
    "- purchase a whole album\n",
    "- 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 is considering changing the 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",
    "I want to find out what percentage of purchases are individual track purchases versus whole album purchases so that management can use this data to understand the effect this decision might have on overall revenue.\n",
    "\n",
    "**I'll 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**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'm going to have to identify whether each invoice has all the tracks from an album. I 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",
    "I can 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 I pick, since if it's an album purchase, that album will be the same for all tracks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_purchase</th>\n",
       "        <th>number_of_invoices</th>\n",
       "        <th>percent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>no</td>\n",
       "        <td>500</td>\n",
       "        <td>0.8143322475570033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>yes</td>\n",
       "        <td>114</td>\n",
       "        <td>0.18566775244299674</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('no', 500, 0.8143322475570033), ('yes', 114, 0.18566775244299674)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH invoice_first_track AS\n",
    "    (\n",
    "     SELECT\n",
    "         il.invoice_id invoice_id,\n",
    "         MIN(il.track_id) first_track_id\n",
    "     FROM invoice_line il\n",
    "     GROUP BY 1\n",
    "    )\n",
    "\n",
    "SELECT\n",
    "    album_purchase,\n",
    "    COUNT(invoice_id) number_of_invoices,\n",
    "    CAST(count(invoice_id) AS FLOAT) / (\n",
    "                                         SELECT COUNT(*) FROM invoice\n",
    "                                      ) percent\n",
    "FROM\n",
    "    (\n",
    "    SELECT\n",
    "        ifs.*,\n",
    "        CASE\n",
    "            WHEN\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 = ifs.first_track_id\n",
    "                                     ) \n",
    "\n",
    "                  EXCEPT \n",
    "\n",
    "                  SELECT il2.track_id FROM invoice_line il2\n",
    "                  WHERE il2.invoice_id = ifs.invoice_id\n",
    "                 ) IS NULL\n",
    "             AND\n",
    "                 (\n",
    "                  SELECT il2.track_id FROM invoice_line il2\n",
    "                  WHERE il2.invoice_id = ifs.invoice_id\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 = ifs.first_track_id\n",
    "                                     ) \n",
    "                 ) IS NULL\n",
    "             THEN \"yes\"\n",
    "             ELSE \"no\"\n",
    "         END AS \"album_purchase\"\n",
    "     FROM invoice_first_track ifs\n",
    "    )\n",
    "GROUP BY album_purchase;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Solution\n",
    "\n",
    "Only 18.6% of purchases are entire album purchases. While more information would be helpful to make a recommendation on purchasing strategy, changing the purchase strategy *may* save money for the store.\n",
    "\n",
    "However, changing the purchasing strategy is a major change and one that may negatively impact the purchasing experience of some customers. More data is required, perhaps a customer survey, before a recommendation can be made. "
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}