SalesAnalysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "467d9177-f761-4102-88fa-2d3069964b6d",
   "metadata": {},
   "source": [
    "The goal of this project is to understand the overall sales performance of the company."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8ea242d-4db7-449f-8203-cbb91cd63605",
   "metadata": {},
   "source": [
    "Want to understand the following:\n",
    "\n",
    "- Among all product lines, which one has the highest sales?\n",
    "- Which year recorded the highest sales?\n",
    "- Which month experienced the highest sales?\n",
    "- Which country achieved the highest product sales?\n",
    "- Who stands out as the top customer?\n",
    "- What products are usually sold together?\n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5883083c-b17b-4770-b5f8-aaa6fbe4c000",
   "metadata": {},
   "source": [
    "Let's start with the data exploration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "58634cf1-4676-4efa-9310-c87c5ee71744",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "90fc0d54-8c52-4103-8faa-cf1093aa23d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connecting to the MySQL database\n",
    "%sql mysql+mysqlconnector://root:0808Kari!@localhost/test_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "47cd3ed7-c6ad-45cc-a187-ac8932b9afa9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>ORDERNUMBER</th>\n",
       "            <th>QUANTITYORDERED</th>\n",
       "            <th>PRICEEACH</th>\n",
       "            <th>ORDERLINENUMBER</th>\n",
       "            <th>SALES</th>\n",
       "            <th>ORDERDATE</th>\n",
       "            <th>STATUS</th>\n",
       "            <th>QTR_ID</th>\n",
       "            <th>MONTH_ID</th>\n",
       "            <th>YEAR_ID</th>\n",
       "            <th>PRODUCTLINE</th>\n",
       "            <th>MSRP</th>\n",
       "            <th>PRODUCTCODE</th>\n",
       "            <th>CUSTOMERNAME</th>\n",
       "            <th>PHONE</th>\n",
       "            <th>ADDRESSLINE1</th>\n",
       "            <th>ADDRESSLINE2</th>\n",
       "            <th>CITY</th>\n",
       "            <th>STATE</th>\n",
       "            <th>POSTALCODE</th>\n",
       "            <th>COUNTRY</th>\n",
       "            <th>TERRITORY</th>\n",
       "            <th>CONTACTLASTNAME</th>\n",
       "            <th>CONTACTFIRSTNAME</th>\n",
       "            <th>DEALSIZE</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>10107</td>\n",
       "            <td>30</td>\n",
       "            <td>95.7</td>\n",
       "            <td>2</td>\n",
       "            <td>2871</td>\n",
       "            <td>2003-02-24 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>1</td>\n",
       "            <td>2</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Land of Toys Inc.</td>\n",
       "            <td>2125557818</td>\n",
       "            <td>897 Long Airport Avenue</td>\n",
       "            <td></td>\n",
       "            <td>NYC</td>\n",
       "            <td>NY</td>\n",
       "            <td>10022</td>\n",
       "            <td>USA</td>\n",
       "            <td>NA</td>\n",
       "            <td>Yu</td>\n",
       "            <td>Kwai</td>\n",
       "            <td>Small</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10121</td>\n",
       "            <td>34</td>\n",
       "            <td>81.35</td>\n",
       "            <td>5</td>\n",
       "            <td>2765.9</td>\n",
       "            <td>2003-05-07 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>2</td>\n",
       "            <td>5</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Reims Collectables</td>\n",
       "            <td>26.47.1555</td>\n",
       "            <td>59 rue de l&#x27;Abbaye</td>\n",
       "            <td></td>\n",
       "            <td>Reims</td>\n",
       "            <td></td>\n",
       "            <td>51100</td>\n",
       "            <td>France</td>\n",
       "            <td>EMEA</td>\n",
       "            <td>Henriot</td>\n",
       "            <td>Paul</td>\n",
       "            <td>Small</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10134</td>\n",
       "            <td>41</td>\n",
       "            <td>94.74</td>\n",
       "            <td>2</td>\n",
       "            <td>3884.34</td>\n",
       "            <td>2003-07-01 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>3</td>\n",
       "            <td>7</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Lyon Souveniers</td>\n",
       "            <td>+33 1 46 62 7555</td>\n",
       "            <td>27 rue du Colonel Pierre Avia</td>\n",
       "            <td></td>\n",
       "            <td>Paris</td>\n",
       "            <td></td>\n",
       "            <td>75508</td>\n",
       "            <td>France</td>\n",
       "            <td>EMEA</td>\n",
       "            <td>Da Cunha</td>\n",
       "            <td>Daniel</td>\n",
       "            <td>Medium</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10145</td>\n",
       "            <td>45</td>\n",
       "            <td>83.26</td>\n",
       "            <td>6</td>\n",
       "            <td>3746.7</td>\n",
       "            <td>2003-08-25 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>3</td>\n",
       "            <td>8</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Toys4GrownUps.com</td>\n",
       "            <td>6265557265</td>\n",
       "            <td>78934 Hillside Dr.</td>\n",
       "            <td></td>\n",
       "            <td>Pasadena</td>\n",
       "            <td>CA</td>\n",
       "            <td>90003</td>\n",
       "            <td>USA</td>\n",
       "            <td>NA</td>\n",
       "            <td>Young</td>\n",
       "            <td>Julie</td>\n",
       "            <td>Medium</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10159</td>\n",
       "            <td>49</td>\n",
       "            <td>100</td>\n",
       "            <td>14</td>\n",
       "            <td>5205.27</td>\n",
       "            <td>2003-10-10 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>4</td>\n",
       "            <td>10</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Corporate Gift Ideas Co.</td>\n",
       "            <td>6505551386</td>\n",
       "            <td>7734 Strong St.</td>\n",
       "            <td></td>\n",
       "            <td>San Francisco</td>\n",
       "            <td>CA</td>\n",
       "            <td></td>\n",
       "            <td>USA</td>\n",
       "            <td>NA</td>\n",
       "            <td>Brown</td>\n",
       "            <td>Julie</td>\n",
       "            <td>Medium</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10168</td>\n",
       "            <td>36</td>\n",
       "            <td>96.66</td>\n",
       "            <td>1</td>\n",
       "            <td>3479.76</td>\n",
       "            <td>2003-10-28 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>4</td>\n",
       "            <td>10</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Technics Stores Inc.</td>\n",
       "            <td>6505556809</td>\n",
       "            <td>9408 Furth Circle</td>\n",
       "            <td></td>\n",
       "            <td>Burlingame</td>\n",
       "            <td>CA</td>\n",
       "            <td>94217</td>\n",
       "            <td>USA</td>\n",
       "            <td>NA</td>\n",
       "            <td>Hirano</td>\n",
       "            <td>Juri</td>\n",
       "            <td>Medium</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10180</td>\n",
       "            <td>29</td>\n",
       "            <td>86.13</td>\n",
       "            <td>9</td>\n",
       "            <td>2497.77</td>\n",
       "            <td>2003-11-11 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Daedalus Designs Imports</td>\n",
       "            <td>20.16.1555</td>\n",
       "            <td>&quot;184, chausse de Tournai&quot;</td>\n",
       "            <td></td>\n",
       "            <td>Lille</td>\n",
       "            <td></td>\n",
       "            <td>59000</td>\n",
       "            <td>France</td>\n",
       "            <td>EMEA</td>\n",
       "            <td>Rance</td>\n",
       "            <td>Martine</td>\n",
       "            <td>Small</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10188</td>\n",
       "            <td>48</td>\n",
       "            <td>100</td>\n",
       "            <td>1</td>\n",
       "            <td>5512.32</td>\n",
       "            <td>2003-11-18 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Herkku Gifts</td>\n",
       "            <td>+47 2267 3215</td>\n",
       "            <td>&quot;Drammen 121, PR 744 Sentrum&quot;</td>\n",
       "            <td></td>\n",
       "            <td>Bergen</td>\n",
       "            <td></td>\n",
       "            <td>N 5804</td>\n",
       "            <td>Norway</td>\n",
       "            <td>EMEA</td>\n",
       "            <td>Oeztan</td>\n",
       "            <td>Veysel</td>\n",
       "            <td>Medium</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10201</td>\n",
       "            <td>22</td>\n",
       "            <td>98.57</td>\n",
       "            <td>2</td>\n",
       "            <td>2168.54</td>\n",
       "            <td>2003-12-01 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>2003</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Mini Wheels Co.</td>\n",
       "            <td>6505555787</td>\n",
       "            <td>5557 North Pendale Street</td>\n",
       "            <td></td>\n",
       "            <td>San Francisco</td>\n",
       "            <td>CA</td>\n",
       "            <td></td>\n",
       "            <td>USA</td>\n",
       "            <td>NA</td>\n",
       "            <td>Murphy</td>\n",
       "            <td>Julie</td>\n",
       "            <td>Small</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10211</td>\n",
       "            <td>41</td>\n",
       "            <td>100</td>\n",
       "            <td>14</td>\n",
       "            <td>4708.44</td>\n",
       "            <td>2004-01-15 00:00:00</td>\n",
       "            <td>Shipped</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "            <td>2004</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>95</td>\n",
       "            <td>S10_1678</td>\n",
       "            <td>Auto Canal Petit</td>\n",
       "            <td>(1) 47.55.6555</td>\n",
       "            <td>&quot;25, rue Lauriston&quot;</td>\n",
       "            <td></td>\n",
       "            <td>Paris</td>\n",
       "            <td></td>\n",
       "            <td>75016</td>\n",
       "            <td>France</td>\n",
       "            <td>EMEA</td>\n",
       "            <td>Perrier</td>\n",
       "            <td>Dominique</td>\n",
       "            <td>Medium</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('10107', '30', '95.7', '2', '2871', '2003-02-24 00:00:00', 'Shipped', '1', '2', '2003', 'Motorcycles', '95', 'S10_1678', 'Land of Toys Inc.', '2125557818', '897 Long Airport Avenue', '', 'NYC', 'NY', '10022', 'USA', 'NA', 'Yu', 'Kwai', 'Small'),\n",
       " ('10121', '34', '81.35', '5', '2765.9', '2003-05-07 00:00:00', 'Shipped', '2', '5', '2003', 'Motorcycles', '95', 'S10_1678', 'Reims Collectables', '26.47.1555', \"59 rue de l'Abbaye\", '', 'Reims', '', '51100', 'France', 'EMEA', 'Henriot', 'Paul', 'Small'),\n",
       " ('10134', '41', '94.74', '2', '3884.34', '2003-07-01 00:00:00', 'Shipped', '3', '7', '2003', 'Motorcycles', '95', 'S10_1678', 'Lyon Souveniers', '+33 1 46 62 7555', '27 rue du Colonel Pierre Avia', '', 'Paris', '', '75508', 'France', 'EMEA', 'Da Cunha', 'Daniel', 'Medium'),\n",
       " ('10145', '45', '83.26', '6', '3746.7', '2003-08-25 00:00:00', 'Shipped', '3', '8', '2003', 'Motorcycles', '95', 'S10_1678', 'Toys4GrownUps.com', '6265557265', '78934 Hillside Dr.', '', 'Pasadena', 'CA', '90003', 'USA', 'NA', 'Young', 'Julie', 'Medium'),\n",
       " ('10159', '49', '100', '14', '5205.27', '2003-10-10 00:00:00', 'Shipped', '4', '10', '2003', 'Motorcycles', '95', 'S10_1678', 'Corporate Gift Ideas Co.', '6505551386', '7734 Strong St.', '', 'San Francisco', 'CA', '', 'USA', 'NA', 'Brown', 'Julie', 'Medium'),\n",
       " ('10168', '36', '96.66', '1', '3479.76', '2003-10-28 00:00:00', 'Shipped', '4', '10', '2003', 'Motorcycles', '95', 'S10_1678', 'Technics Stores Inc.', '6505556809', '9408 Furth Circle', '', 'Burlingame', 'CA', '94217', 'USA', 'NA', 'Hirano', 'Juri', 'Medium'),\n",
       " ('10180', '29', '86.13', '9', '2497.77', '2003-11-11 00:00:00', 'Shipped', '4', '11', '2003', 'Motorcycles', '95', 'S10_1678', 'Daedalus Designs Imports', '20.16.1555', '\"184, chausse de Tournai\"', '', 'Lille', '', '59000', 'France', 'EMEA', 'Rance', 'Martine', 'Small'),\n",
       " ('10188', '48', '100', '1', '5512.32', '2003-11-18 00:00:00', 'Shipped', '4', '11', '2003', 'Motorcycles', '95', 'S10_1678', 'Herkku Gifts', '+47 2267 3215', '\"Drammen 121, PR 744 Sentrum\"', '', 'Bergen', '', 'N 5804', 'Norway', 'EMEA', 'Oeztan', 'Veysel', 'Medium'),\n",
       " ('10201', '22', '98.57', '2', '2168.54', '2003-12-01 00:00:00', 'Shipped', '4', '12', '2003', 'Motorcycles', '95', 'S10_1678', 'Mini Wheels Co.', '6505555787', '5557 North Pendale Street', '', 'San Francisco', 'CA', '', 'USA', 'NA', 'Murphy', 'Julie', 'Small'),\n",
       " ('10211', '41', '100', '14', '4708.44', '2004-01-15 00:00:00', 'Shipped', '1', '1', '2004', 'Motorcycles', '95', 'S10_1678', 'Auto Canal Petit', '(1) 47.55.6555', '\"25, rue Lauriston\"', '', 'Paris', '', '75016', 'France', 'EMEA', 'Perrier', 'Dominique', 'Medium')]"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading my SALES data\n",
    "%sql SELECT * FROM sales_data LIMIT 10; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "070da287-dc01-4e79-af14-2fcd95377f57",
   "metadata": {},
   "source": [
    "Let's identify the unique values for some of the columns by using the DISTINCT function in SQL (for columns related to our questions of interest)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b32e3d92-35a5-4d08-850c-0927ef0d6c6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>STATUS</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Shipped</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Disputed</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>In Process</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cancelled</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>On Hold</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Resolved</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shipped',),\n",
       " ('Disputed',),\n",
       " ('In Process',),\n",
       " ('Cancelled',),\n",
       " ('On Hold',),\n",
       " ('Resolved',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT(STATUS) FROM sales_data;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d1456b88-afc2-48ca-a4f4-b25d51dbe0e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>PRODUCTLINE</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Motorcycles</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Classic Cars</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Trucks and Buses</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Vintage Cars</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Planes</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ships</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Trains</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Motorcycles',),\n",
       " ('Classic Cars',),\n",
       " ('Trucks and Buses',),\n",
       " ('Vintage Cars',),\n",
       " ('Planes',),\n",
       " ('Ships',),\n",
       " ('Trains',)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT(PRODUCTLINE) FROM sales_data;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ac1eefde-faf9-4bf3-81a4-3bd803a65678",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>YEAR_ID</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2005</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2003',), ('2004',), ('2005',)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT(YEAR_ID) FROM sales_data;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9292f22d-76ce-47be-acbf-c27bbc711599",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COUNTRY</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>USA</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>France</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Norway</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Australia</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Finland</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Austria</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UK</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Spain</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sweden</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Singapore</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canada</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Japan</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Italy</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Denmark</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Belgium</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Philippines</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Germany</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Switzerland</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ireland</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('USA',),\n",
       " ('France',),\n",
       " ('Norway',),\n",
       " ('Australia',),\n",
       " ('Finland',),\n",
       " ('Austria',),\n",
       " ('UK',),\n",
       " ('Spain',),\n",
       " ('Sweden',),\n",
       " ('Singapore',),\n",
       " ('Canada',),\n",
       " ('Japan',),\n",
       " ('Italy',),\n",
       " ('Denmark',),\n",
       " ('Belgium',),\n",
       " ('Philippines',),\n",
       " ('Germany',),\n",
       " ('Switzerland',),\n",
       " ('Ireland',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT(COUNTRY) FROM sales_data;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b66ee5d9-7d07-469d-9c80-3ba8c99b04a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "92 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>CUSTOMERNAME</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Land of Toys Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Reims Collectables</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Lyon Souveniers</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Toys4GrownUps.com</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Corporate Gift Ideas Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Technics Stores Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Daedalus Designs Imports</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Herkku Gifts</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Wheels Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Auto Canal Petit</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Australian Collectors, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Vitachrome Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tekni Collectables Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gift Depot Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>La Rochelle Gifts</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Marta&#x27;s Replicas Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Toys of Finland, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Baane Mini Imports</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Diecast Classics Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Salzburg Collectables</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Souveniers And Things Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FunGiftIdeas.com</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;UK Collectables, Ltd.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Euro Shopping Channel</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Volvo Model Replicas, Co&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Corrida Auto Replicas, Ltd&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Dragon Souveniers, Ltd.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Classic Legends Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Australian Gift Network, Co&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Classic Gift Ideas, Inc&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Saveley &amp; Henriot, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canadian Gift Exchange Network</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West Coast Collectables Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cambridge Collectables Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Super Scale Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Tokyo Collectables, Ltd&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Amica Models &amp; Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Scandinavian Gift Ideas</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Auto Assoc. &amp; Cie.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Gifts Distributors Ltd.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Online Diecast Creations Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Collectables For Less Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Anna&#x27;s Decorations, Ltd&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quebec Home Shopping Network</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Osaka Souveniers Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Motor Mint Distributors Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Atelier graphique</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Classics</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Danish Wholesale Imports</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Stylish Desk Decors, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Creations Ltd.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Alpha Cognac</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Double Decker Gift Stores, Ltd&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Enaco Distributors</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Men &#x27;R&#x27; US Retailers, Ltd.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Collectable Mini Designs Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Petit Auto</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Royal Canadian Collectables, Ltd.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gifts4AllAges.com</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>giftsbymail.co.uk</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Oulu Toy Supplies, Inc.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Sharp Gifts Warehouse</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;La Corne D&#x27;abondance, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Auto Werke</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Diecast Collectables</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Handji Gifts&amp; Co</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cruz &amp; Sons Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Marseille Mini Autos</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Toms Spezialitten, Ltd&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Vida Sport, Ltd&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>L&#x27;ordine Souveniers</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Online Mini Collectables</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Blauer See Auto, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Suominen Souveniers</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Muscle Machine Inc</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Clover Collections, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CAF Imports</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;AV Stores, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Heintze Collectables</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Microscale Inc.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Boards &amp; Toys Co.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Iberia Gift Imports, Corp.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Signal Collectibles Ltd.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Caravy</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Signal Gift Stores</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Norway Gifts By Mail, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rovelli Gifts</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Australian Collectables, Ltd&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gift Ideas Corp.</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Bavarian Collectables Imports, Co.&quot;</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Royale Belge</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Auto-Moto Classics Inc.</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Land of Toys Inc.',),\n",
       " ('Reims Collectables',),\n",
       " ('Lyon Souveniers',),\n",
       " ('Toys4GrownUps.com',),\n",
       " ('Corporate Gift Ideas Co.',),\n",
       " ('Technics Stores Inc.',),\n",
       " ('Daedalus Designs Imports',),\n",
       " ('Herkku Gifts',),\n",
       " ('Mini Wheels Co.',),\n",
       " ('Auto Canal Petit',),\n",
       " ('\"Australian Collectors, Co.\"',),\n",
       " ('Vitachrome Inc.',),\n",
       " ('Tekni Collectables Inc.',),\n",
       " ('Gift Depot Inc.',),\n",
       " ('La Rochelle Gifts',),\n",
       " (\"Marta's Replicas Co.\",),\n",
       " ('\"Toys of Finland, Co.\"',),\n",
       " ('Baane Mini Imports',),\n",
       " ('Diecast Classics Inc.',),\n",
       " ('Salzburg Collectables',),\n",
       " ('Souveniers And Things Co.',),\n",
       " ('FunGiftIdeas.com',),\n",
       " ('\"UK Collectables, Ltd.\"',),\n",
       " ('Euro Shopping Channel',),\n",
       " ('\"Volvo Model Replicas, Co\"',),\n",
       " ('\"Corrida Auto Replicas, Ltd\"',),\n",
       " ('\"Dragon Souveniers, Ltd.\"',),\n",
       " ('Classic Legends Inc.',),\n",
       " ('\"Australian Gift Network, Co\"',),\n",
       " ('\"Classic Gift Ideas, Inc\"',),\n",
       " ('\"Saveley & Henriot, Co.\"',),\n",
       " ('Canadian Gift Exchange Network',),\n",
       " ('West Coast Collectables Co.',),\n",
       " ('Cambridge Collectables Co.',),\n",
       " ('Super Scale Inc.',),\n",
       " ('\"Tokyo Collectables, Ltd\"',),\n",
       " ('Amica Models & Co.',),\n",
       " ('Scandinavian Gift Ideas',),\n",
       " ('Auto Assoc. & Cie.',),\n",
       " ('Mini Gifts Distributors Ltd.',),\n",
       " ('Online Diecast Creations Co.',),\n",
       " ('Collectables For Less Inc.',),\n",
       " ('\"Anna\\'s Decorations, Ltd\"',),\n",
       " ('Quebec Home Shopping Network',),\n",
       " ('Osaka Souveniers Co.',),\n",
       " ('Motor Mint Distributors Inc.',),\n",
       " ('Atelier graphique',),\n",
       " ('Mini Classics',),\n",
       " ('Danish Wholesale Imports',),\n",
       " ('\"Stylish Desk Decors, Co.\"',),\n",
       " ('Mini Creations Ltd.',),\n",
       " ('Alpha Cognac',),\n",
       " ('\"Double Decker Gift Stores, Ltd\"',),\n",
       " ('Enaco Distributors',),\n",
       " ('\"Men \\'R\\' US Retailers, Ltd.\"',),\n",
       " ('Collectable Mini Designs Co.',),\n",
       " ('Petit Auto',),\n",
       " ('\"Royal Canadian Collectables, Ltd.\"',),\n",
       " ('Gifts4AllAges.com',),\n",
       " ('giftsbymail.co.uk',),\n",
       " ('\"Oulu Toy Supplies, Inc.\"',),\n",
       " ('The Sharp Gifts Warehouse',),\n",
       " ('\"La Corne D\\'abondance, Co.\"',),\n",
       " ('Mini Auto Werke',),\n",
       " ('Diecast Collectables',),\n",
       " ('Handji Gifts& Co',),\n",
       " ('Cruz & Sons Co.',),\n",
       " ('Marseille Mini Autos',),\n",
       " ('\"Toms Spezialitten, Ltd\"',),\n",
       " ('\"Vida Sport, Ltd\"',),\n",
       " (\"L'ordine Souveniers\",),\n",
       " ('Online Mini Collectables',),\n",
       " ('\"Blauer See Auto, Co.\"',),\n",
       " ('Suominen Souveniers',),\n",
       " ('Muscle Machine Inc',),\n",
       " ('\"Clover Collections, Co.\"',),\n",
       " ('CAF Imports',),\n",
       " ('\"AV Stores, Co.\"',),\n",
       " ('Heintze Collectables',),\n",
       " ('Microscale Inc.',),\n",
       " ('Boards & Toys Co.',),\n",
       " ('\"Iberia Gift Imports, Corp.\"',),\n",
       " ('Signal Collectibles Ltd.',),\n",
       " ('Mini Caravy',),\n",
       " ('Signal Gift Stores',),\n",
       " ('\"Norway Gifts By Mail, Co.\"',),\n",
       " ('Rovelli Gifts',),\n",
       " ('\"Australian Collectables, Ltd\"',),\n",
       " ('Gift Ideas Corp.',),\n",
       " ('\"Bavarian Collectables Imports, Co.\"',),\n",
       " ('Royale Belge',),\n",
       " ('Auto-Moto Classics Inc.',)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT(CUSTOMERNAME) FROM sales_data;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8300d576-cc7e-41b4-bffd-5886616831b6",
   "metadata": {},
   "source": [
    "From the outputs above, it can be seen that the sales status was categorized into either “Shipped, Disputed, In Process, Cancelled, On Hold, and Resolved.” \n",
    "Data was collected over 3 years: 2003, 2004 and 2005. \n",
    "The company specializes in 7 product lines (Motorcycles, Classic Cars, Trucks and Buses, Vintage Cars, Planes, Ships, Trains), operates in 19 different countries and has a total of 92 customers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e46b1ea-e51a-406f-88c5-1abffbb2e977",
   "metadata": {},
   "source": [
    "Also, it appears there are no empty entries in the fields of our interest. So, let's proceed with the data exploration."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8a5a569-1350-4ea6-af4a-febd04292dcc",
   "metadata": {},
   "source": [
    "#### 1. Among all product lines, which one has the highest sales?\n",
    "In order to find this, I group the sales by product line."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1432b25b-be83-4288-9e11-19e636573627",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>PRODUCTLINE</th>\n",
       "            <th>Revenue</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Classic Cars</td>\n",
       "            <td>3919615.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Vintage Cars</td>\n",
       "            <td>1903150.84</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>1166388.34</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Trucks and Buses</td>\n",
       "            <td>1127789.84</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Planes</td>\n",
       "            <td>975003.57</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ships</td>\n",
       "            <td>714437.13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Trains</td>\n",
       "            <td>226243.47</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Classic Cars', 3919615.66),\n",
       " ('Vintage Cars', 1903150.84),\n",
       " ('Motorcycles', 1166388.34),\n",
       " ('Trucks and Buses', 1127789.84),\n",
       " ('Planes', 975003.57),\n",
       " ('Ships', 714437.13),\n",
       " ('Trains', 226243.47)]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    PRODUCTLINE,\n",
    "    ROUND(SUM(SALES), 3) as Revenue\n",
    "FROM \n",
    "    sales_data\n",
    "GROUP BY \n",
    "    PRODUCTLINE\n",
    "ORDER BY \n",
    "    Revenue DESC;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76848f43-8718-4609-8d62-0a272998c876",
   "metadata": {},
   "source": [
    "Based on the displayed results, Classic Cars is the leading product line in terms of sales, while Trains exhibits the lowest sales among the product lines."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a141c3f-5cae-41d9-8e67-d5e697cb1dc4",
   "metadata": {},
   "source": [
    "#### 2. Which year recorded the highest sales?\n",
    "Now, group the data by the year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "c3ef03f0-ffb7-4c82-af5c-df249b556c88",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Year</th>\n",
       "            <th>Revenue</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>4724162.6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>3516979.54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2005</td>\n",
       "            <td>1791486.71</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2004', 4724162.6), ('2003', 3516979.54), ('2005', 1791486.71)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    YEAR_ID as Year,\n",
    "    ROUND(SUM(SALES), 3) as Revenue\n",
    "FROM \n",
    "    sales_data\n",
    "GROUP BY \n",
    "    YEAR_ID\n",
    "ORDER BY \n",
    "    Revenue DESC;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34f6464a-69c4-4aca-b598-313076f1bdae",
   "metadata": {},
   "source": [
    "From the data presented above, it's evident that 2004 recorded the highest sales compared to the other two years. Interestingly, 2005 stands out with the lowest sales figure. This prompts the question: Why did the sales dip in 2005? So, let's explore that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "3870cd11-636d-44b0-9812-e96364a42176",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "29 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Year</th>\n",
       "            <th>Month</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2003</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2004</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2005</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2005</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2005</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2005</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2005</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2003', '1'),\n",
       " ('2003', '2'),\n",
       " ('2003', '3'),\n",
       " ('2003', '4'),\n",
       " ('2003', '5'),\n",
       " ('2003', '6'),\n",
       " ('2003', '7'),\n",
       " ('2003', '8'),\n",
       " ('2003', '9'),\n",
       " ('2003', '10'),\n",
       " ('2003', '11'),\n",
       " ('2003', '12'),\n",
       " ('2004', '1'),\n",
       " ('2004', '2'),\n",
       " ('2004', '3'),\n",
       " ('2004', '4'),\n",
       " ('2004', '5'),\n",
       " ('2004', '6'),\n",
       " ('2004', '7'),\n",
       " ('2004', '8'),\n",
       " ('2004', '9'),\n",
       " ('2004', '10'),\n",
       " ('2004', '11'),\n",
       " ('2004', '12'),\n",
       " ('2005', '1'),\n",
       " ('2005', '2'),\n",
       " ('2005', '3'),\n",
       " ('2005', '4'),\n",
       " ('2005', '5')]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    YEAR_ID as Year,\n",
    "    MONTH_ID as Month\n",
    "FROM \n",
    "    sales_data\n",
    "GROUP BY \n",
    "    YEAR_ID, MONTH_ID\n",
    "ORDER BY \n",
    "    Year ASC, CAST(Month AS UNSIGNED) ASC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc557709-85c8-430a-b79b-18a15141af76",
   "metadata": {},
   "source": [
    "It appears that sales did not decline in 2005. Rather, sales data were only recorded for the first 5 months of 2005, in contrast to 2003 and 2004, which have sales data recorded for all 12 months."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64b9389e-8c5c-4de4-92bf-c89674059055",
   "metadata": {},
   "source": [
    "#### 3. Which month experienced the highest sales?\n",
    "So, to find out this, I iterate over the years 2003, 2004, and 2005. Then, the SQL query calculates the total sales revenue and the number of orders for each month in the specified year. The results are displayed below each year's heading."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "a9efd82f-1708-4bb6-b0b2-7a7bd30d87ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Year: 2003'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "12 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Month</th>\n",
       "            <th>Revenue</th>\n",
       "            <th>Number_of_Orders</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>1029837.66</td>\n",
       "            <td>28</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>568290.97</td>\n",
       "            <td>17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>263973.36</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>261876.46</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>201609.55</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>197809.3</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>192673.11</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>187731.88</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>174504.9</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>168082.56</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>140836.19</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>129753.6</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('11', 1029837.66, 28),\n",
       " ('10', 568290.97, 17),\n",
       " ('9', 263973.36, 8),\n",
       " ('12', 261876.46, 7),\n",
       " ('4', 201609.55, 7),\n",
       " ('8', 197809.3, 5),\n",
       " ('5', 192673.11, 6),\n",
       " ('7', 187731.88, 6),\n",
       " ('3', 174504.9, 6),\n",
       " ('6', 168082.56, 6),\n",
       " ('2', 140836.19, 3),\n",
       " ('1', 129753.6, 5)]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'Year: 2004'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "12 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Month</th>\n",
       "            <th>Revenue</th>\n",
       "            <th>Number_of_Orders</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>1089048.01</td>\n",
       "            <td>33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>552924.25</td>\n",
       "            <td>13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>461501.27</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>372802.66</td>\n",
       "            <td>11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>327144.09</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>320750.91</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>316577.42</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>311419.53</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>286674.22</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>273438.39</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>206148.12</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>205733.73</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('11', 1089048.01, 33),\n",
       " ('10', 552924.25, 13),\n",
       " ('8', 461501.27, 12),\n",
       " ('12', 372802.66, 11),\n",
       " ('7', 327144.09, 10),\n",
       " ('9', 320750.91, 12),\n",
       " ('1', 316577.42, 8),\n",
       " ('2', 311419.53, 10),\n",
       " ('6', 286674.22, 10),\n",
       " ('5', 273438.39, 8),\n",
       " ('4', 206148.12, 10),\n",
       " ('3', 205733.73, 7)]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'Year: 2005'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Month</th>\n",
       "            <th>Revenue</th>\n",
       "            <th>Number_of_Orders</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>457861.06</td>\n",
       "            <td>14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>374262.76</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>358186.18</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>339543.42</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>261633.29</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('5', 457861.06, 14),\n",
       " ('3', 374262.76, 12),\n",
       " ('2', 358186.18, 12),\n",
       " ('1', 339543.42, 12),\n",
       " ('4', 261633.29, 9)]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for year in [2003, 2004, 2005]:\n",
    "    display(f\"Year: {year}\")\n",
    "    result = %sql SELECT \\\n",
    "                MONTH_ID as Month, \\\n",
    "                ROUND(SUM(SALES), 3) as Revenue, \\\n",
    "                COUNT(DISTINCT ORDERNUMBER) as Number_of_Orders \\\n",
    "            FROM \\\n",
    "                sales_data \\\n",
    "            WHERE \\\n",
    "                YEAR_ID = :year \\\n",
    "            GROUP BY \\\n",
    "                MONTH_ID \\\n",
    "            ORDER BY \\\n",
    "                Revenue DESC;\n",
    "    display(result)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c4bd4b3-8f06-47f5-8af8-929aa7168aaf",
   "metadata": {},
   "source": [
    "According to the results, November stands out as the month with the highest sales for both 2003 and 2004. Conversely, in 2005, May recorded the highest sales. However, it's important to note that the data for 2005 only spans five months."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "ef443936-fa6a-4221-a26b-6627d834ba1d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Year: 2003'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>MONTH_ID</th>\n",
       "            <th>PRODUCTLINE</th>\n",
       "            <th>Revenue</th>\n",
       "            <th>Number_of_Orders</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Classic Cars</td>\n",
       "            <td>452924.3699999999</td>\n",
       "            <td>114</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Vintage Cars</td>\n",
       "            <td>184673.39999999997</td>\n",
       "            <td>66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Trucks and Buses</td>\n",
       "            <td>127062.92000000003</td>\n",
       "            <td>33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>109345.5</td>\n",
       "            <td>31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Ships</td>\n",
       "            <td>79174.8</td>\n",
       "            <td>27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Planes</td>\n",
       "            <td>54133.270000000004</td>\n",
       "            <td>16</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Trains</td>\n",
       "            <td>22523.4</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('11', 'Classic Cars', 452924.3699999999, 114),\n",
       " ('11', 'Vintage Cars', 184673.39999999997, 66),\n",
       " ('11', 'Trucks and Buses', 127062.92000000003, 33),\n",
       " ('11', 'Motorcycles', 109345.5, 31),\n",
       " ('11', 'Ships', 79174.8, 27),\n",
       " ('11', 'Planes', 54133.270000000004, 16),\n",
       " ('11', 'Trains', 22523.4, 9)]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'Year: 2004'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>MONTH_ID</th>\n",
       "            <th>PRODUCTLINE</th>\n",
       "            <th>Revenue</th>\n",
       "            <th>Number_of_Orders</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Classic Cars</td>\n",
       "            <td>372231.8899999999</td>\n",
       "            <td>105</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Vintage Cars</td>\n",
       "            <td>233990.33999999997</td>\n",
       "            <td>65</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Motorcycles</td>\n",
       "            <td>151711.85999999996</td>\n",
       "            <td>39</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Trucks and Buses</td>\n",
       "            <td>123811.14000000001</td>\n",
       "            <td>29</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Planes</td>\n",
       "            <td>121130.7</td>\n",
       "            <td>36</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Ships</td>\n",
       "            <td>63900.85</td>\n",
       "            <td>21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Trains</td>\n",
       "            <td>22271.23</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('11', 'Classic Cars', 372231.8899999999, 105),\n",
       " ('11', 'Vintage Cars', 233990.33999999997, 65),\n",
       " ('11', 'Motorcycles', 151711.85999999996, 39),\n",
       " ('11', 'Trucks and Buses', 123811.14000000001, 29),\n",
       " ('11', 'Planes', 121130.7, 36),\n",
       " ('11', 'Ships', 63900.85, 21),\n",
       " ('11', 'Trains', 22271.23, 6)]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for year in [2003, 2004]:\n",
    "    display(f\"Year: {year}\")\n",
    "    result = %sql \\\n",
    "        SELECT \\\n",
    "            MONTH_ID, \\\n",
    "            PRODUCTLINE, \\\n",
    "            SUM(SALES) as Revenue, \\\n",
    "            COUNT(ORDERNUMBER) as Number_of_Orders \\\n",
    "        FROM \\\n",
    "            sales_data \\\n",
    "        WHERE \\\n",
    "            YEAR_ID = :year and MONTH_ID = 11 \\\n",
    "        GROUP BY \\\n",
    "            MONTH_ID, PRODUCTLINE \\\n",
    "        ORDER BY \\\n",
    "            Revenue DESC;\n",
    "    display(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ae03577-1414-46ec-9b7f-82f41598eea4",
   "metadata": {},
   "source": [
    "Classic Cars is the product line that sold the most in November of 2003 and 2004. Trains, as in the question 1, remains the product line with the smallest sales."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59ec55ab-a642-4a05-9b57-ff2809cee496",
   "metadata": {},
   "source": [
    "#### 4. Which country achieved the highest product sales?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "4cacc958-3e5e-4a70-8ce5-e47cb12d71e1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COUNTRY</th>\n",
       "            <th>Revenue</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>USA</td>\n",
       "            <td>3627982.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Spain</td>\n",
       "            <td>1215686.92</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>France</td>\n",
       "            <td>1110916.52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Australia</td>\n",
       "            <td>630623.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UK</td>\n",
       "            <td>478880.46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Italy</td>\n",
       "            <td>374674.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Finland</td>\n",
       "            <td>329581.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Norway</td>\n",
       "            <td>307463.7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Singapore</td>\n",
       "            <td>288488.41</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Denmark</td>\n",
       "            <td>245637.15</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canada</td>\n",
       "            <td>224078.56</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Germany</td>\n",
       "            <td>220472.09</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sweden</td>\n",
       "            <td>210014.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Austria</td>\n",
       "            <td>202062.53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Japan</td>\n",
       "            <td>188167.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Switzerland</td>\n",
       "            <td>117713.56</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Belgium</td>\n",
       "            <td>108412.62</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Philippines</td>\n",
       "            <td>94015.73</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ireland</td>\n",
       "            <td>57756.43</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('USA', 3627982.83),\n",
       " ('Spain', 1215686.92),\n",
       " ('France', 1110916.52),\n",
       " ('Australia', 630623.1),\n",
       " ('UK', 478880.46),\n",
       " ('Italy', 374674.31),\n",
       " ('Finland', 329581.91),\n",
       " ('Norway', 307463.7),\n",
       " ('Singapore', 288488.41),\n",
       " ('Denmark', 245637.15),\n",
       " ('Canada', 224078.56),\n",
       " ('Germany', 220472.09),\n",
       " ('Sweden', 210014.21),\n",
       " ('Austria', 202062.53),\n",
       " ('Japan', 188167.81),\n",
       " ('Switzerland', 117713.56),\n",
       " ('Belgium', 108412.62),\n",
       " ('Philippines', 94015.73),\n",
       " ('Ireland', 57756.43)]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    COUNTRY,\n",
    "    ROUND(SUM(SALES), 3) as Revenue\n",
    "FROM \n",
    "    sales_data\n",
    "GROUP BY \n",
    "    COUNTRY\n",
    "ORDER BY \n",
    "    Revenue DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b526b3c4-55ad-4e71-b646-1ba9b4ff6340",
   "metadata": {},
   "source": [
    "The results show that the United States achieved the highest product sales, generating a revenue of $3,627,982.83. Following USA, Spain and France secured the second and third positions respectively in terms of sales revenue."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85366d75-bc3f-46d9-8857-49cd651b513d",
   "metadata": {},
   "source": [
    "#### 5. Who stands out as the top customer?\n",
    "To find this, I will perform RFM (Recency, Frequency, Monetary) analysis in SQL, and calculate the recency, frequency, and monetary value for each customer based on their transactions. I think these three key indicators are crucial in assessing customer behavior. Customers who have made purchases more recently are likely to be more engaged and valuable. Those who make frequent purchases are typically more loyal. Finally, clients who spend more money are considered higher value.\n",
    "\n",
    "Based on the RFM scores, customers can be segmented into different categories to tailor marketing strategies. For example, customers with high RFM value are considered the most valuable and may be targeted with loyalty programs or exclusive offers. Customers with moderate scores may require nurturing to increase their engagement and spending. And those with low scores may need re-engagement strategies to encourage repeat purchases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecf6fb0a-5cb4-4388-a341-560fe81c79ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- Convert string dates to date data types\n",
    "UPDATE sales_data\n",
    "SET ORDERDATE = STR_TO_DATE(ORDERDATE, '%m/%d/%Y %H:%i');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "78b2c548-5ec0-49d0-8d63-0c332d462c43",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>CustomerName</th>\n",
       "            <th>LastPurchaseDate</th>\n",
       "            <th>Recency</th>\n",
       "            <th>NumberOfTransactions</th>\n",
       "            <th>MoneyValue</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Euro Shopping Channel</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6896</td>\n",
       "            <td>259</td>\n",
       "            <td>912294.1100000002</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Gifts Distributors Ltd.</td>\n",
       "            <td>2005-05-29 00:00:00</td>\n",
       "            <td>6898</td>\n",
       "            <td>180</td>\n",
       "            <td>654858.06</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Australian Collectors, Co.&quot;</td>\n",
       "            <td>2004-11-29 00:00:00</td>\n",
       "            <td>7079</td>\n",
       "            <td>55</td>\n",
       "            <td>200995.40999999997</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Muscle Machine Inc</td>\n",
       "            <td>2004-12-01 00:00:00</td>\n",
       "            <td>7077</td>\n",
       "            <td>48</td>\n",
       "            <td>197736.93999999997</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>La Rochelle Gifts</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6896</td>\n",
       "            <td>53</td>\n",
       "            <td>180124.9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Dragon Souveniers, Ltd.&quot;</td>\n",
       "            <td>2005-03-02 00:00:00</td>\n",
       "            <td>6986</td>\n",
       "            <td>43</td>\n",
       "            <td>172989.68000000008</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Land of Toys Inc.</td>\n",
       "            <td>2004-11-15 00:00:00</td>\n",
       "            <td>7093</td>\n",
       "            <td>49</td>\n",
       "            <td>164069.44000000003</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Sharp Gifts Warehouse</td>\n",
       "            <td>2005-04-22 00:00:00</td>\n",
       "            <td>6935</td>\n",
       "            <td>40</td>\n",
       "            <td>160010.26999999996</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;AV Stores, Co.&quot;</td>\n",
       "            <td>2004-11-17 00:00:00</td>\n",
       "            <td>7091</td>\n",
       "            <td>51</td>\n",
       "            <td>157807.80999999997</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Anna&#x27;s Decorations, Ltd&quot;</td>\n",
       "            <td>2005-03-09 00:00:00</td>\n",
       "            <td>6979</td>\n",
       "            <td>46</td>\n",
       "            <td>153996.13000000003</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Euro Shopping Channel', '2005-05-31 00:00:00', 6896, 259, 912294.1100000002),\n",
       " ('Mini Gifts Distributors Ltd.', '2005-05-29 00:00:00', 6898, 180, 654858.06),\n",
       " ('\"Australian Collectors, Co.\"', '2004-11-29 00:00:00', 7079, 55, 200995.40999999997),\n",
       " ('Muscle Machine Inc', '2004-12-01 00:00:00', 7077, 48, 197736.93999999997),\n",
       " ('La Rochelle Gifts', '2005-05-31 00:00:00', 6896, 53, 180124.9),\n",
       " ('\"Dragon Souveniers, Ltd.\"', '2005-03-02 00:00:00', 6986, 43, 172989.68000000008),\n",
       " ('Land of Toys Inc.', '2004-11-15 00:00:00', 7093, 49, 164069.44000000003),\n",
       " ('The Sharp Gifts Warehouse', '2005-04-22 00:00:00', 6935, 40, 160010.26999999996),\n",
       " ('\"AV Stores, Co.\"', '2004-11-17 00:00:00', 7091, 51, 157807.80999999997),\n",
       " ('\"Anna\\'s Decorations, Ltd\"', '2005-03-09 00:00:00', 6979, 46, 153996.13000000003)]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Calculate Recency (R), Frequency (F), and Monetary Value (M) for each customer\n",
    "WITH customer_rfm AS (\n",
    "    SELECT\n",
    "        CUSTOMERNAME as CustomerName,\n",
    "        MAX(ORDERDATE) as LastPurchaseDate,\n",
    "        COUNT(ORDERNUMBER) as NumberOfTransactions,\n",
    "        SUM(SALES) AS MoneyValue\n",
    "    FROM\n",
    "        sales_data\n",
    "    GROUP BY\n",
    "        CUSTOMERNAME\n",
    ")\n",
    "\n",
    "-- Calculate Recency\n",
    "SELECT\n",
    "    CustomerName,\n",
    "    LastPurchaseDate,\n",
    "    DATEDIFF(CURRENT_DATE, LastPurchaseDate) as Recency,\n",
    "    NumberOfTransactions,\n",
    "    MoneyValue\n",
    "FROM\n",
    "    customer_rfm\n",
    "ORDER BY\n",
    "    MoneyValue DESC\n",
    "LIMIT 10; \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b4cec87-d175-45fe-990d-165506afdb5b",
   "metadata": {},
   "source": [
    "The recency column shows the number of days since each customer's last purchase. Customers like \"Euro Shopping Channel\" and \"Mini Gifts Distributors Ltd.\" made purchases relatively recently, with recency values of 6895 and 6897 days, respectively. On the other hand, customers like \"Boards & Toys Co.\" and \"Atelier graphique\" made purchases quite a while ago, with recency values of 7007 and 7082 days, respectively.\n",
    "\n",
    "The frequency column represents the number of transactions made by each customer. Customers such as \"Euro Shopping Channel\" and \"Mini Gifts Distributors Ltd.\" have high frequencies, indicating they made numerous purchases. Conversely, customers like \"Bavarian Collectables Imports, Co.\" and \"Boards & Toys Co.\" have lower frequencies, suggesting they made fewer purchases.\n",
    "\n",
    "Customers such as \"Euro Shopping Channel\" and \"Mini Gifts Distributors Ltd.\" have made significant monetary contributions. Meanwhile, customers like \"Boards & Toys Co.\" and \"Atelier graphique\" have lower total spending.\n",
    "\n",
    "Overall, high-value customers who made purchases recently, frequently, and spent a considerable amount are \"Euro Shopping Channel\" and \"Mini Gifts Distributors Ltd.\" \n",
    "Customers with relatively low recency, frequency, and monetary value might require targeted marketing efforts or incentives to increase their engagement and spending.\n",
    "Customers who made purchases quite a while ago and have low spending might be considered inactive or lost customers, requiring different retention strategies."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc9642a9-075a-4758-93a2-308606f995b4",
   "metadata": {},
   "source": [
    "####"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70c312ca-7842-4244-9902-67646729e391",
   "metadata": {},
   "source": [
    "To gain a deeper understanding of the customers, I wanted to categorize customers into distinct segments such as 'new customers', 'potential churners', 'active', 'loyal', and 'slipping away, cannot lose'. This segmentation provides valuable insights that can inform strategies for the sales and marketing teams, helping them better target and engage with different customer groups.\n",
    "\n",
    "- 'lost_customers': These are customers who have low recency, frequency, and monetary value. They used to be active customers but have stopped purchasing from the company.\n",
    "\n",
    "- 'slipping_away': These are customers who were previously active but are now showing a decline in recency, frequency, and monetary value. They are at risk of becoming lost customers if their behavior does not change.\n",
    "\n",
    "- 'new_customers': These are newly acquired customers who have recently made their first purchase. They are still in the early stages of their relationship with the company.\n",
    "\n",
    "- 'potential_churners': These are customers who are showing signs of churn based on their RFM scores. Although they may still be active, their behavior suggests that they could become lost customers in the future if certain actions are not taken.\n",
    "\n",
    "- 'active': These are active and engaged customers who have high recency, frequency, and monetary value. They are valuable to the business and are likely to continue making purchases.\n",
    "\n",
    "- 'loyal': These are loyal customers who consistently show high recency, frequency, and monetary value. They are the most valuable segment as they contribute significantly to the company's revenue and are likely to remain customers for the long term."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "65525ab6-194c-4fd1-98c8-5d3cc6fd1413",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>CustomerName</th>\n",
       "            <th>MonetaryValue</th>\n",
       "            <th>AvgMonetaryValue</th>\n",
       "            <th>Frequency</th>\n",
       "            <th>LastOrderDate</th>\n",
       "            <th>MaxOrderDate</th>\n",
       "            <th>Recency</th>\n",
       "            <th>rfm_recency</th>\n",
       "            <th>rfm_frequency</th>\n",
       "            <th>rfm_monetary</th>\n",
       "            <th>rfm_cell</th>\n",
       "            <th>rfm_cell_string</th>\n",
       "            <th>rfm_segment</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Euro Shopping Channel</td>\n",
       "            <td>912294.11</td>\n",
       "            <td>3522.371</td>\n",
       "            <td>259</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6896</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Gifts Distributors Ltd.</td>\n",
       "            <td>654858.06</td>\n",
       "            <td>3638.1</td>\n",
       "            <td>180</td>\n",
       "            <td>2005-05-29 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6898</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Australian Collectors, Co.&quot;</td>\n",
       "            <td>200995.41</td>\n",
       "            <td>3654.462</td>\n",
       "            <td>55</td>\n",
       "            <td>2004-11-29 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>7079</td>\n",
       "            <td>3</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>344</td>\n",
       "            <td>slipping_away</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Muscle Machine Inc</td>\n",
       "            <td>197736.94</td>\n",
       "            <td>4119.52</td>\n",
       "            <td>48</td>\n",
       "            <td>2004-12-01 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>7077</td>\n",
       "            <td>3</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>344</td>\n",
       "            <td>slipping_away</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>La Rochelle Gifts</td>\n",
       "            <td>180124.9</td>\n",
       "            <td>3398.583</td>\n",
       "            <td>53</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6896</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Dragon Souveniers, Ltd.&quot;</td>\n",
       "            <td>172989.68</td>\n",
       "            <td>4023.016</td>\n",
       "            <td>43</td>\n",
       "            <td>2005-03-02 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6986</td>\n",
       "            <td>3</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>344</td>\n",
       "            <td>slipping_away</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Land of Toys Inc.</td>\n",
       "            <td>164069.44</td>\n",
       "            <td>3348.356</td>\n",
       "            <td>49</td>\n",
       "            <td>2004-11-15 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>7093</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>10</td>\n",
       "            <td>244</td>\n",
       "            <td>slipping_away</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Sharp Gifts Warehouse</td>\n",
       "            <td>160010.27</td>\n",
       "            <td>4000.257</td>\n",
       "            <td>40</td>\n",
       "            <td>2005-04-22 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6935</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;AV Stores, Co.&quot;</td>\n",
       "            <td>157807.81</td>\n",
       "            <td>3094.271</td>\n",
       "            <td>51</td>\n",
       "            <td>2004-11-17 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>7091</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>10</td>\n",
       "            <td>244</td>\n",
       "            <td>slipping_away</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Anna&#x27;s Decorations, Ltd&quot;</td>\n",
       "            <td>153996.13</td>\n",
       "            <td>3347.742</td>\n",
       "            <td>46</td>\n",
       "            <td>2005-03-09 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6979</td>\n",
       "            <td>3</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>344</td>\n",
       "            <td>slipping_away</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Euro Shopping Channel', 912294.11, 3522.371, 259, '2005-05-31 00:00:00', '2005-05-31 00:00:00', 6896, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('Mini Gifts Distributors Ltd.', 654858.06, 3638.1, 180, '2005-05-29 00:00:00', '2005-05-31 00:00:00', 6898, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('\"Australian Collectors, Co.\"', 200995.41, 3654.462, 55, '2004-11-29 00:00:00', '2005-05-31 00:00:00', 7079, 3, 4, 4, 11, '344', 'slipping_away'),\n",
       " ('Muscle Machine Inc', 197736.94, 4119.52, 48, '2004-12-01 00:00:00', '2005-05-31 00:00:00', 7077, 3, 4, 4, 11, '344', 'slipping_away'),\n",
       " ('La Rochelle Gifts', 180124.9, 3398.583, 53, '2005-05-31 00:00:00', '2005-05-31 00:00:00', 6896, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('\"Dragon Souveniers, Ltd.\"', 172989.68, 4023.016, 43, '2005-03-02 00:00:00', '2005-05-31 00:00:00', 6986, 3, 4, 4, 11, '344', 'slipping_away'),\n",
       " ('Land of Toys Inc.', 164069.44, 3348.356, 49, '2004-11-15 00:00:00', '2005-05-31 00:00:00', 7093, 2, 4, 4, 10, '244', 'slipping_away'),\n",
       " ('The Sharp Gifts Warehouse', 160010.27, 4000.257, 40, '2005-04-22 00:00:00', '2005-05-31 00:00:00', 6935, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('\"AV Stores, Co.\"', 157807.81, 3094.271, 51, '2004-11-17 00:00:00', '2005-05-31 00:00:00', 7091, 2, 4, 4, 10, '244', 'slipping_away'),\n",
       " ('\"Anna\\'s Decorations, Ltd\"', 153996.13, 3347.742, 46, '2005-03-09 00:00:00', '2005-05-31 00:00:00', 6979, 3, 4, 4, 11, '344', 'slipping_away')]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Calculate RFM (Recency, Frequency, Monetary Value) metrics for each customer\n",
    "WITH rfm as (\n",
    "    SELECT \n",
    "        CUSTOMERNAME as CustomerName,\n",
    "        ROUND(SUM(SALES), 3) as MonetaryValue,\n",
    "        ROUND(AVG(SALES), 3) as AvgMonetaryValue,\n",
    "        COUNT(ORDERNUMBER) as Frequency,\n",
    "        MAX(ORDERDATE) as LastOrderDate,\n",
    "        (SELECT MAX(ORDERDATE) FROM sales_data) as MaxOrderDate,\n",
    "        DATEDIFF(CURRENT_DATE, MAX(ORDERDATE)) as Recency\n",
    "    FROM \n",
    "        sales_data\n",
    "    GROUP BY \n",
    "        CUSTOMERNAME\n",
    "),\n",
    "rfm_calc as (\n",
    "    -- Calculate quartile ranks for Recency, Frequency, and MonetaryValue\n",
    "    SELECT \n",
    "        r.*,\n",
    "        NTILE(4) OVER (ORDER BY Recency DESC) as rfm_recency,\n",
    "        NTILE(4) OVER (ORDER BY Frequency) as rfm_frequency,\n",
    "        NTILE(4) OVER (ORDER BY MonetaryValue) as rfm_monetary\n",
    "    FROM \n",
    "        rfm r\n",
    ")\n",
    "\n",
    "-- Final selection and categorization of customers based on RFM quartiles.  \n",
    "-- This part calculates the RFM cell value for each customer by summing up their recency, frequency, and monetary quartiles. \n",
    "SELECT \n",
    "    c.*, \n",
    "    rfm_recency + rfm_frequency + rfm_monetary as rfm_cell,\n",
    "    CONCAT(rfm_recency, rfm_frequency, rfm_monetary) as rfm_cell_string,\n",
    "    CASE \n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('111', '112', '121', '122', '123', '132', '211', '212', '114', '141') THEN 'lost_customers'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('133', '134', '143', '244', '334', '343', '344', '144') THEN 'slipping_away'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('311', '411', '331') THEN 'new_customers'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('222', '223', '233', '322') THEN 'potential_churners'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('323', '333', '321', '422', '332', '432') THEN 'active'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('433', '434', '443', '444') THEN 'loyal'\n",
    "    END as rfm_segment\n",
    "FROM \n",
    "    rfm_calc c\n",
    "ORDER BY\n",
    "    MonetaryValue DESC\n",
    "LIMIT 10; \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49a7fcd2-b128-49b7-bfdc-b0d9ad27572f",
   "metadata": {},
   "source": [
    "Let's find all loyal customers. They are the most valuable segment as they contribute significantly to the company's revenue and are likely to remain customers for the long term."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "7190fbef-e062-495b-8d38-136ae067edcc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "14 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>CustomerName</th>\n",
       "            <th>MonetaryValue</th>\n",
       "            <th>AvgMonetaryValue</th>\n",
       "            <th>Frequency</th>\n",
       "            <th>LastOrderDate</th>\n",
       "            <th>MaxOrderDate</th>\n",
       "            <th>Recency</th>\n",
       "            <th>rfm_recency</th>\n",
       "            <th>rfm_frequency</th>\n",
       "            <th>rfm_monetary</th>\n",
       "            <th>rfm_cell</th>\n",
       "            <th>rfm_cell_string</th>\n",
       "            <th>rfm_segment</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Euro Shopping Channel</td>\n",
       "            <td>912294.11</td>\n",
       "            <td>3522.371</td>\n",
       "            <td>259</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6895</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mini Gifts Distributors Ltd.</td>\n",
       "            <td>654858.06</td>\n",
       "            <td>3638.1</td>\n",
       "            <td>180</td>\n",
       "            <td>2005-05-29 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6897</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>La Rochelle Gifts</td>\n",
       "            <td>180124.9</td>\n",
       "            <td>3398.583</td>\n",
       "            <td>53</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6895</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Sharp Gifts Warehouse</td>\n",
       "            <td>160010.27</td>\n",
       "            <td>4000.257</td>\n",
       "            <td>40</td>\n",
       "            <td>2005-04-22 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6934</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Souveniers And Things Co.</td>\n",
       "            <td>151570.98</td>\n",
       "            <td>3295.021</td>\n",
       "            <td>46</td>\n",
       "            <td>2005-05-29 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6897</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Salzburg Collectables</td>\n",
       "            <td>149798.63</td>\n",
       "            <td>3744.966</td>\n",
       "            <td>40</td>\n",
       "            <td>2005-05-17 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6909</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Danish Wholesale Imports</td>\n",
       "            <td>145041.6</td>\n",
       "            <td>4028.933</td>\n",
       "            <td>36</td>\n",
       "            <td>2005-04-15 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6941</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>L&#x27;ordine Souveniers</td>\n",
       "            <td>142601.33</td>\n",
       "            <td>3656.444</td>\n",
       "            <td>39</td>\n",
       "            <td>2005-05-10 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6916</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Reims Collectables</td>\n",
       "            <td>135042.94</td>\n",
       "            <td>3293.73</td>\n",
       "            <td>41</td>\n",
       "            <td>2005-03-30 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6957</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>12</td>\n",
       "            <td>444</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Diecast Classics Inc.</td>\n",
       "            <td>122138.14</td>\n",
       "            <td>3939.94</td>\n",
       "            <td>31</td>\n",
       "            <td>2005-05-30 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6896</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>434</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;Tokyo Collectables, Ltd&quot;</td>\n",
       "            <td>120562.74</td>\n",
       "            <td>3767.586</td>\n",
       "            <td>32</td>\n",
       "            <td>2005-04-22 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6934</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "            <td>10</td>\n",
       "            <td>433</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>&quot;UK Collectables, Ltd.&quot;</td>\n",
       "            <td>118008.27</td>\n",
       "            <td>4069.251</td>\n",
       "            <td>29</td>\n",
       "            <td>2005-04-08 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6948</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "            <td>10</td>\n",
       "            <td>433</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Handji Gifts&amp; Co</td>\n",
       "            <td>115498.73</td>\n",
       "            <td>3208.298</td>\n",
       "            <td>36</td>\n",
       "            <td>2005-04-23 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6933</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "            <td>11</td>\n",
       "            <td>443</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Auto Canal Petit</td>\n",
       "            <td>93170.66</td>\n",
       "            <td>3450.765</td>\n",
       "            <td>27</td>\n",
       "            <td>2005-04-07 00:00:00</td>\n",
       "            <td>2005-05-31 00:00:00</td>\n",
       "            <td>6949</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "            <td>10</td>\n",
       "            <td>433</td>\n",
       "            <td>loyal</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Euro Shopping Channel', 912294.11, 3522.371, 259, '2005-05-31 00:00:00', '2005-05-31 00:00:00', 6895, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('Mini Gifts Distributors Ltd.', 654858.06, 3638.1, 180, '2005-05-29 00:00:00', '2005-05-31 00:00:00', 6897, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('La Rochelle Gifts', 180124.9, 3398.583, 53, '2005-05-31 00:00:00', '2005-05-31 00:00:00', 6895, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('The Sharp Gifts Warehouse', 160010.27, 4000.257, 40, '2005-04-22 00:00:00', '2005-05-31 00:00:00', 6934, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('Souveniers And Things Co.', 151570.98, 3295.021, 46, '2005-05-29 00:00:00', '2005-05-31 00:00:00', 6897, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('Salzburg Collectables', 149798.63, 3744.966, 40, '2005-05-17 00:00:00', '2005-05-31 00:00:00', 6909, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('Danish Wholesale Imports', 145041.6, 4028.933, 36, '2005-04-15 00:00:00', '2005-05-31 00:00:00', 6941, 4, 4, 4, 12, '444', 'loyal'),\n",
       " (\"L'ordine Souveniers\", 142601.33, 3656.444, 39, '2005-05-10 00:00:00', '2005-05-31 00:00:00', 6916, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('Reims Collectables', 135042.94, 3293.73, 41, '2005-03-30 00:00:00', '2005-05-31 00:00:00', 6957, 4, 4, 4, 12, '444', 'loyal'),\n",
       " ('Diecast Classics Inc.', 122138.14, 3939.94, 31, '2005-05-30 00:00:00', '2005-05-31 00:00:00', 6896, 4, 3, 4, 11, '434', 'loyal'),\n",
       " ('\"Tokyo Collectables, Ltd\"', 120562.74, 3767.586, 32, '2005-04-22 00:00:00', '2005-05-31 00:00:00', 6934, 4, 3, 3, 10, '433', 'loyal'),\n",
       " ('\"UK Collectables, Ltd.\"', 118008.27, 4069.251, 29, '2005-04-08 00:00:00', '2005-05-31 00:00:00', 6948, 4, 3, 3, 10, '433', 'loyal'),\n",
       " ('Handji Gifts& Co', 115498.73, 3208.298, 36, '2005-04-23 00:00:00', '2005-05-31 00:00:00', 6933, 4, 4, 3, 11, '443', 'loyal'),\n",
       " ('Auto Canal Petit', 93170.66, 3450.765, 27, '2005-04-07 00:00:00', '2005-05-31 00:00:00', 6949, 4, 3, 3, 10, '433', 'loyal')]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Calculate RFM (Recency, Frequency, Monetary Value) metrics for each customer\n",
    "WITH rfm as (\n",
    "    SELECT \n",
    "        CUSTOMERNAME as CustomerName,\n",
    "        ROUND(SUM(SALES), 3) as MonetaryValue,\n",
    "        ROUND(AVG(SALES), 3) as AvgMonetaryValue,\n",
    "        COUNT(ORDERNUMBER) as Frequency,\n",
    "        MAX(ORDERDATE) as LastOrderDate,\n",
    "        (SELECT MAX(ORDERDATE) FROM sales_data) as MaxOrderDate,\n",
    "        DATEDIFF(CURRENT_DATE, MAX(ORDERDATE)) as Recency\n",
    "    FROM \n",
    "        sales_data\n",
    "    GROUP BY \n",
    "        CUSTOMERNAME\n",
    "),\n",
    "rfm_calc as (\n",
    "    -- Calculate quartile ranks for Recency, Frequency, and MonetaryValue\n",
    "    SELECT \n",
    "        r.*,\n",
    "        NTILE(4) OVER (ORDER BY Recency DESC) as rfm_recency,\n",
    "        NTILE(4) OVER (ORDER BY Frequency) as rfm_frequency,\n",
    "        NTILE(4) OVER (ORDER BY MonetaryValue) as rfm_monetary\n",
    "    FROM \n",
    "        rfm r\n",
    ")\n",
    "\n",
    "-- Final selection and categorization of customers based on RFM quartiles.  \n",
    "-- This part calculates the RFM cell value for each customer by summing up their recency, frequency, and monetary quartiles. \n",
    "SELECT \n",
    "    c.*, \n",
    "    rfm_recency + rfm_frequency + rfm_monetary as rfm_cell,\n",
    "    CONCAT(rfm_recency, rfm_frequency, rfm_monetary) as rfm_cell_string,\n",
    "    CASE \n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('111', '112', '121', '122', '123', '132', '211', '212', '114', '141') THEN 'lost_customers'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('133', '134', '143', '244', '334', '343', '344', '144') THEN 'slipping_away'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('311', '411', '331') THEN 'new_customers'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('222', '223', '233', '322') THEN 'potential_churners'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('323', '333', '321', '422', '332', '432') THEN 'active'\n",
    "        WHEN CONCAT(rfm_recency, rfm_frequency, rfm_monetary) IN ('433', '434', '443', '444') THEN 'loyal'\n",
    "    END as rfm_segment\n",
    "FROM \n",
    "    rfm_calc c\n",
    "HAVING\n",
    "    rfm_segment = 'loyal'\n",
    "ORDER BY\n",
    "    MonetaryValue DESC;  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29201de6-2d14-488f-80c7-2e51f04effc2",
   "metadata": {},
   "source": [
    "#### 6. What products are usually sold together?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "9e6ee5b4-db56-4273-bf98-f048e1acdc1f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/test_db\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Product1</th>\n",
       "            <th>ProductLine1</th>\n",
       "            <th>Product2</th>\n",
       "            <th>ProductLine2</th>\n",
       "            <th>PairFrequency</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>S700_2047</td>\n",
       "            <td>Ships</td>\n",
       "            <td>S72_1253</td>\n",
       "            <td>Planes</td>\n",
       "            <td>26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S24_3949</td>\n",
       "            <td>Planes</td>\n",
       "            <td>S700_4002</td>\n",
       "            <td>Planes</td>\n",
       "            <td>26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S18_2319</td>\n",
       "            <td>Trucks and Buses</td>\n",
       "            <td>S18_3232</td>\n",
       "            <td>Classic Cars</td>\n",
       "            <td>26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S24_2841</td>\n",
       "            <td>Planes</td>\n",
       "            <td>S24_3420</td>\n",
       "            <td>Vintage Cars</td>\n",
       "            <td>26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S50_1341</td>\n",
       "            <td>Vintage Cars</td>\n",
       "            <td>S700_1691</td>\n",
       "            <td>Planes</td>\n",
       "            <td>26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S18_2957</td>\n",
       "            <td>Vintage Cars</td>\n",
       "            <td>S18_3136</td>\n",
       "            <td>Vintage Cars</td>\n",
       "            <td>26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S10_4962</td>\n",
       "            <td>Classic Cars</td>\n",
       "            <td>S18_4600</td>\n",
       "            <td>Trucks and Buses</td>\n",
       "            <td>25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S700_3962</td>\n",
       "            <td>Ships</td>\n",
       "            <td>S72_3212</td>\n",
       "            <td>Ships</td>\n",
       "            <td>25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S10_1949</td>\n",
       "            <td>Classic Cars</td>\n",
       "            <td>S18_1097</td>\n",
       "            <td>Trucks and Buses</td>\n",
       "            <td>25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>S18_1129</td>\n",
       "            <td>Classic Cars</td>\n",
       "            <td>S18_1984</td>\n",
       "            <td>Classic Cars</td>\n",
       "            <td>25</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('S700_2047', 'Ships', 'S72_1253', 'Planes', 26),\n",
       " ('S24_3949', 'Planes', 'S700_4002', 'Planes', 26),\n",
       " ('S18_2319', 'Trucks and Buses', 'S18_3232', 'Classic Cars', 26),\n",
       " ('S24_2841', 'Planes', 'S24_3420', 'Vintage Cars', 26),\n",
       " ('S50_1341', 'Vintage Cars', 'S700_1691', 'Planes', 26),\n",
       " ('S18_2957', 'Vintage Cars', 'S18_3136', 'Vintage Cars', 26),\n",
       " ('S10_4962', 'Classic Cars', 'S18_4600', 'Trucks and Buses', 25),\n",
       " ('S700_3962', 'Ships', 'S72_3212', 'Ships', 25),\n",
       " ('S10_1949', 'Classic Cars', 'S18_1097', 'Trucks and Buses', 25),\n",
       " ('S18_1129', 'Classic Cars', 'S18_1984', 'Classic Cars', 25)]"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH TransactionItems AS (\n",
    "    -- Identifying transaction items (products) for each order along with product line\n",
    "    SELECT s.ORDERNUMBER, s.PRODUCTCODE, s.PRODUCTLINE\n",
    "    FROM sales_data s\n",
    "),\n",
    "OrderSummary AS (\n",
    "    -- Aggregating transaction data to summarize products purchased together in each order\n",
    "    SELECT ORDERNUMBER, GROUP_CONCAT(PRODUCTCODE ORDER BY PRODUCTCODE) AS ProductList\n",
    "    FROM TransactionItems\n",
    "    GROUP BY ORDERNUMBER\n",
    "),\n",
    "ProductPairs AS (\n",
    "    -- Generating all possible pairs of products within each order along with product lines\n",
    "    SELECT o1.ORDERNUMBER, p1.PRODUCTCODE AS Product1, p1.PRODUCTLINE AS ProductLine1,\n",
    "           p2.PRODUCTCODE AS Product2, p2.PRODUCTLINE AS ProductLine2\n",
    "    FROM OrderSummary o1\n",
    "    JOIN TransactionItems p1 ON o1.ORDERNUMBER = p1.ORDERNUMBER\n",
    "    JOIN TransactionItems p2 ON o1.ORDERNUMBER = p2.ORDERNUMBER\n",
    "    WHERE p1.PRODUCTCODE < p2.PRODUCTCODE -- Ensure unique pairs\n",
    ")\n",
    "-- Counting occurrences of each product pair across all orders\n",
    "SELECT Product1, ProductLine1, Product2, ProductLine2, COUNT(*) AS PairFrequency\n",
    "FROM ProductPairs\n",
    "GROUP BY Product1, ProductLine1, Product2, ProductLine2\n",
    "ORDER BY PairFrequency DESC\n",
    "LIMIT 10; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0464161f-9804-4944-9a93-e5cdd0a432c7",
   "metadata": {},
   "source": [
    "Knowing which products tend to be bought together is very important. It helps to figure out what customers really like to buy together, so the company can put those items side by side or suggest them as a package deal. This not only makes shopping easier for customers but also boosts sales by encouraging customers to buy more. By understanding these buying patterns, companies can create targeted promotions and marketing campaigns that resonate with customers' preferences, making their shopping experience even better and keeping them coming back for more."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1135a1e9-d0ef-4461-9120-517563e68533",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}