Marketing Analysis - Challenge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c801af0c",
   "metadata": {},
   "source": [
    "                                               # Marketing Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fffe1f6",
   "metadata": {},
   "source": [
    "# Intro\n",
    "You are a Marketing Analyst\n",
    "The 'Sustainable Clothing Co.' have been running several marketing campaigns and have asked you to provide your insight into whether they have been successful or not. Analyse the following data and answer the questions to form your answer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5fecda10",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting ipython-sql\n",
      "  Downloading ipython_sql-0.5.0-py3-none-any.whl (20 kB)\n",
      "Collecting prettytable (from ipython-sql)\n",
      "  Downloading prettytable-3.8.0-py3-none-any.whl (27 kB)\n",
      "Requirement already satisfied: ipython in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (7.31.1)\n",
      "Collecting sqlalchemy>=2.0 (from ipython-sql)\n",
      "  Downloading SQLAlchemy-2.0.16-cp39-cp39-win_amd64.whl (2.0 MB)\n",
      "     ---------------------------------------- 2.0/2.0 MB 5.3 MB/s eta 0:00:00\n",
      "Collecting sqlparse (from ipython-sql)\n",
      "  Downloading sqlparse-0.4.4-py3-none-any.whl (41 kB)\n",
      "     ---------------------------------------- 41.2/41.2 kB ? eta 0:00:00\n",
      "Requirement already satisfied: six in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (1.16.0)\n",
      "Requirement already satisfied: ipython-genutils in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (4.3.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (1.1.1)\n",
      "Requirement already satisfied: setuptools>=18.5 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (63.4.1)\n",
      "Requirement already satisfied: jedi>=0.16 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.18.1)\n",
      "Requirement already satisfied: decorator in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: pickleshare in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: traitlets>=4.2 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (3.0.20)\n",
      "Requirement already satisfied: pygments in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (2.15.0)\n",
      "Requirement already satisfied: backcall in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: matplotlib-inline in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.1.6)\n",
      "Requirement already satisfied: colorama in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.4.6)\n",
      "Requirement already satisfied: wcwidth in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from prettytable->ipython-sql) (0.2.5)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.0 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.3)\n",
      "Installing collected packages: sqlparse, sqlalchemy, prettytable, ipython-sql\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 1.4.39\n",
      "    Uninstalling SQLAlchemy-1.4.39:\n",
      "      Successfully uninstalled SQLAlchemy-1.4.39\n",
      "Successfully installed ipython-sql-0.5.0 prettytable-3.8.0 sqlalchemy-2.0.16 sqlparse-0.4.4\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d57bcf6a",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting mysqlclient\n",
      "  Downloading mysqlclient-2.1.1-cp39-cp39-win_amd64.whl (178 kB)\n",
      "     -------------------------------------- 178.5/178.5 kB 2.2 MB/s eta 0:00:00\n",
      "Installing collected packages: mysqlclient\n",
      "Successfully installed mysqlclient-2.1.1\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install mysqlclient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "914aeca8",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: ipython-sql in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (0.5.0)\n",
      "Requirement already satisfied: mysql-connector-python in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (8.0.31)\n",
      "Requirement already satisfied: prettytable in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (3.8.0)\n",
      "Requirement already satisfied: ipython in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (7.31.1)\n",
      "Requirement already satisfied: sqlalchemy>=2.0 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (2.0.16)\n",
      "Requirement already satisfied: sqlparse in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (0.4.4)\n",
      "Requirement already satisfied: six in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (1.16.0)\n",
      "Requirement already satisfied: ipython-genutils in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: protobuf<=3.20.1,>=3.11.0 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from mysql-connector-python) (3.20.1)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (4.3.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (1.1.1)\n",
      "Requirement already satisfied: setuptools>=18.5 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (63.4.1)\n",
      "Requirement already satisfied: jedi>=0.16 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.18.1)\n",
      "Requirement already satisfied: decorator in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: pickleshare in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: traitlets>=4.2 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (3.0.20)\n",
      "Requirement already satisfied: pygments in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (2.15.0)\n",
      "Requirement already satisfied: backcall in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: matplotlib-inline in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.1.6)\n",
      "Requirement already satisfied: colorama in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.4.6)\n",
      "Requirement already satisfied: wcwidth in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from prettytable->ipython-sql) (0.2.5)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.0 in c:\\users\\selva jenner\\anaconda3\\lib\\site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.3)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install ipython-sql mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "52ffa731",
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "8c7eabb3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "host = '127.0.0.1'                      # Replace with your MySQL server host\n",
    "database = 'sustainable_clothingDB'     # Replace with your database name\n",
    "user = 'root'                           # Replace with your MySQL username\n",
    "password = 'root'                       # Replace with your MySQL password\n",
    "\n",
    "# Connect to MySQL\n",
    "%load_ext sql\n",
    "%sql mysql+mysqlconnector://{user}:{password}@{host}/{database}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "105eb9a4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>product_id</th>\n",
       "            <th>product_name</th>\n",
       "            <th>category</th>\n",
       "            <th>size</th>\n",
       "            <th>price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>Organic Cotton T-Shirt</td>\n",
       "            <td>Tops</td>\n",
       "            <td>S</td>\n",
       "            <td>29.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Recycled Denim Jeans</td>\n",
       "            <td>Bottoms</td>\n",
       "            <td>M</td>\n",
       "            <td>79.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Hemp Crop Top</td>\n",
       "            <td>Tops</td>\n",
       "            <td>L</td>\n",
       "            <td>24.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Bamboo Lounge Pants</td>\n",
       "            <td>Bottoms</td>\n",
       "            <td>XS</td>\n",
       "            <td>49.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>Eco-Friendly Hoodie</td>\n",
       "            <td>Outerwear</td>\n",
       "            <td>XL</td>\n",
       "            <td>59.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Linen Button-Down Shirt</td>\n",
       "            <td>Tops</td>\n",
       "            <td>M</td>\n",
       "            <td>39.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>Organic Cotton Dress</td>\n",
       "            <td>Dresses</td>\n",
       "            <td>S</td>\n",
       "            <td>69.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>Sustainable Swim Shorts</td>\n",
       "            <td>Swimwear</td>\n",
       "            <td>L</td>\n",
       "            <td>34.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>Recycled Polyester Jacket</td>\n",
       "            <td>Outerwear</td>\n",
       "            <td>XL</td>\n",
       "            <td>89.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>Bamboo Yoga Leggings</td>\n",
       "            <td>Activewear</td>\n",
       "            <td>XS</td>\n",
       "            <td>54.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>Hemp Overalls</td>\n",
       "            <td>Bottoms</td>\n",
       "            <td>M</td>\n",
       "            <td>74.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>Organic Cotton Sweater</td>\n",
       "            <td>Tops</td>\n",
       "            <td>L</td>\n",
       "            <td>49.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>13</td>\n",
       "            <td>Cork Sandals</td>\n",
       "            <td>Footwear</td>\n",
       "            <td>S</td>\n",
       "            <td>39.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>14</td>\n",
       "            <td>Recycled Nylon Backpack</td>\n",
       "            <td>Accessories</td>\n",
       "            <td>One Size</td>\n",
       "            <td>59.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>15</td>\n",
       "            <td>Organic Cotton Skirt</td>\n",
       "            <td>Bottoms</td>\n",
       "            <td>XS</td>\n",
       "            <td>34.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>16</td>\n",
       "            <td>Hemp Baseball Cap</td>\n",
       "            <td>Accessories</td>\n",
       "            <td>One Size</td>\n",
       "            <td>24.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>17</td>\n",
       "            <td>Upcycled Denim Jacket</td>\n",
       "            <td>Outerwear</td>\n",
       "            <td>M</td>\n",
       "            <td>79.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18</td>\n",
       "            <td>Linen Jumpsuit</td>\n",
       "            <td>Dresses</td>\n",
       "            <td>L</td>\n",
       "            <td>69.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>19</td>\n",
       "            <td>Organic Cotton Socks</td>\n",
       "            <td>Accessories</td>\n",
       "            <td>M</td>\n",
       "            <td>9.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>20</td>\n",
       "            <td>Bamboo Bathrobe</td>\n",
       "            <td>Loungewear</td>\n",
       "            <td>XL</td>\n",
       "            <td>69.99</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Organic Cotton T-Shirt', 'Tops', 'S', 29.99),\n",
       " (2, 'Recycled Denim Jeans', 'Bottoms', 'M', 79.99),\n",
       " (3, 'Hemp Crop Top', 'Tops', 'L', 24.99),\n",
       " (4, 'Bamboo Lounge Pants', 'Bottoms', 'XS', 49.99),\n",
       " (5, 'Eco-Friendly Hoodie', 'Outerwear', 'XL', 59.99),\n",
       " (6, 'Linen Button-Down Shirt', 'Tops', 'M', 39.99),\n",
       " (7, 'Organic Cotton Dress', 'Dresses', 'S', 69.99),\n",
       " (8, 'Sustainable Swim Shorts', 'Swimwear', 'L', 34.99),\n",
       " (9, 'Recycled Polyester Jacket', 'Outerwear', 'XL', 89.99),\n",
       " (10, 'Bamboo Yoga Leggings', 'Activewear', 'XS', 54.99),\n",
       " (11, 'Hemp Overalls', 'Bottoms', 'M', 74.99),\n",
       " (12, 'Organic Cotton Sweater', 'Tops', 'L', 49.99),\n",
       " (13, 'Cork Sandals', 'Footwear', 'S', 39.99),\n",
       " (14, 'Recycled Nylon Backpack', 'Accessories', 'One Size', 59.99),\n",
       " (15, 'Organic Cotton Skirt', 'Bottoms', 'XS', 34.99),\n",
       " (16, 'Hemp Baseball Cap', 'Accessories', 'One Size', 24.99),\n",
       " (17, 'Upcycled Denim Jacket', 'Outerwear', 'M', 79.99),\n",
       " (18, 'Linen Jumpsuit', 'Dresses', 'L', 69.99),\n",
       " (19, 'Organic Cotton Socks', 'Accessories', 'M', 9.99),\n",
       " (20, 'Bamboo Bathrobe', 'Loungewear', 'XL', 69.99)]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM sustainable_clothing;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "a2efc5f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>campaign_id</th>\n",
       "            <th>campaign_name</th>\n",
       "            <th>product_id</th>\n",
       "            <th>start_date</th>\n",
       "            <th>end_date</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>Summer Sale</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-06-01</td>\n",
       "            <td>2023-06-30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>New Collection Launch</td>\n",
       "            <td>10</td>\n",
       "            <td>2023-07-15</td>\n",
       "            <td>2023-08-15</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Super Save</td>\n",
       "            <td>7</td>\n",
       "            <td>2023-08-20</td>\n",
       "            <td>2023-09-15</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Summer Sale', 2, datetime.date(2023, 6, 1), datetime.date(2023, 6, 30)),\n",
       " (2, 'New Collection Launch', 10, datetime.date(2023, 7, 15), datetime.date(2023, 8, 15)),\n",
       " (3, 'Super Save', 7, datetime.date(2023, 8, 20), datetime.date(2023, 9, 15))]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT * FROM marketing_campaigns ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "76b537ce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "64 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>transaction_id</th>\n",
       "            <th>product_id</th>\n",
       "            <th>quantity</th>\n",
       "            <th>purchase_date</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>2</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-06-02</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>14</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-06-02</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>5</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-06-05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-06-07</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>19</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-06-10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-06-13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>16</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-06-13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>10</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-06-15</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-06-18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>4</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-06-22</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>18</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-06-26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-06-30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>13</td>\n",
       "            <td>13</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-06-30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>14</td>\n",
       "            <td>4</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-07-04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>15</td>\n",
       "            <td>6</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-07-08</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>16</td>\n",
       "            <td>15</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-07-08</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>17</td>\n",
       "            <td>9</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-07-12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18</td>\n",
       "            <td>20</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-07-12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>19</td>\n",
       "            <td>11</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-07-16</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>20</td>\n",
       "            <td>10</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-07-20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>21</td>\n",
       "            <td>12</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-07-24</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>22</td>\n",
       "            <td>5</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-07-29</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>23</td>\n",
       "            <td>10</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-07-29</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>24</td>\n",
       "            <td>10</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-08-03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>25</td>\n",
       "            <td>19</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-08-08</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>26</td>\n",
       "            <td>3</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-08-14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>27</td>\n",
       "            <td>10</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-08-14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>28</td>\n",
       "            <td>16</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-08-20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>29</td>\n",
       "            <td>18</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-08-27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>30</td>\n",
       "            <td>12</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-09-01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>31</td>\n",
       "            <td>13</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-09-05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>32</td>\n",
       "            <td>7</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-09-05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>33</td>\n",
       "            <td>6</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-09-10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>34</td>\n",
       "            <td>15</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-09-14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>35</td>\n",
       "            <td>9</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-09-14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>36</td>\n",
       "            <td>11</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-09-19</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>37</td>\n",
       "            <td>17</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-09-23</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>38</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-09-28</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>39</td>\n",
       "            <td>14</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-09-28</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>40</td>\n",
       "            <td>5</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-09-30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>41</td>\n",
       "            <td>16</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>42</td>\n",
       "            <td>12</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>43</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>44</td>\n",
       "            <td>7</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-02</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>45</td>\n",
       "            <td>18</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>46</td>\n",
       "            <td>12</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>47</td>\n",
       "            <td>13</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>48</td>\n",
       "            <td>4</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>49</td>\n",
       "            <td>12</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>50</td>\n",
       "            <td>7</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-06</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>51</td>\n",
       "            <td>4</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-08</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>52</td>\n",
       "            <td>8</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-08</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>53</td>\n",
       "            <td>16</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-09</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>54</td>\n",
       "            <td>19</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-09</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>55</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>56</td>\n",
       "            <td>18</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>57</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>58</td>\n",
       "            <td>15</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>59</td>\n",
       "            <td>17</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>60</td>\n",
       "            <td>13</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>61</td>\n",
       "            <td>10</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>62</td>\n",
       "            <td>9</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>63</td>\n",
       "            <td>19</td>\n",
       "            <td>2</td>\n",
       "            <td>2023-10-13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>64</td>\n",
       "            <td>20</td>\n",
       "            <td>1</td>\n",
       "            <td>2023-10-14</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 2, 2, datetime.date(2023, 6, 2)),\n",
       " (2, 14, 1, datetime.date(2023, 6, 2)),\n",
       " (3, 5, 2, datetime.date(2023, 6, 5)),\n",
       " (4, 2, 1, datetime.date(2023, 6, 7)),\n",
       " (5, 19, 2, datetime.date(2023, 6, 10)),\n",
       " (6, 2, 1, datetime.date(2023, 6, 13)),\n",
       " (7, 16, 1, datetime.date(2023, 6, 13)),\n",
       " (8, 10, 2, datetime.date(2023, 6, 15)),\n",
       " (9, 2, 1, datetime.date(2023, 6, 18)),\n",
       " (10, 4, 1, datetime.date(2023, 6, 22)),\n",
       " (11, 18, 2, datetime.date(2023, 6, 26)),\n",
       " (12, 2, 1, datetime.date(2023, 6, 30)),\n",
       " (13, 13, 1, datetime.date(2023, 6, 30)),\n",
       " (14, 4, 1, datetime.date(2023, 7, 4)),\n",
       " (15, 6, 2, datetime.date(2023, 7, 8)),\n",
       " (16, 15, 1, datetime.date(2023, 7, 8)),\n",
       " (17, 9, 2, datetime.date(2023, 7, 12)),\n",
       " (18, 20, 1, datetime.date(2023, 7, 12)),\n",
       " (19, 11, 1, datetime.date(2023, 7, 16)),\n",
       " (20, 10, 1, datetime.date(2023, 7, 20)),\n",
       " (21, 12, 2, datetime.date(2023, 7, 24)),\n",
       " (22, 5, 1, datetime.date(2023, 7, 29)),\n",
       " (23, 10, 1, datetime.date(2023, 7, 29)),\n",
       " (24, 10, 1, datetime.date(2023, 8, 3)),\n",
       " (25, 19, 2, datetime.date(2023, 8, 8)),\n",
       " (26, 3, 1, datetime.date(2023, 8, 14)),\n",
       " (27, 10, 1, datetime.date(2023, 8, 14)),\n",
       " (28, 16, 2, datetime.date(2023, 8, 20)),\n",
       " (29, 18, 1, datetime.date(2023, 8, 27)),\n",
       " (30, 12, 2, datetime.date(2023, 9, 1)),\n",
       " (31, 13, 1, datetime.date(2023, 9, 5)),\n",
       " (32, 7, 1, datetime.date(2023, 9, 5)),\n",
       " (33, 6, 1, datetime.date(2023, 9, 10)),\n",
       " (34, 15, 2, datetime.date(2023, 9, 14)),\n",
       " (35, 9, 1, datetime.date(2023, 9, 14)),\n",
       " (36, 11, 2, datetime.date(2023, 9, 19)),\n",
       " (37, 17, 1, datetime.date(2023, 9, 23)),\n",
       " (38, 2, 1, datetime.date(2023, 9, 28)),\n",
       " (39, 14, 1, datetime.date(2023, 9, 28)),\n",
       " (40, 5, 2, datetime.date(2023, 9, 30)),\n",
       " (41, 16, 1, datetime.date(2023, 10, 1)),\n",
       " (42, 12, 2, datetime.date(2023, 10, 1)),\n",
       " (43, 1, 1, datetime.date(2023, 10, 1)),\n",
       " (44, 7, 1, datetime.date(2023, 10, 2)),\n",
       " (45, 18, 2, datetime.date(2023, 10, 3)),\n",
       " (46, 12, 1, datetime.date(2023, 10, 3)),\n",
       " (47, 13, 1, datetime.date(2023, 10, 4)),\n",
       " (48, 4, 1, datetime.date(2023, 10, 5)),\n",
       " (49, 12, 2, datetime.date(2023, 10, 5)),\n",
       " (50, 7, 1, datetime.date(2023, 10, 6)),\n",
       " (51, 4, 2, datetime.date(2023, 10, 8)),\n",
       " (52, 8, 2, datetime.date(2023, 10, 8)),\n",
       " (53, 16, 1, datetime.date(2023, 10, 9)),\n",
       " (54, 19, 1, datetime.date(2023, 10, 9)),\n",
       " (55, 1, 1, datetime.date(2023, 10, 10)),\n",
       " (56, 18, 2, datetime.date(2023, 10, 10)),\n",
       " (57, 2, 1, datetime.date(2023, 10, 10)),\n",
       " (58, 15, 2, datetime.date(2023, 10, 11)),\n",
       " (59, 17, 2, datetime.date(2023, 10, 13)),\n",
       " (60, 13, 1, datetime.date(2023, 10, 13)),\n",
       " (61, 10, 2, datetime.date(2023, 10, 13)),\n",
       " (62, 9, 1, datetime.date(2023, 10, 13)),\n",
       " (63, 19, 2, datetime.date(2023, 10, 13)),\n",
       " (64, 20, 1, datetime.date(2023, 10, 14))]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT * FROM  transactions ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "d0d237bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>campaign_name</th>\n",
       "            <th>Transactions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Summer Sale</td>\n",
       "            <td>13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>New Collection Launch</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Super Save</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Summer Sale', 13), ('New Collection Launch', 9), ('Super Save', 8)]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "# 1. How many transactions were completed during each marketing campaign\n",
    "\n",
    "SELECT campaign_name,count(transaction_id) as Transactions\n",
    "FROM transactions\n",
    "JOIN marketing_campaigns\n",
    "ON purchase_date\n",
    "WHERE purchase_date BETWEEN start_date and end_date\n",
    "GROUP BY campaign_name;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "179994d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>product_name</th>\n",
       "            <th>Sales_quantity</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Sweater</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Organic Cotton Sweater', Decimal('9'))]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 2. Which product had the highest sales quantity?\n",
    "\n",
    "SELECT product_name , sum(quantity) as Sales_quantity\n",
    "FROM sustainable_clothing\n",
    "JOIN transactions\n",
    "USING (product_id)\n",
    "GROUP BY product_name\n",
    "ORDER BY Sales_quantity DESC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "e0f4d32f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>campaign_name</th>\n",
       "            <th>Total_Revenue</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Summer Sale</td>\n",
       "            <td>1044.82</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>New Collection Launch</td>\n",
       "            <td>499.89</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Super Save</td>\n",
       "            <td>529.89</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Summer Sale', 1044.82),\n",
       " ('New Collection Launch', 499.89),\n",
       " ('Super Save', 529.89)]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 3. What is the total revenue generated from each marketing campaign?\n",
    "\n",
    "SELECT campaign_name,round(sum(quantity * price),2) as Total_Revenue\n",
    "FROM transactions as t\n",
    "JOIN marketing_campaigns \n",
    "ON purchase_date\n",
    "BETWEEN start_date and end_date\n",
    "JOIN sustainable_clothing as s \n",
    "ON s.product_id = t.product_id\n",
    "GROUP BY campaign_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "b79e3db5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>category</th>\n",
       "            <th>Total_revenue</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Bottoms</td>\n",
       "            <td>1289.79</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bottoms', 1289.79)]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 4. What is the top-selling product category based on the total revenue generated?\n",
    "\n",
    "SELECT category,round(sum(price *quantity),2) as Total_revenue\n",
    "FROM transactions as t\n",
    "JOIN sustainable_clothing as s \n",
    "ON s.product_id = t.product_id\n",
    "GROUP BY category\n",
    "ORDER BY Total_revenue DESC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "6a6a0f6a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>product_id</th>\n",
       "            <th>product_name</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Recycled Denim Jeans</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Bamboo Lounge Pants</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>Eco-Friendly Hoodie</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>Bamboo Yoga Leggings</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>Organic Cotton Sweater</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>15</td>\n",
       "            <td>Organic Cotton Skirt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>16</td>\n",
       "            <td>Hemp Baseball Cap</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18</td>\n",
       "            <td>Linen Jumpsuit</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>19</td>\n",
       "            <td>Organic Cotton Socks</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Recycled Denim Jeans'),\n",
       " (4, 'Bamboo Lounge Pants'),\n",
       " (5, 'Eco-Friendly Hoodie'),\n",
       " (10, 'Bamboo Yoga Leggings'),\n",
       " (12, 'Organic Cotton Sweater'),\n",
       " (15, 'Organic Cotton Skirt'),\n",
       " (16, 'Hemp Baseball Cap'),\n",
       " (18, 'Linen Jumpsuit'),\n",
       " (19, 'Organic Cotton Socks')]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 5. Which products had a higher quantity sold compared to the average quantity sold?\n",
    "\n",
    "SELECT s.product_id,product_name FROM sustainable_clothing as s\n",
    "WHERE s.product_id IN (\n",
    "SELECT product_id FROM \n",
    "(SELECT product_id,sum(quantity) as quant\n",
    "FROM transactions as t\n",
    "GROUP BY product_id) as t1\n",
    "WHERE quant \n",
    ">(\n",
    "SELECT AVG(quant)\n",
    "FROM\n",
    "(SELECT sum(quantity) as quant\n",
    "FROM transactions as t\n",
    "GROUP BY product_id)as t2\n",
    ") \n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "d8df391d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "23 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>purchase_date</th>\n",
       "            <th>Average_Revenue</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2023-06-02</td>\n",
       "            <td>109.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-05</td>\n",
       "            <td>119.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-07</td>\n",
       "            <td>79.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-10</td>\n",
       "            <td>19.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-13</td>\n",
       "            <td>52.49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-15</td>\n",
       "            <td>109.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-18</td>\n",
       "            <td>79.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-22</td>\n",
       "            <td>49.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-26</td>\n",
       "            <td>139.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-30</td>\n",
       "            <td>59.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-07-16</td>\n",
       "            <td>74.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-07-20</td>\n",
       "            <td>54.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-07-24</td>\n",
       "            <td>99.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-07-29</td>\n",
       "            <td>57.49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-08-03</td>\n",
       "            <td>54.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-08-08</td>\n",
       "            <td>19.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-08-14</td>\n",
       "            <td>39.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-08-20</td>\n",
       "            <td>49.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-08-27</td>\n",
       "            <td>69.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-09-01</td>\n",
       "            <td>99.98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-09-05</td>\n",
       "            <td>54.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-09-10</td>\n",
       "            <td>39.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-09-14</td>\n",
       "            <td>79.99</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2023, 6, 2), 109.98),\n",
       " (datetime.date(2023, 6, 5), 119.98),\n",
       " (datetime.date(2023, 6, 7), 79.99),\n",
       " (datetime.date(2023, 6, 10), 19.98),\n",
       " (datetime.date(2023, 6, 13), 52.49),\n",
       " (datetime.date(2023, 6, 15), 109.98),\n",
       " (datetime.date(2023, 6, 18), 79.99),\n",
       " (datetime.date(2023, 6, 22), 49.99),\n",
       " (datetime.date(2023, 6, 26), 139.98),\n",
       " (datetime.date(2023, 6, 30), 59.99),\n",
       " (datetime.date(2023, 7, 16), 74.99),\n",
       " (datetime.date(2023, 7, 20), 54.99),\n",
       " (datetime.date(2023, 7, 24), 99.98),\n",
       " (datetime.date(2023, 7, 29), 57.49),\n",
       " (datetime.date(2023, 8, 3), 54.99),\n",
       " (datetime.date(2023, 8, 8), 19.98),\n",
       " (datetime.date(2023, 8, 14), 39.99),\n",
       " (datetime.date(2023, 8, 20), 49.98),\n",
       " (datetime.date(2023, 8, 27), 69.99),\n",
       " (datetime.date(2023, 9, 1), 99.98),\n",
       " (datetime.date(2023, 9, 5), 54.99),\n",
       " (datetime.date(2023, 9, 10), 39.99),\n",
       " (datetime.date(2023, 9, 14), 79.99)]"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 6. What is the average revenue generated per day during the marketing campaigns?\n",
    "\n",
    "SELECT purchase_date,round(AVG(quantity * price),2) as Average_Revenue\n",
    "FROM transactions as t\n",
    "JOIN marketing_campaigns \n",
    "ON purchase_date\n",
    "BETWEEN start_date and end_date\n",
    "JOIN sustainable_clothing as s \n",
    "ON s.product_id = t.product_id\n",
    "GROUP BY purchase_date;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "id": "354a792f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>product_name</th>\n",
       "            <th>percentage_contribution</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Recycled Denim Jeans</td>\n",
       "            <td>13.71</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Linen Jumpsuit</td>\n",
       "            <td>10.49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Sweater</td>\n",
       "            <td>9.64</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bamboo Yoga Leggings</td>\n",
       "            <td>9.42</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Recycled Polyester Jacket</td>\n",
       "            <td>7.71</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eco-Friendly Hoodie</td>\n",
       "            <td>6.42</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bamboo Lounge Pants</td>\n",
       "            <td>5.35</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Upcycled Denim Jacket</td>\n",
       "            <td>5.14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hemp Overalls</td>\n",
       "            <td>4.82</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Dress</td>\n",
       "            <td>4.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Skirt</td>\n",
       "            <td>3.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cork Sandals</td>\n",
       "            <td>3.43</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bamboo Bathrobe</td>\n",
       "            <td>3.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hemp Baseball Cap</td>\n",
       "            <td>2.68</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Linen Button-Down Shirt</td>\n",
       "            <td>2.57</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Recycled Nylon Backpack</td>\n",
       "            <td>2.57</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sustainable Swim Shorts</td>\n",
       "            <td>1.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Socks</td>\n",
       "            <td>1.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton T-Shirt</td>\n",
       "            <td>1.28</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hemp Crop Top</td>\n",
       "            <td>0.54</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Recycled Denim Jeans', 13.71),\n",
       " ('Linen Jumpsuit', 10.49),\n",
       " ('Organic Cotton Sweater', 9.64),\n",
       " ('Bamboo Yoga Leggings', 9.42),\n",
       " ('Recycled Polyester Jacket', 7.71),\n",
       " ('Eco-Friendly Hoodie', 6.42),\n",
       " ('Bamboo Lounge Pants', 5.35),\n",
       " ('Upcycled Denim Jacket', 5.14),\n",
       " ('Hemp Overalls', 4.82),\n",
       " ('Organic Cotton Dress', 4.5),\n",
       " ('Organic Cotton Skirt', 3.75),\n",
       " ('Cork Sandals', 3.43),\n",
       " ('Bamboo Bathrobe', 3.0),\n",
       " ('Hemp Baseball Cap', 2.68),\n",
       " ('Linen Button-Down Shirt', 2.57),\n",
       " ('Recycled Nylon Backpack', 2.57),\n",
       " ('Sustainable Swim Shorts', 1.5),\n",
       " ('Organic Cotton Socks', 1.5),\n",
       " ('Organic Cotton T-Shirt', 1.28),\n",
       " ('Hemp Crop Top', 0.54)]"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 7. What is the percentage contribution of each product to the total revenue?\n",
    "\n",
    "with table2 as(\n",
    "With table1 as(\n",
    "SELECT product_name,round(sum(quantity * price),2) as Product_Revenue\n",
    "FROM transactions as t\n",
    "JOIN sustainable_clothing as s \n",
    "ON s.product_id = t.product_id\n",
    "GROUP BY product_name)\n",
    "SELECT *,sum(Product_Revenue) OVER() as Total_Revenue\n",
    "    FROM table1)\n",
    "SELECT product_name, round((Product_Revenue/Total_Revenue) * 100 ,2) as percentage_contribution \n",
    "FROM table2\n",
    "ORDER BY percentage_contribution DESC; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "id": "04779a32",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Total_sales</th>\n",
       "            <th>AVG_Sales_Marketing_campaigns</th>\n",
       "            <th>outside_Marketing_campaigns</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1.3750</td>\n",
       "            <td>1.3333</td>\n",
       "            <td>0.0417</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('1.3750'), Decimal('1.3333'), Decimal('0.0417'))]"
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 8. Compare the average quantity sold during marketing campaigns to outside the marketing campaigns\n",
    "With table2 as (SELECT AVG(quantity) as Total_sales \n",
    "                FROM transactions),\n",
    "table1 as(\n",
    "SELECT AVG(quantity) as AVG_Sales_Marketing_campaigns\n",
    "FROM transactions as t\n",
    "JOIN sustainable_clothing as s\n",
    "ON t.product_id = s.product_id\n",
    "JOIN marketing_campaigns as m\n",
    "ON purchase_date \n",
    "BETWEEN start_date and end_date)\n",
    "SELECT *,Total_sales - AVG_Sales_Marketing_campaigns As outside_Marketing_campaigns FROM table2,table1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "id": "a7f7e37b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Total_sales</th>\n",
       "            <th>Product_Sales_Marketing_campaigns</th>\n",
       "            <th>Total_sales -Product_Sales_Marketing_campaigns</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4669.12</td>\n",
       "            <td>2074.6</td>\n",
       "            <td>2594.52</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4669.12, 2074.6, 2594.52)]"
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 9. Compare the revenue generated by products inside the marketing campaigns to outside the campaigns\n",
    "\n",
    "With table2 as (SELECT round(SUM(price *quantity),2) as Total_sales \n",
    "                FROM transactions as t2\n",
    "                JOIN sustainable_clothing as s\n",
    "                ON t2.product_id = s.product_id),\n",
    "table1 as(\n",
    "SELECT round(SUM(price *quantity ),2) as Product_Sales_Marketing_campaigns\n",
    "FROM transactions as t3\n",
    "JOIN sustainable_clothing as s\n",
    "ON t3.product_id = s.product_id\n",
    "JOIN marketing_campaigns as m\n",
    "ON purchase_date \n",
    "BETWEEN start_date and end_date)\n",
    "SELECT Total_sales,Product_Sales_Marketing_campaigns,Total_sales -Product_Sales_Marketing_campaigns\n",
    "FROM table2,table1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "id": "e0d8d8af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/sustainable_clothingDB\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>product_name</th>\n",
       "            <th>AVG_daily</th>\n",
       "            <th>Ranking</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Recycled Polyester Jacket</td>\n",
       "            <td>5</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Upcycled Denim Jacket</td>\n",
       "            <td>5</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Socks</td>\n",
       "            <td>4</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton T-Shirt</td>\n",
       "            <td>4</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sustainable Swim Shorts</td>\n",
       "            <td>4</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bamboo Yoga Leggings</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cork Sandals</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hemp Baseball Cap</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Linen Jumpsuit</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Skirt</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Sweater</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Recycled Nylon Backpack</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bamboo Bathrobe</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bamboo Lounge Pants</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eco-Friendly Hoodie</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hemp Crop Top</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hemp Overalls</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Linen Button-Down Shirt</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Recycled Denim Jeans</td>\n",
       "            <td>2</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Organic Cotton Dress</td>\n",
       "            <td>1</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Recycled Polyester Jacket', Decimal('5'), 1),\n",
       " ('Upcycled Denim Jacket', Decimal('5'), 1),\n",
       " ('Organic Cotton Socks', Decimal('4'), 2),\n",
       " ('Organic Cotton T-Shirt', Decimal('4'), 2),\n",
       " ('Sustainable Swim Shorts', Decimal('4'), 2),\n",
       " ('Bamboo Yoga Leggings', Decimal('3'), 3),\n",
       " ('Cork Sandals', Decimal('3'), 3),\n",
       " ('Hemp Baseball Cap', Decimal('3'), 3),\n",
       " ('Linen Jumpsuit', Decimal('3'), 3),\n",
       " ('Organic Cotton Skirt', Decimal('3'), 3),\n",
       " ('Organic Cotton Sweater', Decimal('3'), 3),\n",
       " ('Recycled Nylon Backpack', Decimal('3'), 3),\n",
       " ('Bamboo Bathrobe', Decimal('2'), 4),\n",
       " ('Bamboo Lounge Pants', Decimal('2'), 4),\n",
       " ('Eco-Friendly Hoodie', Decimal('2'), 4),\n",
       " ('Hemp Crop Top', Decimal('2'), 4),\n",
       " ('Hemp Overalls', Decimal('2'), 4),\n",
       " ('Linen Button-Down Shirt', Decimal('2'), 4),\n",
       " ('Recycled Denim Jeans', Decimal('2'), 4),\n",
       " ('Organic Cotton Dress', Decimal('1'), 5)]"
      ]
     },
     "execution_count": 306,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- 10. Rank the products by their average daily quantity sold\n",
    " \n",
    "with table2 as(\n",
    "With table1 as(\n",
    "SELECT product_name,product_id,sum(quantity) OVER(partition by purchase_date) as daily_qty\n",
    "FROM sustainable_clothing\n",
    "JOIN transactions\n",
    "USING(product_id))\n",
    "SELECT DISTINCT product_name,ROUND(AVG(daily_qty) OVER(partition by product_name)) as AVG_daily \n",
    "    FROM table1)\n",
    "SELECT *,dense_rank() OVER(order by AVG_daily DESC) as Ranking FROM table2;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63d0db04",
   "metadata": {},
   "source": [
    "                                                            Gracias"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}