Optimizing Online Sports Retail Revenue.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "8"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 1. Counting missing values\n",
    "<p>Sports clothing and athleisure attire is a huge industry, worth approximately <a href=\"https://www.statista.com/statistics/254489/total-revenue-of-the-global-sports-apparel-market/\">$193 billion in 2021</a> with a strong growth forecast over the next decade! </p>\n",
    "<p>In this notebook, we play the role of a product analyst for an online sports clothing company. The company is specifically interested in how it can improve revenue. We will dive into product data such as pricing, reviews, descriptions, and ratings, as well as revenue and website traffic, to produce recommendations for its marketing and sales teams.  </p>\n",
    "<p>The database provided to us, <code>sports</code>, contains five tables, with <code>product_id</code> being the primary key for all of them: </p>\n",
    "<h3 id=\"info\"><code>info</code></h3>\n",
    "<table>\n",
    "<thead>\n",
    "<tr>\n",
    "<th>column</th>\n",
    "<th>data type</th>\n",
    "<th>description</th>\n",
    "</tr>\n",
    "</thead>\n",
    "<tbody>\n",
    "<tr>\n",
    "<td><code>product_name</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Name of the product</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>product_id</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Unique ID for product</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>description</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Description of the product</td>\n",
    "</tr>\n",
    "</tbody>\n",
    "</table>\n",
    "<h3 id=\"finance\"><code>finance</code></h3>\n",
    "<table>\n",
    "<thead>\n",
    "<tr>\n",
    "<th>column</th>\n",
    "<th>data type</th>\n",
    "<th>description</th>\n",
    "</tr>\n",
    "</thead>\n",
    "<tbody>\n",
    "<tr>\n",
    "<td><code>product_id</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Unique ID for product</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>listing_price</code></td>\n",
    "<td><code>float</code></td>\n",
    "<td>Listing price for product</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>sale_price</code></td>\n",
    "<td><code>float</code></td>\n",
    "<td>Price of the product when on sale</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>discount</code></td>\n",
    "<td><code>float</code></td>\n",
    "<td>Discount, as a decimal, applied to the sale price</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>revenue</code></td>\n",
    "<td><code>float</code></td>\n",
    "<td>Amount of revenue generated by each product, in US dollars</td>\n",
    "</tr>\n",
    "</tbody>\n",
    "</table>\n",
    "<h3 id=\"reviews\"><code>reviews</code></h3>\n",
    "<table>\n",
    "<thead>\n",
    "<tr>\n",
    "<th>column</th>\n",
    "<th>data type</th>\n",
    "<th>description</th>\n",
    "</tr>\n",
    "</thead>\n",
    "<tbody>\n",
    "<tr>\n",
    "<td><code>product_name</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Name of the product</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>product_id</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Unique ID for product</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>rating</code></td>\n",
    "<td><code>float</code></td>\n",
    "<td>Product rating, scored from <code>1.0</code> to <code>5.0</code></td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>reviews</code></td>\n",
    "<td><code>float</code></td>\n",
    "<td>Number of reviews for the product</td>\n",
    "</tr>\n",
    "</tbody>\n",
    "</table>\n",
    "<h3 id=\"traffic\"><code>traffic</code></h3>\n",
    "<table>\n",
    "<thead>\n",
    "<tr>\n",
    "<th>column</th>\n",
    "<th>data type</th>\n",
    "<th>description</th>\n",
    "</tr>\n",
    "</thead>\n",
    "<tbody>\n",
    "<tr>\n",
    "<td><code>product_id</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Unique ID for product</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>last_visited</code></td>\n",
    "<td><code>timestamp</code></td>\n",
    "<td>Date and time the product was last viewed on the website</td>\n",
    "</tr>\n",
    "</tbody>\n",
    "</table>\n",
    "<h3 id=\"brands\"><code>brands</code></h3>\n",
    "<table>\n",
    "<thead>\n",
    "<tr>\n",
    "<th>column</th>\n",
    "<th>data type</th>\n",
    "<th>description</th>\n",
    "</tr>\n",
    "</thead>\n",
    "<tbody>\n",
    "<tr>\n",
    "<td><code>product_id</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Unique ID for product</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>brand</code></td>\n",
    "<td><code>varchar</code></td>\n",
    "<td>Brand of the product</td>\n",
    "</tr>\n",
    "</tbody>\n",
    "</table>\n",
    "<p>We will be dealing with missing data as well as numeric, string, and timestamp data types to draw insights about the products in the online store. Let's start by finding out how complete the data is.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "dc": {
     "key": "8"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>total_rows</th>\n",
       "        <th>count_description</th>\n",
       "        <th>count_listing_price</th>\n",
       "        <th>count_last_visited</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3179</td>\n",
       "        <td>3117</td>\n",
       "        <td>3120</td>\n",
       "        <td>2928</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3179, 3117, 3120, 2928)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "postgresql:///sports\n",
    "\n",
    "-- Count all columns as total_rows\n",
    "-- Count the number of non-missing entries for description, listing_price, and last_visited\n",
    "-- Join info, finance, and traffic\n",
    "SELECT COUNT(*) as total_rows,\n",
    "    COUNT(description) as count_description,\n",
    "    COUNT(listing_price) as count_listing_price,\n",
    "    COUNT(last_visited) as count_last_visited\n",
    "FROM info\n",
    "JOIN finance ON info.product_id = finance.product_id\n",
    "JOIN traffic ON info.product_id = traffic.product_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "dc": {
     "key": "8"
    },
    "hide": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert \"total_rows\" in set(last_output_df.columns), \\\n",
    "    \"\"\"Did you alias the count of all products as \"total_rows\"?\"\"\"\n",
    "    assert set(last_output_df.columns) == set(['total_rows', 'count_description', 'count_listing_price',\n",
    "           'count_last_visited']), \\\n",
    "    \"\"\"Did you select four columns and use the aliases in the instructions?\"\"\"\n",
    "\n",
    "def test_shape():\n",
    "    assert last_output_df.shape[0] == 1, \\\n",
    "    \"\"\"Did you return a single row containing the count of values for each column?\"\"\"\n",
    "    assert last_output_df.shape[1] == 4, \\\n",
    "    \"\"\"Did you select four columns?\"\"\"\n",
    "\n",
    "def test_values():\n",
    "    assert last_output_df.values.tolist() == [[3179, 3117, 3120, 2928]], \\\n",
    "    \"\"\"Did you correctly calculate the values for each column? Expected different results.\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "18"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 2. Nike vs Adidas pricing\n",
    "<p>We can see the database contains 3,179 products in total. Of the columns we previewed, only one &mdash; <code>last_visited</code> &mdash; is missing more than five percent of its values. Now let's turn our attention to pricing. </p>\n",
    "<p>How do the price points of Nike and Adidas products differ? Answering this question can help us build a picture of the company's stock range and customer market. We will run a query to produce a distribution of the <code>listing_price</code> and the count for each price, grouped by <code>brand</code>. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "dc": {
     "key": "18"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql:///sports\n",
      "77 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>brand</th>\n",
       "        <th>listing_price</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>300</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>280</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>240</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>230</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>220</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>200</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>200</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>190</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>190</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>180</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>180</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>170</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>170</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>160</td>\n",
       "        <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>160</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>150</td>\n",
       "        <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>150</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>140</td>\n",
       "        <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>140</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>130</td>\n",
       "        <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>130</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>120</td>\n",
       "        <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>120</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>110</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>110</td>\n",
       "        <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>100</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>100</td>\n",
       "        <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>96</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>95</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>90</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>90</td>\n",
       "        <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>86</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>85</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>85</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>80</td>\n",
       "        <td>322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>80</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>79</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>76</td>\n",
       "        <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>75</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>75</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>70</td>\n",
       "        <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>70</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>66</td>\n",
       "        <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>65</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>63</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>60</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>60</td>\n",
       "        <td>211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>56</td>\n",
       "        <td>174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>55</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>53</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>50</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>50</td>\n",
       "        <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>48</td>\n",
       "        <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>48</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>46</td>\n",
       "        <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>45</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>45</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>43</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>40</td>\n",
       "        <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>40</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>38</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>36</td>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>33</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>30</td>\n",
       "        <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>30</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>28</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>27</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>25</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>23</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>20</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>18</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>15</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>13</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>12</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>10</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>9</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Adidas', 300, 2),\n",
       " ('Adidas', 280, 4),\n",
       " ('Adidas', 240, 5),\n",
       " ('Adidas', 230, 8),\n",
       " ('Adidas', 220, 11),\n",
       " ('Nike', 200, 1),\n",
       " ('Adidas', 200, 8),\n",
       " ('Nike', 190, 2),\n",
       " ('Adidas', 190, 7),\n",
       " ('Nike', 180, 4),\n",
       " ('Adidas', 180, 34),\n",
       " ('Nike', 170, 14),\n",
       " ('Adidas', 170, 27),\n",
       " ('Nike', 160, 31),\n",
       " ('Adidas', 160, 28),\n",
       " ('Adidas', 150, 41),\n",
       " ('Nike', 150, 6),\n",
       " ('Adidas', 140, 36),\n",
       " ('Nike', 140, 12),\n",
       " ('Adidas', 130, 96),\n",
       " ('Nike', 130, 12),\n",
       " ('Adidas', 120, 115),\n",
       " ('Nike', 120, 16),\n",
       " ('Nike', 110, 17),\n",
       " ('Adidas', 110, 91),\n",
       " ('Nike', 100, 14),\n",
       " ('Adidas', 100, 72),\n",
       " ('Adidas', 96, 2),\n",
       " ('Nike', 95, 1),\n",
       " ('Nike', 90, 13),\n",
       " ('Adidas', 90, 89),\n",
       " ('Adidas', 86, 7),\n",
       " ('Nike', 85, 5),\n",
       " ('Adidas', 85, 1),\n",
       " ('Adidas', 80, 322),\n",
       " ('Nike', 80, 16),\n",
       " ('Nike', 79, 1),\n",
       " ('Adidas', 76, 149),\n",
       " ('Adidas', 75, 1),\n",
       " ('Nike', 75, 7),\n",
       " ('Adidas', 70, 87),\n",
       " ('Nike', 70, 4),\n",
       " ('Adidas', 66, 102),\n",
       " ('Nike', 65, 1),\n",
       " ('Adidas', 63, 1),\n",
       " ('Nike', 60, 2),\n",
       " ('Adidas', 60, 211),\n",
       " ('Adidas', 56, 174),\n",
       " ('Adidas', 55, 2),\n",
       " ('Adidas', 53, 43),\n",
       " ('Nike', 50, 5),\n",
       " ('Adidas', 50, 183),\n",
       " ('Adidas', 48, 42),\n",
       " ('Nike', 48, 1),\n",
       " ('Adidas', 46, 163),\n",
       " ('Nike', 45, 3),\n",
       " ('Adidas', 45, 1),\n",
       " ('Adidas', 43, 51),\n",
       " ('Adidas', 40, 81),\n",
       " ('Nike', 40, 1),\n",
       " ('Adidas', 38, 24),\n",
       " ('Adidas', 36, 25),\n",
       " ('Adidas', 33, 24),\n",
       " ('Adidas', 30, 37),\n",
       " ('Nike', 30, 2),\n",
       " ('Adidas', 28, 38),\n",
       " ('Adidas', 27, 18),\n",
       " ('Adidas', 25, 28),\n",
       " ('Adidas', 23, 1),\n",
       " ('Adidas', 20, 8),\n",
       " ('Adidas', 18, 4),\n",
       " ('Adidas', 16, 4),\n",
       " ('Adidas', 15, 27),\n",
       " ('Adidas', 13, 27),\n",
       " ('Adidas', 12, 1),\n",
       " ('Adidas', 10, 11),\n",
       " ('Adidas', 9, 1)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Select the brand, listing_price as an integer, and a count of all products in finance \n",
    "-- Join brands to finance on product_id\n",
    "-- Aggregate results by brand and listing_price, and sort the results by listing_price in descending order\n",
    "-- Filter for products with a listing_price more than zero\n",
    "SELECT brand, CAST(listing_price AS integer), COUNT(finance) \n",
    "FROM brands\n",
    "JOIN finance ON brands.product_id = finance.product_id\n",
    "JOIN traffic ON brands.product_id = traffic.product_id\n",
    "WHERE listing_price > 0\n",
    "GROUP BY brand, listing_price\n",
    "ORDER BY listing_price DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "dc": {
     "key": "18"
    },
    "hide": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert set(last_output_df.columns) == set(['brand', 'count', 'listing_price']), \\\n",
    "    \"\"\"Did you select the correct columns and alias the first as \"total_rows\"?\"\"\"\n",
    "\n",
    "def test_shape():\n",
    "    assert last_output_df.shape[0] == 77, \\\n",
    "    \"\"\"Did you correctly aggregate by brand? Expected the output to contain 77 products.\"\"\"\n",
    "    assert last_output_df.shape[1] == 3, \\\n",
    "    \"\"\"The output should contain three columns: \"brand\", \"listing_price\", and \"count\"?\"\"\"\n",
    "\n",
    "def test_values():\n",
    "    assert last_output_df.iloc[0].values.tolist() == ['Adidas', 300, 2], \\\n",
    "    \"\"\"Did you sort the results by \"listing_price\" in descending order?\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "27"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 3. Labeling price ranges\n",
    "<p>It turns out there are 77 unique prices for the products in our database, which makes the output of our last query quite difficult to analyze. </p>\n",
    "<p>Let's build on our previous query by assigning labels to different price ranges, grouping by <code>brand</code> and <code>label</code>. We will also include the total <code>revenue</code> for each price range and <code>brand</code>. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "dc": {
     "key": "27"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql:///sports\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>brand</th>\n",
       "        <th>count</th>\n",
       "        <th>total_revenue</th>\n",
       "        <th>price_category</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>849</td>\n",
       "        <td>4626980.069999999</td>\n",
       "        <td>Expensive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>1060</td>\n",
       "        <td>3233661.060000001</td>\n",
       "        <td>Average</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>307</td>\n",
       "        <td>3014316.8299999987</td>\n",
       "        <td>Elite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>359</td>\n",
       "        <td>651661.1200000002</td>\n",
       "        <td>Budget</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>357</td>\n",
       "        <td>595341.0199999992</td>\n",
       "        <td>Budget</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>82</td>\n",
       "        <td>128475.59000000003</td>\n",
       "        <td>Elite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>90</td>\n",
       "        <td>71843.15000000004</td>\n",
       "        <td>Expensive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>16</td>\n",
       "        <td>6623.5</td>\n",
       "        <td>Average</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Adidas', 849, 4626980.069999999, 'Expensive'),\n",
       " ('Adidas', 1060, 3233661.060000001, 'Average'),\n",
       " ('Adidas', 307, 3014316.8299999987, 'Elite'),\n",
       " ('Adidas', 359, 651661.1200000002, 'Budget'),\n",
       " ('Nike', 357, 595341.0199999992, 'Budget'),\n",
       " ('Nike', 82, 128475.59000000003, 'Elite'),\n",
       " ('Nike', 90, 71843.15000000004, 'Expensive'),\n",
       " ('Nike', 16, 6623.5, 'Average')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Select the brand, a count of all products in the finance table, and total revenue\n",
    "-- Create four labels for products based on their price range, aliasing as price_category\n",
    "-- Join brands to finance on product_id\n",
    "-- Group results by brand and price_category, sort by total_revenue and filter out products missing a value for brand\n",
    "SELECT b.brand, COUNT(f.*), SUM(f.revenue) as total_revenue,\n",
    "CASE WHEN f.listing_price < 42 THEN 'Budget'\n",
    "     WHEN f.listing_price >= 42 AND f.listing_price < 74 THEN 'Average'\n",
    "     WHEN f.listing_price >= 74 AND f.listing_price < 129 THEN 'Expensive'\n",
    "     ELSE 'Elite' END AS price_category\n",
    "FROM finance as f \n",
    "INNER JOIN brands as b\n",
    "ON b.product_id = f.product_id\n",
    "GROUP BY b.brand, price_category\n",
    "HAVING b.brand IS NOT NULL\n",
    "ORDER BY total_revenue DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "dc": {
     "key": "27"
    },
    "hide": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert set(last_output_df.columns) == set(['brand', 'price_category', 'count', 'total_revenue']), \\\n",
    "    \"\"\"Did you select the correct columns? Expected \"brand\", \"listing_price\", \"count\", and \"total_revenue\".\"\"\"\n",
    "    \n",
    "def test_shape():\n",
    "    assert last_output_df.shape[0] == 8, \\\n",
    "    \"Did you group by brand and labels? Expected there to be eight rows.\"\n",
    "    assert last_output_df.shape[1] == 4, \\\n",
    "    \"Did you select four columns?\"\n",
    "    \n",
    "def test_values():\n",
    "    assert last_output_df[:4].values.tolist() == [['Adidas', 849, 4626980.069999999, 'Expensive'],\n",
    "     ['Adidas', 1060, 3233661.060000001, 'Average'],\n",
    "     ['Adidas', 307, 3014316.8299999987, 'Elite'],\n",
    "     ['Adidas', 359, 651661.1200000002, 'Budget']], \\\n",
    "    \"Did you correctly calculate values for Adidas products? Expected something different.\"\n",
    "    assert last_output_df[4:].values.tolist() == [['Nike', 357, 595341.0199999992, 'Budget'],\n",
    "     ['Nike', 82, 128475.59000000003, 'Elite'],\n",
    "     ['Nike', 90, 71843.15000000004, 'Expensive'],\n",
    "     ['Nike', 16, 6623.5, 'Average']], \\\n",
    "    \"Did you correctly calculate values for Nike products? Expected something different.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "38"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 4. Average discount by brand\n",
    "<p>Interestingly, grouping products by brand and price range allows us to see that Adidas items generate more total revenue regardless of price category! Specifically, <code>\"Elite\"</code> Adidas products priced \\$129 or more typically generate the highest revenue, so the company can potentially increase revenue by shifting their stock to have a larger proportion of these products!</p>\n",
    "<p>Note we have been looking at <code>listing_price</code> so far. The <code>listing_price</code> may not be the price that the product is ultimately sold for. To understand <code>revenue</code> better, let's take a look at the <code>discount</code>, which is the percent reduction in the <code>listing_price</code> when the product is actually sold. We would like to know whether there is a difference in the amount of <code>discount</code> offered between brands, as this could be influencing <code>revenue</code>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "dc": {
     "key": "38"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql:///sports\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>brand</th>\n",
       "        <th>average_discount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>33.452427184465606</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Nike', 0.0), ('Adidas', 33.452427184465606)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Select brand and average_discount as a percentage\n",
    "-- Join brands to finance on product_id\n",
    "-- Aggregate by brand\n",
    "-- Filter for products without missing values for brand\n",
    "SELECT b.brand,  AVG(f.discount)*100 as average_discount\n",
    "FROM brands as b \n",
    "JOIN finance as f\n",
    "ON b.product_id = f.product_id\n",
    "GROUP BY b.brand\n",
    "HAVING b.brand IS NOT NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "dc": {
     "key": "38"
    },
    "hide": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert set(last_output_df.columns) == set(['brand', 'average_discount']), \\\n",
    "    \"\"\"Did you select the correct columns? Expected \"brand\" and \"average_discount\".\"\"\"\n",
    "    \n",
    "def test_shape():\n",
    "    assert last_output_df.shape[0] == 2, \\\n",
    "    \"Did you group by brand? Expected two rows, one per brand.\"\n",
    "    assert last_output_df.shape[1] == 2, \\\n",
    "    \"Did you select two columns?\"\n",
    "    \n",
    "def test_values():\n",
    "    assert last_output_df.iloc[:, 1].values.tolist() == [0.0, 33.452427184465606], \\\n",
    "    \"Did you correctly calculate the average discount for the two brands?\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "48"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 5. Correlation between revenue and reviews\n",
    "<p>Strangely, no <code>discount</code> is offered on Nike products! In comparison, not only do Adidas products generate the most revenue, but these products are also heavily discounted! </p>\n",
    "<p>To improve revenue further, the company could try to reduce the amount of discount offered on Adidas products, and monitor sales volume to see if it remains stable. Alternatively, it could try offering a small discount on Nike products. This would reduce average revenue for these products, but may increase revenue overall if there is an increase in the volume of Nike products sold. </p>\n",
    "<p>Now explore whether relationships exist between the columns in our database. We will check the strength and direction of a correlation between <code>revenue</code> and <code>reviews</code>. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "dc": {
     "key": "48"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql:///sports\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>review_revenue_corr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0.6518512283481301</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0.6518512283481301,)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Calculate the correlation between reviews and revenue as review_revenue_corr\n",
    "-- Join the reviews and finance tables on product_id\n",
    "SELECT corr(f.revenue, r.reviews) as review_revenue_corr\n",
    "FROM finance as f\n",
    "INNER JOIN reviews as r\n",
    "ON f.product_id = r.product_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "dc": {
     "key": "48"
    },
    "hide": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert set(last_output_df.columns) == set(['review_revenue_corr']), \\\n",
    "    \"\"\"Did you calculate the correlation between reviews and revenue, aliasing as \"review_revenue_corr\"?\"\"\"\n",
    "    \n",
    "def test_shape():\n",
    "    assert last_output_df.shape == (1, 1), \\\n",
    "    \"Did you calculate the correlation between reviews and revenue?\"\n",
    "    \n",
    "def test_values():\n",
    "    assert last_output_df.values.tolist() == [[0.6518512283481301]], \\\n",
    "    \"Did you correctly calculate how reviews correlates with revenue?\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "58"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 6. Ratings and reviews by product description length\n",
    "<p>Interestingly, there is a strong positive correlation between <code>revenue</code> and <code>reviews</code>. This means, potentially, if we can get more reviews on the company's website, it may increase sales of those items with a larger number of reviews. </p>\n",
    "<p>Perhaps the length of a product's <code>description</code> might influence a product's <code>rating</code> and <code>reviews</code> &mdash; if so, the company can produce content guidelines for listing products on their website and test if this influences <code>revenue</code>. Let's check this out!</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "dc": {
     "key": "58"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql:///sports\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>description_length</th>\n",
       "        <th>average_rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>3.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>200</td>\n",
       "        <td>3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>300</td>\n",
       "        <td>3.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>400</td>\n",
       "        <td>3.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>500</td>\n",
       "        <td>3.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>600</td>\n",
       "        <td>3.65</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('0'), Decimal('1.87')),\n",
       " (Decimal('100'), Decimal('3.21')),\n",
       " (Decimal('200'), Decimal('3.27')),\n",
       " (Decimal('300'), Decimal('3.29')),\n",
       " (Decimal('400'), Decimal('3.32')),\n",
       " (Decimal('500'), Decimal('3.12')),\n",
       " (Decimal('600'), Decimal('3.65'))]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Calculate description_length\n",
    "-- Convert rating to an integer and calculate average_rating\n",
    "-- Join info to reviews on product_id and group the results by description_length\n",
    "-- Filter for products without missing values for description, and sort results by description_length\n",
    "SELECT TRUNC(LENGTH(i.description), -2) as description_length,\n",
    "    ROUND(AVG(r.rating::numeric), 2) as average_rating\n",
    "FROM info AS i\n",
    "JOIN reviews AS r ON i.product_id = r.product_id\n",
    "WHERE i.description IS NOT NULL\n",
    "GROUP BY description_length\n",
    "ORDER BY description_length;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "dc": {
     "key": "58"
    },
    "hide": true,
    "scrolled": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert set(last_output_df.columns) == set(['description_length', 'average_rating']), \\\n",
    "    \"\"\"Did you select the correct columns use the aliases \"description_length\" and \"average_rating\"?\"\"\"\n",
    "    \n",
    "def test_shape():\n",
    "    assert last_output_df.shape[0] == 7, \\\n",
    "    \"\"\"Did you create bins of 100 characters for \"description_length\"? Expected the output to contain seven rows.\"\"\"\n",
    "    assert last_output_df.shape[1] == 2, \\\n",
    "    \"Expected the output to contain two columns.\"\n",
    "    \n",
    "def test_values():\n",
    "    last_output_df = last_output.DataFrame().values.astype(\"float\")\n",
    "    assert last_output_df[0].tolist() == [0.0, 1.87], \\\n",
    "    \"\"\"Did you sort the results by \"description_length\" in ascending order?\"\"\"\n",
    "    assert last_output_df[-1].tolist() == [600.0, 3.65], \\\n",
    "    \"Did you correctly calculate the results? Expected a different average rating for the largest description length bin.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "68"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 7. Reviews by month and brand\n",
    "<p>Unfortunately, there doesn't appear to be a clear pattern between the length of a product's <code>description</code> and its <code>rating</code>.</p>\n",
    "<p>As we know a correlation exists between <code>reviews</code> and <code>revenue</code>, one approach the company could take is to run experiments with different sales processes encouraging more reviews from customers about their purchases, such as by offering a small discount on future purchases. </p>\n",
    "<p>Let's take a look at the volume of <code>reviews</code> by month to see if there are any trends or gaps we can look to exploit.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "dc": {
     "key": "68"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql:///sports\n",
      "24 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>brand</th>\n",
       "        <th>month</th>\n",
       "        <th>num_reviews</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>1.0</td>\n",
       "        <td>253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>2.0</td>\n",
       "        <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>3.0</td>\n",
       "        <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>4.0</td>\n",
       "        <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>5.0</td>\n",
       "        <td>172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>6.0</td>\n",
       "        <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>7.0</td>\n",
       "        <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>8.0</td>\n",
       "        <td>189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>9.0</td>\n",
       "        <td>181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>10.0</td>\n",
       "        <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>11.0</td>\n",
       "        <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adidas</td>\n",
       "        <td>12.0</td>\n",
       "        <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>1.0</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>2.0</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>3.0</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>4.0</td>\n",
       "        <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>5.0</td>\n",
       "        <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>6.0</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>7.0</td>\n",
       "        <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>8.0</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>9.0</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>10.0</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>11.0</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nike</td>\n",
       "        <td>12.0</td>\n",
       "        <td>35</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Adidas', 1.0, 253),\n",
       " ('Adidas', 2.0, 272),\n",
       " ('Adidas', 3.0, 269),\n",
       " ('Adidas', 4.0, 180),\n",
       " ('Adidas', 5.0, 172),\n",
       " ('Adidas', 6.0, 159),\n",
       " ('Adidas', 7.0, 170),\n",
       " ('Adidas', 8.0, 189),\n",
       " ('Adidas', 9.0, 181),\n",
       " ('Adidas', 10.0, 192),\n",
       " ('Adidas', 11.0, 150),\n",
       " ('Adidas', 12.0, 190),\n",
       " ('Nike', 1.0, 52),\n",
       " ('Nike', 2.0, 52),\n",
       " ('Nike', 3.0, 55),\n",
       " ('Nike', 4.0, 42),\n",
       " ('Nike', 5.0, 41),\n",
       " ('Nike', 6.0, 43),\n",
       " ('Nike', 7.0, 37),\n",
       " ('Nike', 8.0, 29),\n",
       " ('Nike', 9.0, 28),\n",
       " ('Nike', 10.0, 47),\n",
       " ('Nike', 11.0, 38),\n",
       " ('Nike', 12.0, 35)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Select brand, month from last_visited, and a count of all products in reviews aliased as num_reviews\n",
    "-- Join traffic with reviews and brands on product_id\n",
    "-- Group by brand and month, filtering out missing values for brand and month\n",
    "-- Order the results by brand and month\n",
    "SELECT b.brand, DATE_PART('month', t.last_visited) AS month,\n",
    "    COUNT(r.reviews) AS num_reviews\n",
    "FROM traffic as t\n",
    "INNER JOIN brands as b ON t.product_id = b.product_id\n",
    "INNER JOIN reviews as r ON t.product_id = r.product_id\n",
    "GROUP BY b.brand, DATE_PART('month', t.last_visited)\n",
    "HAVING b.brand IS NOT NULL \n",
    "AND DATE_PART('month', t.last_visited) IS NOT NULL\n",
    "ORDER BY b.brand, month;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "dc": {
     "key": "68"
    },
    "hide": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert set(last_output_df.columns) == set(['brand', 'month', 'num_reviews']), \\\n",
    "    \"\"\"Did you select the correct columns? Expected \"brand\", \"month\", and \"num_reviews\".\"\"\"\n",
    "    \n",
    "def test_shape():\n",
    "    assert last_output_df.shape[0] == 24, \\\n",
    "    \"Did you group by brand and month?\"\n",
    "    assert last_output_df.shape[1] == 3, \\\n",
    "    \"Did you select three columns?\"\n",
    "    \n",
    "def test_values():\n",
    "    assert last_output_df.iloc[0].values.tolist() == ['Adidas', 1.0, 253], \\\n",
    "    \"Expected the first row to contain the number of reviews for Adidas products in January.\"\n",
    "    assert last_output_df.iloc[-1].values.tolist() == ['Nike', 12.0, 35.0], \\\n",
    "    \"Expected the last row to contain the number of reviews for Nike products in December.\"\n",
    "    assert max(last_output_df[\"num_reviews\"]) == 272, \\\n",
    "    \"Did you correctly calculate the number of reviews? Expected the largest number of reviews to be 272 for Adidas products in February.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "76"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 8. Footwear product performance\n",
    "<p>Looks like product reviews are highest in the first quarter of the calendar year, so there is scope to run experiments aiming to increase the volume of reviews in the other nine months!</p>\n",
    "<p>So far, we have been primarily analyzing Adidas vs Nike products. Now, let's switch our attention to the type of products being sold. As there are no labels for product type, we will create a Common Table Expression (CTE) that filters <code>description</code> for keywords, then use the results to find out how much of the company's stock consists of footwear products and the median <code>revenue</code> generated by these items.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "dc": {
     "key": "76"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql:///sports\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>num_footwear_products</th>\n",
       "        <th>median_footwear_revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2700</td>\n",
       "        <td>3118.36</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2700, 3118.36)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Create the footwear CTE, containing description and revenue\n",
    "-- Filter footwear for products with a description containing %shoe%, %trainer, or %foot%\n",
    "-- Also filter for products that are not missing values for description\n",
    "-- Calculate the number of products and median revenue for footwear products\n",
    "WITH footwear AS (\n",
    "    SELECT i.description, f.revenue\n",
    "    FROM info i JOIN finance f ON i.product_id = f.product_id\n",
    "    WHERE i.description ILIKE '%shoe%' OR \n",
    "    i.description ILIKE '%trainer%' OR \n",
    "    i.description ILIKE '%foot%' AND\n",
    "    i.description IS NOT NULL\n",
    ")\n",
    "\n",
    "SELECT COUNT(*) AS num_footwear_products,\n",
    "    percentile_disc(0.5) WITHIN GROUP (ORDER BY revenue) AS median_footwear_revenue\n",
    "FROM footwear;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "dc": {
     "key": "76"
    },
    "hide": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert set(last_output_df.columns) == set(['num_footwear_products', 'median_footwear_revenue']), \\\n",
    "    \"Did you select the correct columns and use the aliases specified in the instructions?\"\n",
    "    \n",
    "def test_shape():\n",
    "    assert last_output_df.shape[0] == 1, \\\n",
    "    \"Expected the output to contain one row.\"\n",
    "    assert last_output_df.shape[1] == 2, \\\n",
    "    \"Expected the output to contain two columns.\"\n",
    "    \n",
    "def test_values():\n",
    "    assert last_output_df.iloc[0,0] == 2700, \\\n",
    "    \"Did you count the number of footwear products?\"\n",
    "    assert last_output_df.iloc[0,1] == 3118.36, \\\n",
    "    \"Did you calculate the median revenue for footwear products?\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "dc": {
     "key": "86"
    },
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    },
    "tags": [
     "context"
    ]
   },
   "source": [
    "## 9. Clothing product performance\n",
    "<p>Recall from the first task that we found there are 3,117 products without missing values for <code>description</code>. Of those, 2,700 are footwear products, which accounts for around 85% of the company's stock. They also generate a median revenue of over $3000 dollars!</p>\n",
    "<p>This is interesting, but we have no point of reference for whether footwear's <code>median_revenue</code> is good or bad compared to other products. So, for our final task, let's examine how this differs to clothing products. We will re-use <code>footwear</code>, adding a filter afterward to count the number of products and <code>median_revenue</code> of products that are not in <code>footwear</code>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "dc": {
     "key": "86"
    },
    "tags": [
     "sample_code"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql:///sports\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>num_clothing_products</th>\n",
       "        <th>median_clothing_revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>417</td>\n",
       "        <td>503.82</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(417, 503.82)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Copy the footwear CTE from the previous task\n",
    "-- Calculate the number of products in info and median revenue from finance\n",
    "-- Inner join info with finance on product_id\n",
    "-- Filter the selection for products with a description not in footwear\n",
    "WITH footwear AS (\n",
    "    SELECT i.description, f.revenue\n",
    "    FROM info i JOIN finance f ON i.product_id = f.product_id\n",
    "    WHERE i.description ILIKE '%shoe%' OR \n",
    "    i.description ILIKE '%trainer%' OR \n",
    "    i.description ILIKE '%foot%' AND\n",
    "    i.description IS NOT NULL\n",
    ")\n",
    "\n",
    "SELECT COUNT(product_name) AS num_clothing_products,\n",
    "     percentile_disc(0.5) WITHIN GROUP (ORDER BY revenue) AS median_clothing_revenue\n",
    "FROM info\n",
    "JOIN finance ON info.product_id = finance.product_id\n",
    "WHERE description NOT IN(SELECT description FROM footwear)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "dc": {
     "key": "86"
    },
    "hide": true,
    "tags": [
     "tests"
    ]
   },
   "outputs": [
    {
     "data": {
      "application/json": "{\"success\": true, \"summary\": {\"tests\": 3, \"failures\": 0, \"errors\": 0}, \"tests\": [{\"name\": \"__main__.test_columns\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_shape\", \"success\": true, \"message\": \"\"}, {\"name\": \"__main__.test_values\", \"success\": true, \"message\": \"\"}]}"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "3/3 tests passed\n"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%nose\n",
    "\n",
    "last_output = _\n",
    "last_output_df = last_output.DataFrame()\n",
    "\n",
    "def test_columns():\n",
    "    assert set(last_output_df.columns) == set(['num_clothing_products', 'median_clothing_revenue']), \\\n",
    "    \"Did you select the correct columns and use the aliases specified in the instructions?\"\n",
    "    \n",
    "def test_shape():\n",
    "    assert last_output_df.shape[0] == 1, \\\n",
    "    \"Expected the output to contain one row.\"\n",
    "    assert last_output_df.shape[1] == 2, \\\n",
    "    \"Expected the output to contain two columns.\"\n",
    "    \n",
    "def test_values():\n",
    "    assert last_output_df.iloc[0,0] == 417, \\\n",
    "    \"Did you count the number of clothing products? Expected there to be 417 items.\"\n",
    "    assert last_output_df.iloc[0,1] == 503.82, \\\n",
    "    \"Did you calculate the median revenue for clothing products? Expected it to be $503.82.\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}