Meesho_assignment.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Meesho_assignment",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/shubh8794/Meesho_sql_assignment/blob/master/Meesho_assignment.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "o4IqZ41M52wI",
        "colab_type": "text"
      },
      "source": [
        "For the Given Data - order_details, orders & products. \n",
        "\n",
        "The Data are linked as follows\n",
        "\n",
        "       Id in orders is OrderId in order_details.\n",
        "      Id in products is productId in order_details.\n",
        "\n",
        "For this Data \n",
        "\n",
        "Find out the GMV(Order Total) per Product\n",
        "\n",
        "Plot a graph of No. of Orders v/s Product Price \n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kAtgCfVv95Jn",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd\n",
        "import sqlite3\n",
        "import matplotlib.pyplot as plt\n",
        "\n",
        "db = sqlite3.connect(':memory:')\n",
        "dfs = pd.read_excel('Q1 (1).xlsx', sheet_name=None)\n",
        "for table, df in dfs.items():\n",
        "    df.to_sql(table, db)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gvWlZVlG4vsq",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def run_query(query):\n",
        "  return pd.read_sql_query(query,db)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9yFJgAnT5Nmk",
        "colab_type": "code",
        "outputId": "918a8c05-7a70-4204-90ad-1ba65d57af63",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "source": [
        "query = 'SELECT * FROM orders LIMIT 5;'\n",
        "run_query(query)"
      ],
      "execution_count": 77,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>index</th>\n",
              "      <th>id</th>\n",
              "      <th>userId</th>\n",
              "      <th>total</th>\n",
              "      <th>created</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>194</td>\n",
              "      <td>330</td>\n",
              "      <td>4760</td>\n",
              "      <td>2017-03-01 14:22:30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>201</td>\n",
              "      <td>142</td>\n",
              "      <td>10395</td>\n",
              "      <td>2017-03-01 16:41:53</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>208</td>\n",
              "      <td>324</td>\n",
              "      <td>10395</td>\n",
              "      <td>2017-03-01 21:51:09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>215</td>\n",
              "      <td>258</td>\n",
              "      <td>4165</td>\n",
              "      <td>2017-03-02 08:22:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>222</td>\n",
              "      <td>232</td>\n",
              "      <td>2940</td>\n",
              "      <td>2017-03-02 11:51:57</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   index   id  userId  total              created\n",
              "0      0  194     330   4760  2017-03-01 14:22:30\n",
              "1      1  201     142  10395  2017-03-01 16:41:53\n",
              "2      2  208     324  10395  2017-03-01 21:51:09\n",
              "3      3  215     258   4165  2017-03-02 08:22:00\n",
              "4      4  222     232   2940  2017-03-02 11:51:57"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 77
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Gka5svQ8BPtX",
        "colab_type": "code",
        "outputId": "33277b9c-a65a-4311-f87d-b97d534c09ed",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "source": [
        "query = 'SELECT * FROM order_details LIMIT 5;'\n",
        "run_query(query)"
      ],
      "execution_count": 78,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>index</th>\n",
              "      <th>id</th>\n",
              "      <th>order_id</th>\n",
              "      <th>product_id</th>\n",
              "      <th>quantity</th>\n",
              "      <th>price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>248</td>\n",
              "      <td>194</td>\n",
              "      <td>180</td>\n",
              "      <td>1</td>\n",
              "      <td>4624</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>265</td>\n",
              "      <td>208</td>\n",
              "      <td>844</td>\n",
              "      <td>1</td>\n",
              "      <td>3366</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>267</td>\n",
              "      <td>208</td>\n",
              "      <td>834</td>\n",
              "      <td>1</td>\n",
              "      <td>3366</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>346</td>\n",
              "      <td>264</td>\n",
              "      <td>1079</td>\n",
              "      <td>1</td>\n",
              "      <td>5814</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>370</td>\n",
              "      <td>285</td>\n",
              "      <td>1419</td>\n",
              "      <td>1</td>\n",
              "      <td>4046</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   index   id  order_id  product_id  quantity  price\n",
              "0      0  248       194         180         1   4624\n",
              "1      1  265       208         844         1   3366\n",
              "2      2  267       208         834         1   3366\n",
              "3      3  346       264        1079         1   5814\n",
              "4      4  370       285        1419         1   4046"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 78
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O01J78gIBUh4",
        "colab_type": "code",
        "outputId": "9066db73-038d-4c9d-ba47-4630211f38d0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "source": [
        "query = 'SELECT * FROM products LIMIT 5;'\n",
        "run_query(query)"
      ],
      "execution_count": 79,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>index</th>\n",
              "      <th>id</th>\n",
              "      <th>name</th>\n",
              "      <th>created</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>142</td>\n",
              "      <td>Multicolored Kurti ( 115K )</td>\n",
              "      <td>2017-02-16 15:11:37</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>168</td>\n",
              "      <td>Multicolored Scarf ( Jute Scarf )</td>\n",
              "      <td>2017-02-16 16:24:11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>190</td>\n",
              "      <td>Jute Scarf</td>\n",
              "      <td>2017-02-17 11:11:19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>146</td>\n",
              "      <td>Multicolored Kurti ( 115K )</td>\n",
              "      <td>2017-02-16 15:11:42</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>148</td>\n",
              "      <td>Multicolored Kurti ( 115K )</td>\n",
              "      <td>2017-02-16 15:11:44</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   index   id                               name              created\n",
              "0      0  142        Multicolored Kurti ( 115K )  2017-02-16 15:11:37\n",
              "1      1  168  Multicolored Scarf ( Jute Scarf )  2017-02-16 16:24:11\n",
              "2      2  190                         Jute Scarf  2017-02-17 11:11:19\n",
              "3      3  146        Multicolored Kurti ( 115K )  2017-02-16 15:11:42\n",
              "4      4  148        Multicolored Kurti ( 115K )  2017-02-16 15:11:44"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 79
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D16ofVpRBxO2",
        "colab_type": "code",
        "outputId": "0d18cbed-ff8c-47bc-9ea5-ba237ff32415",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1969
        }
      },
      "source": [
        "query = '''SELECT p.name,  sum(orders.total) as GMV \n",
        "FROM order_details od \n",
        "LEFT JOIN orders \n",
        "on od.order_id = orders.id\n",
        "LEFT JOIN products p \n",
        "on od.product_id = p.id \n",
        "group by p.name;'''\n",
        "run_query(query)"
      ],
      "execution_count": 80,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>name</th>\n",
              "      <th>GMV</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10 Grams Ganesh-Lakshmi-Saraswati 999 Purity ...</td>\n",
              "      <td>3220</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>3 IN 1 Stand for Kitchen</td>\n",
              "      <td>3360</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>6 Pocket Large Clear Purse Handbag Hanging St...</td>\n",
              "      <td>2142</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Best for Cough &amp; Cold Streamer &amp; Vaporizer  (...</td>\n",
              "      <td>3122</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Designer Alloy Saree Brooch</td>\n",
              "      <td>3395</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Designer Butterfly Inspired Saree Brooch</td>\n",
              "      <td>6986</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Designer Chenile carpet</td>\n",
              "      <td>5810</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Designer Floral Alloy Saree Brooch</td>\n",
              "      <td>3220</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>Designer Fog Fountaion</td>\n",
              "      <td>12572</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>Designer Georgette &amp; Jacquard Saree</td>\n",
              "      <td>4193</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>Designer Gold Plated Hathphool Ring Bracelet</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>Elegance Glace Cotton Double Bedsheet with 2 ...</td>\n",
              "      <td>4403</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>Funky Cluther Hair Bun  (Brown)</td>\n",
              "      <td>4375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>Georgette Chiffon Saree</td>\n",
              "      <td>34930</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>Gold Plated  Meenakari Studded Daily Wear Ban...</td>\n",
              "      <td>20699</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>Gold Plated American Diamond Studded Bangles ...</td>\n",
              "      <td>4655</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>Gold Plated Bangles of 2</td>\n",
              "      <td>3801</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>Gold Plated Daily Wear Designer Bangles Set of 2</td>\n",
              "      <td>4788</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>Gold Plated Multicolor Beads And Meenakari Em...</td>\n",
              "      <td>4788</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>Gold Plated Red Color Dancing Peacock Bangles...</td>\n",
              "      <td>6559</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>Gold Plated Red and Green Bangle Set of 4</td>\n",
              "      <td>7371</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>Gold Plated Traditional Stone Studded Designe...</td>\n",
              "      <td>12873</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>Gold Plated White Pearl Embedded Bangles Set ...</td>\n",
              "      <td>15064</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>Golden Floral Pattern Saree Brooch</td>\n",
              "      <td>1015</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>Kalidaar Silk Palazzo</td>\n",
              "      <td>73080</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>Kitchen storage Canisters with see through li...</td>\n",
              "      <td>4760</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>26</th>\n",
              "      <td>Multicolored Chanderi Suit</td>\n",
              "      <td>7350</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27</th>\n",
              "      <td>Multicolored Cotton Kurti 977_B</td>\n",
              "      <td>4165</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28</th>\n",
              "      <td>Multicolored Rayon Kurti 100K1005</td>\n",
              "      <td>4830</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29</th>\n",
              "      <td>Multicolored Rayon Kurti 100K1007</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6102</th>\n",
              "      <td>Women's Designer Hand Bag</td>\n",
              "      <td>14910</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6103</th>\n",
              "      <td>Women's PolyCotton Yoga Pants Combo Of 2</td>\n",
              "      <td>11445</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6104</th>\n",
              "      <td>Women's Printed  Top &amp; Pyjama Set</td>\n",
              "      <td>4788</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6105</th>\n",
              "      <td>Women's Solid Cotton Tops</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6106</th>\n",
              "      <td>Womens 4 meter Gher Stylish Cambric 60X60 Jaip...</td>\n",
              "      <td>32599</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6107</th>\n",
              "      <td>Womens Cotton Jaipuri Printed Long Length Midi...</td>\n",
              "      <td>38843</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6108</th>\n",
              "      <td>Womenâ¤?s Cotton Jaipuri Printed Kurti</td>\n",
              "      <td>4375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6109</th>\n",
              "      <td>Womenâ¤?s New Knee Length Cotton Jaipuri Print...</td>\n",
              "      <td>9828</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6110</th>\n",
              "      <td>Wonderful Silk Saree</td>\n",
              "      <td>7553</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6111</th>\n",
              "      <td>Wooden Elephant Stool For Decoratives</td>\n",
              "      <td>24052</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6112</th>\n",
              "      <td>Wooden Pen Pencil Holder Container with Drawer...</td>\n",
              "      <td>17192</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6113</th>\n",
              "      <td>Wooden Thela</td>\n",
              "      <td>7392</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6114</th>\n",
              "      <td>Yellow Duck Boat</td>\n",
              "      <td>1890</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6115</th>\n",
              "      <td>Yoga 350ML</td>\n",
              "      <td>3003</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6116</th>\n",
              "      <td>Yoga Grass Mat (4Ft X 2Ft)</td>\n",
              "      <td>3465</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6117</th>\n",
              "      <td>Youful Storage Bag 1</td>\n",
              "      <td>36708</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6118</th>\n",
              "      <td>Youful Storage Bag 2</td>\n",
              "      <td>21056</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6119</th>\n",
              "      <td>Youful Storage Bag 3</td>\n",
              "      <td>18193</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6120</th>\n",
              "      <td>Youful Storage Bag 4</td>\n",
              "      <td>27608</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6121</th>\n",
              "      <td>Zinc Metal Alloy Necklace</td>\n",
              "      <td>20615</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6122</th>\n",
              "      <td>disposable clear plastic gloves Pack of 100</td>\n",
              "      <td>1470</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6123</th>\n",
              "      <td>elec ketle</td>\n",
              "      <td>6195</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6124</th>\n",
              "      <td>kitchen Sink Hanging Bags</td>\n",
              "      <td>2100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6125</th>\n",
              "      <td>simple_round_ring_</td>\n",
              "      <td>749</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6126</th>\n",
              "      <td>stylish Jute Printed Cushion Covers</td>\n",
              "      <td>5138</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6127</th>\n",
              "      <td>stylish Silk Saree</td>\n",
              "      <td>6867</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6128</th>\n",
              "      <td>vivid tea cups ( Set Of 4 )</td>\n",
              "      <td>1967</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6129</th>\n",
              "      <td>â¤?Designer Cotton Patiala Suit</td>\n",
              "      <td>17815</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6130</th>\n",
              "      <td>â¤?Designer Crepe Suit</td>\n",
              "      <td>3150</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6131</th>\n",
              "      <td>â¤?Stylishly Designer Cotton Kurti</td>\n",
              "      <td>8470</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>6132 rows × 2 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                                                   name    GMV\n",
              "0      10 Grams Ganesh-Lakshmi-Saraswati 999 Purity ...   3220\n",
              "1                              3 IN 1 Stand for Kitchen   3360\n",
              "2      6 Pocket Large Clear Purse Handbag Hanging St...   2142\n",
              "3      Best for Cough & Cold Streamer & Vaporizer  (...   3122\n",
              "4                          Designer Alloy Saree Brooch    3395\n",
              "5              Designer Butterfly Inspired Saree Brooch   6986\n",
              "6                               Designer Chenile carpet   5810\n",
              "7                   Designer Floral Alloy Saree Brooch    3220\n",
              "8                                Designer Fog Fountaion  12572\n",
              "9                   Designer Georgette & Jacquard Saree   4193\n",
              "10        Designer Gold Plated Hathphool Ring Bracelet       0\n",
              "11     Elegance Glace Cotton Double Bedsheet with 2 ...   4403\n",
              "12                      Funky Cluther Hair Bun  (Brown)   4375\n",
              "13                              Georgette Chiffon Saree  34930\n",
              "14     Gold Plated  Meenakari Studded Daily Wear Ban...  20699\n",
              "15     Gold Plated American Diamond Studded Bangles ...   4655\n",
              "16                             Gold Plated Bangles of 2   3801\n",
              "17     Gold Plated Daily Wear Designer Bangles Set of 2   4788\n",
              "18     Gold Plated Multicolor Beads And Meenakari Em...   4788\n",
              "19     Gold Plated Red Color Dancing Peacock Bangles...   6559\n",
              "20            Gold Plated Red and Green Bangle Set of 4   7371\n",
              "21     Gold Plated Traditional Stone Studded Designe...  12873\n",
              "22     Gold Plated White Pearl Embedded Bangles Set ...  15064\n",
              "23                  Golden Floral Pattern Saree Brooch    1015\n",
              "24                                Kalidaar Silk Palazzo  73080\n",
              "25     Kitchen storage Canisters with see through li...   4760\n",
              "26                           Multicolored Chanderi Suit   7350\n",
              "27                      Multicolored Cotton Kurti 977_B   4165\n",
              "28                    Multicolored Rayon Kurti 100K1005   4830\n",
              "29                    Multicolored Rayon Kurti 100K1007      0\n",
              "...                                                 ...    ...\n",
              "6102                          Women's Designer Hand Bag  14910\n",
              "6103           Women's PolyCotton Yoga Pants Combo Of 2  11445\n",
              "6104                  Women's Printed  Top & Pyjama Set   4788\n",
              "6105                          Women's Solid Cotton Tops      0\n",
              "6106  Womens 4 meter Gher Stylish Cambric 60X60 Jaip...  32599\n",
              "6107  Womens Cotton Jaipuri Printed Long Length Midi...  38843\n",
              "6108             Womenâ¤?s Cotton Jaipuri Printed Kurti   4375\n",
              "6109  Womenâ¤?s New Knee Length Cotton Jaipuri Print...   9828\n",
              "6110                               Wonderful Silk Saree   7553\n",
              "6111              Wooden Elephant Stool For Decoratives  24052\n",
              "6112  Wooden Pen Pencil Holder Container with Drawer...  17192\n",
              "6113                                       Wooden Thela   7392\n",
              "6114                                   Yellow Duck Boat   1890\n",
              "6115                                        Yoga 350ML    3003\n",
              "6116                         Yoga Grass Mat (4Ft X 2Ft)   3465\n",
              "6117                               Youful Storage Bag 1  36708\n",
              "6118                               Youful Storage Bag 2  21056\n",
              "6119                               Youful Storage Bag 3  18193\n",
              "6120                               Youful Storage Bag 4  27608\n",
              "6121                          Zinc Metal Alloy Necklace  20615\n",
              "6122        disposable clear plastic gloves Pack of 100   1470\n",
              "6123                                        elec ketle    6195\n",
              "6124                          kitchen Sink Hanging Bags   2100\n",
              "6125                                 simple_round_ring_    749\n",
              "6126                stylish Jute Printed Cushion Covers   5138\n",
              "6127                                 stylish Silk Saree   6867\n",
              "6128                        vivid tea cups ( Set Of 4 )   1967\n",
              "6129                    â¤?Designer Cotton Patiala Suit  17815\n",
              "6130                             â¤?Designer Crepe Suit   3150\n",
              "6131                 â¤?Stylishly Designer Cotton Kurti   8470\n",
              "\n",
              "[6132 rows x 2 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 80
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HyKsmQTJEZ44",
        "colab_type": "code",
        "outputId": "586b9404-6980-4972-f3cf-907fd1551e65",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "source": [
        "query = '''SELECT p.name, sum(od.quantity) as order_numbers, od.price \n",
        "FROM order_details od \n",
        "LEFT JOIN products p \n",
        "on od.product_id = p.id \n",
        "group by p.name;'''\n",
        "q1_ans2_df = run_query(query)\n",
        "q1_ans2_df.head()"
      ],
      "execution_count": 81,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>name</th>\n",
              "      <th>order_numbers</th>\n",
              "      <th>price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10 Grams Ganesh-Lakshmi-Saraswati 999 Purity ...</td>\n",
              "      <td>4</td>\n",
              "      <td>3128</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>3 IN 1 Stand for Kitchen</td>\n",
              "      <td>2</td>\n",
              "      <td>1632</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>6 Pocket Large Clear Purse Handbag Hanging St...</td>\n",
              "      <td>1</td>\n",
              "      <td>2081</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Best for Cough &amp; Cold Streamer &amp; Vaporizer  (...</td>\n",
              "      <td>2</td>\n",
              "      <td>1516</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Designer Alloy Saree Brooch</td>\n",
              "      <td>2</td>\n",
              "      <td>1156</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                                name  order_numbers  price\n",
              "0   10 Grams Ganesh-Lakshmi-Saraswati 999 Purity ...              4   3128\n",
              "1                           3 IN 1 Stand for Kitchen              2   1632\n",
              "2   6 Pocket Large Clear Purse Handbag Hanging St...              1   2081\n",
              "3   Best for Cough & Cold Streamer & Vaporizer  (...              2   1516\n",
              "4                       Designer Alloy Saree Brooch               2   1156"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 81
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lQMbK2Oujcmh",
        "colab_type": "code",
        "outputId": "7c8f777f-2cb0-4d57-9272-fa33f50d488d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 308
        }
      },
      "source": [
        " import seaborn as sns; sns.set()\n",
        " sns.scatterplot(x=\"order_numbers\", y=\"price\", data=q1_ans2_df)"
      ],
      "execution_count": 82,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<matplotlib.axes._subplots.AxesSubplot at 0x7f8a27fc8d68>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 82
        },
        {
          "output_type": "display_data",
          "data": {
            "image/png": "iVBORw0KGgoAAAANSUhEUgAAAaAAAAESCAYAAAC7NAEnAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBo\ndHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAIABJREFUeJzt3XtgFNXdPvBnZnY3m3s2ISEBVAQL\nRi4SCKEIigISiwi2tCXyE0FuatoC9u37mmALWJSAtLaogKJUtK+voS1eaLSKgrUClUsBMYioKSKV\nkECygdw2uztzfn8sGdjsLmZhk8lmn88/ys5ezp7NzHfOOd9zjiSEECAiImpnstEFICKiyMQARERE\nhmAAIiIiQzAAERGRIRiAiIjIEAxARERkCAYgIiIyBAMQEREZggGIiIgMwQBERESGYAAiIiJDMAAR\nEZEhGICIiMgQJqML0FHZ7fXQtOAXCk9JiUNVVV0blCh8sU78Y734x3rxFQ51IssSbLbYoF7DABSA\npolLCkDNryVvrBP/WC/+sV58dcY6YRccEREZggGIiIgMwQBERESGYAAiIiJDMACFiKLIEIqMSnsD\nhCJDUVi1REQXwyy4EFAUGfYGF5Zt2I1KeyPSbNFYOCMHthgzVFUzunhERB0Sb9NDwA3owQcAKu2N\nWLZhN9zGFouIqENjAAoBVRN68GlWaW+E2gnz9omIQoUBKAQUWUKaLdrrsTRbNBRZMqhEREQdX7sH\noKeffhp9+/bF559/DgA4cOAAJk6ciNzcXMycORNVVVX6c9viWFswAVg4I0cPQs1jQBxgIyIKrF0D\n0KFDh3DgwAF0794dAKBpGv77v/8bixYtwjvvvIPs7Gz85je/abNjbUVVNdhizCjKH4H1v7wVRfkj\nmIBARPQt2i0AOZ1O/PrXv8aSJUv0x0pLSxEVFYXs7GwAQF5eHt5+++02O9aWVFWDpGpIs8VAUjUG\nHyKib9FuvUSrVq3CxIkT0aNHD/2x8vJydOvWTf93cnIyNE1DTU1NmxxLSkpqdXlTUuIu9asiNTX+\nkl/bWbFO/GO9+Md68dUZ66RdAtD+/ftRWlqKX/ziF+3xcSFRVVV3SavPpqbG49Sp2jYoUfhinfjH\nevGP9eIrHOpElqWgb9zbJQDt2bMHZWVlGDNmDADg5MmTmDVrFqZNm4YTJ07oz6uuroYsy0hKSkJG\nRkbIjxERUcfRLmNAc+fOxfbt27Ft2zZs27YN6enpWL9+PWbPng2Hw4G9e/cCAIqLi3HbbbcBAPr3\n7x/yY0RE1HEYmiksyzIef/xxLF68GE1NTejevTtWrlzZZseIiKjjkIQQnK7vB8eAQod14h/rxT/W\ni69wqJNLGQPiSghERGQIBiAiIjIEAxARERmCAYiIiAzBAERERIZgACIiIkMwABERkSEYgIiIyBAM\nQEREZAgGICIiMgQDEBERGYIBiIiIDMEAREREhmAAIiIiQzAAERGRIRiAiIjIEAxARERkCAYgIiIy\nBAMQEREZggGIiIgMwQBERESGYAAiIiJDMAAREZEhGICIiMgQDEBERGQIBiAiIjIEAxARERmCAYiI\niAzBAERERIZgACIiIkMwABERkSEYgIiIyBAMQEREZAgGICIiMgQDEBERGYIBiIiIDMEAREREhmAA\nIiIiQzAAERGRIdotAOXn52PixIm48847MXXqVBw+fBgAcPToUUyZMgW5ubmYMmUKvvrqK/01bXGM\niIg6hnYLQCtWrMDmzZvx+uuvY+bMmVi4cCEAYPHixZg6dSreeecdTJ06FYsWLdJf0xbHiIioY2i3\nABQfH6//f11dHSRJQlVVFT799FNMmDABADBhwgR8+umnqK6ubpNjbUlRZAhFRqW9AUKRoSjs3SQi\nuhhTe37Yww8/jB07dkAIgeeffx7l5eXo2rUrFEUBACiKgrS0NJSXl0MIEfJjycnJrS5rSkpcq5+r\naQLHTp7Fo3/YhUp7I9Js0fjlzGG4Kj0Bsiy1+n06s9TU+G9/UgRivfjHevHVGeukXQPQY489BgB4\n/fXX8fjjj2P+/Pnt+fFBqaqqg6aJVj1XKLIefACg0t6IR/+wC0X5IyCpWlsWMyykpsbj1Klao4vR\n4bBe/GO9+AqHOpFlKagbd8CgLLg777wTu3btQnp6OioqKqCqKgBAVVVUVlYiIyMDGRkZIT/WVlRN\n6MGnWaW9EWorAxgRUSRqlwBUX1+P8vJy/d/btm1DYmIiUlJSkJmZiZKSEgBASUkJMjMzkZyc3CbH\n2ooiS0izRXs9lmaLhsLuNyKigCQhRJvfpp8+fRr5+flobGyELMtITEzEQw89hH79+qGsrAwFBQU4\ne/YsEhISsGLFCvTq1QsA2uRYawXTBacoMupdKiqqG2C1mOBwutE1OQaxZgUqu+DCovvACKwX/1gv\nvsKhTi6lC65dAlA4CjYA1TS48NiG3XoSwsMzcpAUY2YAQnicPEZgvfjHevEVDnUSNmNAnY0b0IMP\n4Bn/eWzDbriNLRYRUYfGABQCTEIgIgoeA1AIMAmBiCh4DEAhYAKwcEaOHoTSbNFYOCOnfSdZERGF\nGV4jQ0BVNdhizCjKHwFIEiAETOceJyIi/9gCChFV1SCpGtJsMZBUjcGHiOhbMAAREZEhGIBChKth\nExEFh2NAIaAoMuwNLiy7YCLqwhk5sHEiKhFRQLxNDwE3oAcfwDMHaBknohIRXRQDUAhwIioRUfAY\ngEKAE1GJiILHABQCnIhKRBQ8XiNDgBNRiYiCxxZQiHAiKhFRcBiAiIjIEAxARERkCAYgIiIyBANQ\niHApHiKi4DALLgS4FA8RUfB4mx4CXIqHiCh4DEAhwKV4iIiCxwAUAlyKh4goeAxAIcCleIiIgsdr\nZAioqoaUeAuK8kdCFQKKJMGiAC6nanTRiIg6LAagEFAUGbUONyqqG2C1mOBwutE1OQaxZoVZcERE\nATAAhYBQJNirm7B200E9DXt+XhZiUmMBNoKIiPziGFAIuFWBVcX7vdKwVxXvh1tlFhwRUSBBt4DK\ny8tRUVGBQYMGtUV5wpKmCdjirZg9aQDiY8yobXBh07YvoGmM8EREgbQ6AJ04cQI///nP8dlnn0GS\nJOzfvx9vv/02PvzwQzz22GNtWcYOL8qs4J7bM/VWUHMXXJRZhurkGBARkT+tvkFftGgRbr75Zuzb\ntw8mkydujRgxAjt37myzwoULNUAXnMouOCKigFodgD755BPMnTsXsixDkjwTLOPj41FbW9tmhQsX\nbk3zuxKCW2Prh4gokFYHoJSUFBw7dszrsS+//BIZGRkhL1S4CbQSgknmCBARUSCtvkLOnDkT999/\nPzZt2gS3242SkhI8+OCDmDNnTluWLyyYADzcYiWE+XlZcLjc3JaBiCgASQjR6oGK9957Dxs3bsSJ\nEyeQkZGBvLw8jB07ti3LZ5iqqjpoQSwmarKaUNfghiwBqgbsPPgNrrkiCd1T46DIEkxAxE5KTU2N\nx6lT7KptifXiH+vFVzjUiSxLSEmJC+o1QaVhjx07ttMGnMuhKDKqzzTpWzIM69cVU27ti+Uv7uH+\nQEREAbS6f+jRRx/Fvn37vB7bt29fxKdgA777AY0ZepUefADuD0RE5E+rA1BJSQn69+/v9Vj//v1R\nUlIS8kKFG00TuDXnSjy3cCzWFY5Br+4JsMVbvZ7D/YGIiLy1ugtOkiS0HC5SVRUaU40RbTUh+7p0\nPLx2h9dE1JfePIwjX9sBXLA/EOcGEREBCKIFlJ2djd///vd6wNE0DU899RSys7O/9bV2ux1z5sxB\nbm4u7rjjDvz0pz9FdXU1AODAgQOYOHEicnNzMXPmTFRVVemva4tjbaHJpaGoxZbcq4r3I29cHwDc\nH4iIyJ9WZ8GdPHkS9913H06dOoVu3bqhvLwcqampeOaZZ5Cenn7R19bU1ODIkSMYNmwYAGDFihU4\nc+YMHn30UeTm5qKoqAjZ2dlYs2YNjh8/jqKiImiaFvJjwQgmC84tSZhbtNXn8XWFYwEIZsGFQQaP\nEVgv/rFefIVDnVxKFlyrW0Dp6el47bXXsHr1asyaNQurV6/Gq6+++q3BBwCSkpL04AMAgwYNwokT\nJ1BaWoqoqCi9FZWXl4e3334bANrkWFtRZDngltwmISCpWsQGHyKiQILqFZJlGVlZWZf1gZqm4ZVX\nXsHo0aNRXl6Obt266ceSk5OhaRpqamra5FhSUlKryxlMJLfXOlAwfahX2nXB9KEwm2XYkmNa/T6d\nWWpqvNFF6JBYL/6xXnx1xjq5aAD63ve+h7/97W8AgFGjRulrwLX097//vdUfuHTpUsTExODuu+/G\nu+++2/qStrNguuCEIuPD/f/B4tnDocieiajv7f4KE0b26vDN5vYQDt0HRmC9+Md68RUOdRLyiahL\nly7V/3/lypWXVqoLrFixAseOHcMzzzwDWZaRkZGBEydO6Merq6shyzKSkpLa5FhbMQH43oir4XZ7\nApYkef5tAjdEJSIK5KIBqHkcRVVVbNq0CUuXLoXFYrmkD3riiSdQWlqKdevW6e/Rv39/OBwO7N27\nF9nZ2SguLsZtt93WZsfaiqxIqK916ZlwabZoFM7IQXS8BSojEBGRX63Oghs5ciTef/99mM3moD/k\niy++wIQJE9CzZ09YrZ4Jmj169MDq1auxb98+LF68GE1NTejevTtWrlyJLl26AECbHGut4LrgFBSu\n2e61JUOaLRpF+SMhMQKFRfeBEVgv/rFefIVDnVxKF1yrA9Bzzz2H2tpa/OxnP7ukIBRuQpWGbRLM\nfguHk8cIrBf/WC++wqFO2nQx0v/93//F6dOn8cILLyA5OVlfGUGSpKCSEDqj5jTsli0gz8oHfp6v\nyHADUDXOESKiyNXqABSKJITOSlHgNw1bUSRoasvnyrA3uPTFS7lSNhFFqlYHoEGDBmHt2rV48803\nUVlZibS0NIwfPx4PPPBAW5YvLKgqAqRh90bLxPWWK2c3r5RdlD/C57lERJ1ZqwPQkiVLcPToUTz8\n8MPo3r07vvnmGzz77LOoqKgIepmbzsZslnDT4CvwyPP/9MqCM5sluFu0gFRNeHXVAedXyuZacUQU\nSVp9zdu6dSveffddJCQkAACuueYaXH/99Rg3blybFS5cyJKE2GgTlswZDlkCNAGYTBJkPxN3FVm6\nyHgRV8omosjR6gDUpUsXNDY26gEIAJqampCamtomBQsnTU4Nv1y7038adovnmgAsnJHjMwbESatE\nFGlaHYAmTZqE2bNnY9q0aejatStOnjyJl19+GZMmTcI///lP/XnDhw9vk4J2ZKqmwRZvxexJAxAf\nY0Ztgwubtn3ht1tNVTXYYswoyh/BLDgiimitngc0evTob38zScLWrb7zYcJRMPOAZIsJJ6sa8MT/\n/Utv1fx86hCkp8RAc3Ij7nCYw2AE1ot/rBdf4VAnbToPaNu2bUEXKFIIIfTgA3iSCp74v39hxU9H\nGlwyIqKOi4lXIeByaxh4TRd8/+bv6GnYr/39C7jcAlGKrHevcQIqEdF5DEAhYI0yYfyIq73SsAum\nD4U1SvFsYa5yAioRUUut3hGVAnO5NWx89whmTxqAovwRmD1pADa+ewQulwb3udTqQBNQOUJERJGK\nASgkBH40tg/MJk/Stdkk4Udj+wCSgHaucXOxCahERJGIXXAhoMgympwq1m46qHevzc/LgiLLkIQA\nVMBilrF49jBYLSY9Tdte6+AEVCKKWAxAIaCqAquK93t1r60q3o+i/JEwQQCKjJpap0+AssVHcQIq\nEUUsdsGFgKppAbvXVFWDG8BjLcZ/VhXvh9VsYgICEUUsBqAQMCme/YAulGaLhuncmFCg8R+3xuBD\nRJGLASgEFFnCL+4eogeh5i42h9MNRZH1BUgvpC9ASkQUoTgGFAJOtwZFkfTVsJsnoh788jSWnxsH\n4gKkRETeGIBCwGySoUgyljznvR/QLdlXQMCT4cYFSImIvLELLgSEED6TTIs27IYECWfqnRCKBFXV\nIKkaTEJAUjUGHyKKeAxAIeBy+08ySIqPwvIX9+irIRAR0XkMQCEgy/CbZCDLEirtjWCyGxGRLwag\nEDApMubnZflkwVWd8YwHmZjtRkTkg0kIIWAxSUi1RetZcJoA3JqGl/92GA/fmwMFgtluREQtMACF\ngNMtUFPbhN/877+80qwfmHw9AAHVyfBDRNQSu+BCwK1qevABLthqQRVwujgARETkDwNQCAgBv1lw\nQgiudkBEFAADUAjIAZbaEUIgyswqJiLyh1fHkBAomD7UKwtu3pQsrN9ciiYX5wAREfnDJIQQUGQZ\nCbEWzJ40APExZtQ2uPDHtw7jyNd2zJokWMlERH7w2hgCkgS43QLPv/GJ11hQmi0aisQxICIif9gF\nFwIut8ALJaWYN8V7MurCGTmAJDxbMigyhCLDLUkQ5/5NRBTJ2AIKAU0TqKl1QgjhmYwqS6g604j4\nWAu+PnkWvbolwl7b5LMdgy3GzEVJiShi8TY8BKLMMu65PRNP/ekA8h/fhkXP7gQAmGQJxVs+h0v1\nXS172YbdcBtZaCIigzEAhYCqCawq3u8VYFYV74eqCdhrHdA0ze88IVVjhhwRRS4GoBBwq/4DjFsV\nWHr/DVAUmVtyExG1wAAUAoocOMDcV7QV61476DNPqHlLbv09mKRARBGGSQghYLHIKJyRg6ILkgwK\nZ+TAYvEEkV2HKgAAi2cPR12jE10So2GC0BMQFEWGvcHFJAUiiijtcpu9YsUKjB49Gn379sXnn3+u\nP3706FFMmTIFubm5mDJlCr766qs2PdZWnC4NxVs+w+xJA1CUPwKzJw1A8ZbP4HRpGDv0CgCeIHS2\nvgkPPb0dqua9JbcbYJICEUWcdglAY8aMwcsvv4zu3bt7Pb548WJMnToV77zzDqZOnYpFixa16bG2\nomoCuw5VYNmG3ShcswPLNuzGrkMV0DSBu3L7ou+VNqTZolHb4PI79qNq/rf0ZpICEXVm7RKAsrOz\nkZGR4fVYVVUVPv30U0yYMAEAMGHCBHz66aeorq5uk2NtSQmwGKksSxAakDeuDwqm52DrnmM+Yz8X\nez2TFIioMzNsDKi8vBxdu3aFoigAAEVRkJaWhvLycgghQn4sOTk5qPKlpMS1+rn2WgcKpg/F8hf3\n6GM4BdOHwmyS4XKrSE+OhdVqQv4PByEpLgpyi8CiaQK/nDkMj/5hl/76X84chi5JMT7PDVepqfFG\nF6FDYr34x3rx1RnrhEkIAVRV1UFrZReYUBRsfPeI12KkG989gjl3DoDTpSLKokBTBVxCRU2N6jex\nINFqwvL8kXBrArIMmBQJNTUNnSIJITU1HqdO1RpdjA6H9eIf68VXONSJLEtB3bgDBgagjIwMVFRU\nQFVVKIoCVVVRWVmJjIwMCCFCfqwtqcIzBtSc7dbs3jv6w17bBABYVbwfBdOHwpIQBQTYoftsg5OZ\ncEQUMQybbJKSkoLMzEyUlJQAAEpKSpCZmYnk5OQ2OdaWTIHGgCRg76cnkZIYjUp7I5a/uAdu1X+r\niplwRBRpJCFEm6daPfroo9iyZQtOnz4Nm82GpKQkvPnmmygrK0NBQQHOnj2LhIQErFixAr169QKA\nNjkWjGC64GSLggaHGxXVDUiKi0KUxQRZllB9xoEuSdHQhMD9y7cCANYVjkWU7Ak4qubZstsEoEkT\nmFu01ee91xWOgantf6I2FQ7dB0ZgvfjHevEVDnVyKV1w7RKAwlFwAciEmtomvLLlM9xxY288uXG/\nVzdafKwZj7/0L9hrHVj5sxv9roydFG/B/zy13Wc/oaL8EZDCvAsuHE4eI7Be/GO9+AqHOrmUAMT1\nXkJA0wRe2fIZ7hnfTw8+wPlutIqqRuSN64P5eVlQNf8rY2sqsHBGzkWX6yEi6kx4fQsBAeBHY/tA\nluB3QqkkAd1T4/Cn9z7Hj8f28b9wqabBFmNGUf4Ir665C5fradltx+QEIgpnDEAhoMgSmpwqauud\nSLNF+3SjOZxunK5x4NZhV+oLl/ps3S1LUFUNEs79KKrQk+W4VhwRdUbsggsBVfXsB1S85XO/23J3\nTYlBWnI0bPFWlGwv8/uci90JMEOOiDojtoBCQNUEbPFWTB79HURZFCyePRxOlxvxsVGwRsl4auMB\n7DpUgTRbNOZNycLf/3UcsycNQGKcxWdl7EDvH2itOP6ARBSueP0KAcu5Lbmbd0VNs0Vjfl4WBICa\nWoEJI3th+u39IMsSztQ5cEv2FXh4rWfb7nWFYyB9SyJi81px/rrtEGBeERFRR8cuuBDQAmzJXd/o\nQnyMBU/96QDyH9+GRc/uhFsVsMVH4bEHbsCwfl1bteCoCcyQI6LOh9ewEGjugrtwLbhN276Axayg\n6ozDJzAtyBsMkyJh1sT+0IRn0VRFkdDk0iDLgKJIkABIqqdrTlUvniFHRBSOGIBCwGLy3wWXkmhF\nRXUDAKDvlTZMHv0dxMeYkWqLhqoJ/PKZnV7Pf+nNw7DXOjA/LwvWKAXRFhNizYoehPxlyLXEdG0i\nChfsggsBNUAXnCxJiI02o++VNkwbn4nn3/gEhWt2YOGaHahvdMEWb/V6/uTR39H//2ydExXVDRCK\nBKHIcEue/ypK4J+sOV27cM0OzC3aisI1O2BvcF30NRQ6iiK3+rciIraAQiJQlppbE4ixmjD99uvw\n++J9+nNs8VacqWvC/Lws/KeyDpu2fYEjX9vRIy0ORfkjUNvgQlJcFACgprb1K2QHStcuyh+BzrGr\nUMfFuVpEweMtWgjIkv/VsIUmUNfgRteU8xlsza2htZsOIv/xbXj+jU8wbXwmhvXriorqehSu2YHn\n3/gEAkBsjCWo+T+dYWvvcG1FcK4WUfDC4+zu4EwmCQXTvbPUCqYPhSQBG9/9DMD5ADV59Hd81ot7\ncuN+3DuhP4q3fK4/tvzFPQGX9gkUUBRZwrB+XbFwRg6K8kdg4YycVmfadQTh3IXYGYI/UXtjF1wI\nuFWBje9+5rMj6m3De2LM0KvgcmsonJGDog27ER9j9nuhqm104sjXdq/HNIGg5v9YFAl5465F0QXd\nQIUzcmBRJLgCZS10IOHchci5WkTBYwAKAVXzvyPqnaOuQYxVQvnpOsRYTVg8ezgUxf+FyuXSPFs3\nnAtgW/ccgyJLWDgjx2dcwQT/m6o6VaEHH8BzAS86dwE3XWJ2XHtm1YXzig/Nc7Va+1sREQNQSDSP\nAflbhLRbahz+sPkQZky4Dmfrm7Ch5FPMm5LlvWfQvZ7Ao9RIOFPnxNY9x5A37lps2/M1vvxPDR69\n/waYFBlCAJoQcAEwWRR9nlCzQBdwTQPsjuAHyNt7YD2cWxGcq0UUvI7fuR4GzIrks1LB/LwspCXH\nQBMC9loHHE43HE43jnxtx18/LMOv77sBzy0ci6L8kbCYZBSu3oGHnt6O59/4BHfc2BvFWz7DDQO7\nY9ehCqzfXIraBicK12zHnGXvYeGaHfjmVD3qXarX+IgSaGtwWbqkAfL2HlgP9xUfVFWDpGowCQHp\n3NwtIgqMO6IGEMyOqCarCZoGnDhVj6T4KMiyhKozjdj8jzLMuXMAKqobEBNlgtOtIj4mChXV9bCY\nFaQmReObU3VYu+mgz13/7EkD0DMjAQ6XG4ok46W3Dnl18aXZorEgbzC6JkfrO6YGarHEx5ox69H3\nfMr9bdt9uyUpJNuEB7ObYyRNpA2HXS6NwHrxFQ51cik7oobLzWWH5nJ7usIWrt3hc2zWxAFITYrB\n33b+G+O+ezXqGp1wuQVsCWYosgyrxeS32ywxzoJvTtXCbFLw1w/LcOeoazDxpt6QJUlf6seWEIUL\nR+cv7AbShKdrEJIAhITvj+qFzKu7+IwxXaxry4gusdau+EBE4Y8BKARMigQhAlysFc9Gc9+74Wqs\n31yqb8uwcEYOzGYJtnir39clxEbhD5sPwV7rwOxJA/C7V/bhgckD8cjzu/QuvsYmNyymKK+yqKoG\ni0XB6VqnnpAwrF/XS8qO48D6t4ukFhtRqLELLoBguuCEoqBkexluzOqB5S/u8brIx8easfKlf8Fe\n69D3Asrpl4HEOAvioi14b/dXuGnwFV7B4aF7huJvO4/ivT3H0fdKGx6cOhj2sw50SYrGxneP4L09\nx5Fmi8ay/BGQJAkW2ZMB13wRlCQJBau360Ft4YwcPP/GJz5Brih/hN59F4jZosCpAqqmQZFlWBTA\n5Qwu/IRD98GlCCZJw1+gSk6O7ZT1crna++8lHG4iwuEcYhecQVRNw6dH7fjugG5Yev8NUGQJmgaY\nzRKcLg2TR38Hyzbsxl8/LMOPx/bFipfOB6mC6TkQQuCx/BFwuTRUVNfjbzuPIqdfBm4fcTUEgEXP\n7vQKarnDe8Lt1uBWBV746yfIG3ctird8preult53g1ewaZ57dOGCqLUNLq/uO38nIQBUBbEUUDic\nyKHU2nlLgQJVUpLQj0dSvXUk/n6bh2fkIDHeAqdL4+/RxpQlS5YsMboQHVFjoxOtbRuaLCb0790F\ntQ1OuNwaqs86oCgyFFmGBAlmk4R3PjqG6bf3w1N/Or8KQr3Djf1HKjE25ypIABqb3IiLsaBbahzW\nbvoY1/ZM8Vo1ofn513+nC3717D+x+9BJ3DP+Orz6/heYPWkAbsrqjqHXdUV0lBm7Pz2JeocnX21Y\nvww4XW78aGwfrN30MTZ/+G8cOVaNYf0yEBNlgixLsDe4sOS5j1D87uf4qLQcQ/tlwGo1YdG6f3p9\n/r4jlRg99EqfTfSaT+Qlz32E/UdO4Yr0eERHW6AoCqKjTGhsdIbmh+lAVADF737u9Vi9w40JI6/2\nSi/VZAlLnvvIpx5HDe4BScBv3cdEmRCpnROxsVFoaGifv5dAv801VyThF09+2GF+j/ask0slSRJi\nYixBvYZp2CEgSYAmNMRYTbAlWJGeHItYqwnRUQoUBUiIi8K6wjHo3T1RXwG7WaW9ERKAJ/5vH5a/\nuAdut4bN/yhDpb0x4KoJVotJ//8nN+7HmKFXofqsA8+/XgoAeKGkFPOmZOnpzFv3HMOsif19lgBq\nTqkOdCevqvBbXn/LyzS/hy3eqq91d9/yrShYsx3HTp5tt+V02nMtuUBp7y2XPgo0P8vl1lqd6h6u\na+R1dIF+mwvPMa7p13bYBRfy2YGBAAAYUElEQVQCzTdGblV4dZctvDcHblXD4y/t9epCa3C48NKb\nh3HkazvSbNGw1zZh2vhM/PGtw1j+4h7MnjQAuw5VoLbB5TdBobbBpf+7OWPuTJ0Tk0d/R98WoqbW\nidmTBiAxzoIuidHQxMXXKvO3od7pM4245/ZMvazNn+8vC675RJ49aYBPoHv0D7vaZTmd9p4429ok\njUDZhGaTDEeT+1tXf+BK220n0G/T8hwLh9U4whG74AIIpgtOhYT6RiccThU3DuqOYf0ycPTEWby7\n6xgGXtMFb+38CsD5LrTBfdOQ1TcN9Y1OzLyjPzQh8OTG/bjvBwMwoHcqruwah4HfScWx8jP40Zg+\nOPB5Jeodbj37rXjLEVSdcQDwnCzjR1wNp0tFty6x6N3DhsrqBhz52o4PD3yD4ydrMWxAOgBg8LVp\nOHm6weu1t+ZcCbNJRq/uiV7dc/f9YAA04dnn6N47rsMH+77RL3yxZsWnO0JSZHxUWo5Rg3tg84f/\n9jrmr1uqLQTqTvHXZRgKsizBajXhluwrMGFkL4zLuQoxZtknKJhkCUP7ZWDfkfO/48IZOUhPiYWj\nyY2PSsv17lLg/O/SXOb2/l5Ga8/uJn+/jb9z7MLfwwidtQuOQT0EJAkwm0xYu2mffoc6b0oW/vjW\nYST56cKKi7Fg5R/3Ysmc4bDXOiBLEgZe0wWxVrPXrqoF04fiw/3/0VsyKYlWOF0q7LXnT4yFM3LQ\n4HDjqT8d8MmiA4Afju6DmtomfYmf5haNvdaBh+89d7euwu+Gej/78SBU2hvRIy0e6wrHwGKWoalA\nk6ZBUWSvwdnm1oC91mHYcjrtuZbcxVolPuUKsEyPfO6/39aKCuc18jq6lr+NSZbhcLl9zjFOPWgb\n/PsNkeUvevfjP7lxPx6YPBDxMWasKxyDUzWN+oU/Ltqib0pnUmQ4XSp+OLoPFq3b6fUezd1xyzbs\nRt8rbbjn9kxs/keZHpBs8VEwmWUUPLXd63UrXtqDZfkjIARQfdahB587buyNzf8ow4NTB6P8dB0S\n4ixQnSpUSfLfDx5l8uxrJASsUQqqzzQF7AZqPpG7JFnx6P03wH5B0Pt/t2V+6wkcbCaYv+cLBLd6\n+OUIduXuQBNsW7OGXDivkRcOvH8bFbFmhWv6tRMGoBDQAtyhZnSJw8o/euYAzc/Lwtwf9IfFpGBD\nSSnyxvVBcoIViiKhrtGJM/VNft/DluCZaHrh+E7zkjxptmgsmTPc53W2eCtq610oevF8sJg3JQt/\n/bAMY4ZeBftZBx55fhfWFY49dycu+73AxVrN+J97snG6xoGUJCvstQ7Y4q2otDf6veDKioRTNQ6v\nOU0LZ+SgR2ocamoaAtZfsGMcgZ6fEm9pt4mzoWyVfNvqD5wQ3L4icTUOo6YCMACFgHzuDtUWb9Xn\n2TicbphNkj54v6rY0yK6oms8dh2qwIwJ/fDE/+2DvdaBhTM8yQr+gkBMlKdLJ1BGnOzn7jhvXB89\n+DQ/78mN+/WW05k6J9Js0TDJkucKJgk8eNdg/O6V812ID941GGazDFedpm8nfmHX4pGv7foF12JR\noKlAo0ugxk+QWv6TkXrZ/P2hB9uauNjzg12R+lJPvPZslXClbWpLRia5MACFSOH0oWhocnuN4Sy8\nNwdjh16B9/Yc11M7NQ1YPHsYAE+rZtO2L7Bsw278z7RsFEwf6rOSgiR7LmYOpxvD+nXFmKFXea3n\nVnWmEQ/dM9Rrcmt6SqzfiacZXWIQZVYACCyZ+10oEIAiQ8gyLBYZD0weCKvFBIfTDYtFhtCgByXA\nO5At27AbabZonKrxHLvwe7cMUu5zrYJAf+gJsf6Da6DWxMVaH5IQrb57vZwTr71bJZF4V07tw8iN\nIBmAQkCWgdhoM4rOBQ/g3I/4wm48MvcGHK+o07dkkGVJX/36wot1QqwF6zeXeqVCF2/5DHPvHIjf\nLbgJCXFRmHLrtfpYU3OSwls7PMkGv557A87Ue8Zd7GebMKxfV9xxY2/vfYdm5ECxSvjty/uwcEYO\n4qLNsJ9pQr3DrbdymqXZovHo/Tf4vdDHx5j1ZIcGh0tPgGg+3jJImWQJ0AL/oS/LHxFUayJUrY/L\nOfHYKqHOwsgkF85mCwGhAfZa/2M4Z+ubkDeuj2cspGs8JOn85M7mi3XeuD6QZQm7DlVg2YbdKFyz\nA8s27MauQxU4fabRk6IrhE+iw/IX9yCnXwYOfnkadQ4nVFWgZ0YCzGY54MRTCAkP3jX4XEDU8NiG\n3ZAk+C074H+ipS3BM2dIlgCTIl80SBXOyNG/b6A/9DN1zqD2AQrVvkEXO/Fa9Xru/0OdQGsnVLcF\ntoBCQNWEPq7S8q48JdGKLknRUIWGo9+cRWKcBQvuGoxN2z7Xu+a6dYlD1ZlGv+NIblXDkxv3Y1n+\nCL/dar26J2Dx7OFwqSoSkizQoKHJqULE+A8qLlVD4Zod57ub4q0BJ7yaFN8twedNycLvzo1dzZ40\nQH9uy9em2qI9m+0pgMnkuc8J1HKpOuNA7x4JrW5NhKr1Ee7ZZZGwhlwkfEejGZnkwgAUAiZFwtY9\nx3y32p6Rg+ItR3Dwy9NYOCMHW/cc0xcMfeieoXrXnEmR8YfNn+J/7slGk1P1Gk958K7BsMVb4VZF\nwG61Vy5YiLRg+lD89cMy3DO+n9+Lq3ruwtrcInpg8kAUb/ncb9kVIWCLMWN5/ki4VA0nTtfhj295\nUskLZ+SgeMtnqKl1Yn5elvfY14wcmAGoquq13YO/P/Tm7Lze3Qd4WhNAq8Y4QjEmEs7ZZZGwOkIk\nfMeOwMjuZG7HEEBwO6KaUXXGgeItn2HM0KuQGGdBQmwUtnx0FEMy0/WLdvO4COAJBg9MHoikOCsS\n4y149tWDmDCyl9d4yoXPi4+xICHWgl8+s9PneMv3nT1pAHYfKsf4EVd7JTU0jxm9t+e4/vq1D43G\n4nX/hC3eirxxfdCtSxzMigwFwnsuSos7UYsi6VtANE9QdWv+Vw++cCl5s0VBo0vzmid017hrW72F\nQahPCs8aaxLcqoB2biJiy+/eVi5niX2hyChcs8Pnb6E1W2x0dM310pm/Y7C4HQMF5HJr+PTfpzH3\nzoFQNQ2SJOHv/zqO1z74N3YcLNcDRPwFs+Qr7Y1IT47F74v36y2KQGMxGV3i4FZVCPg/3vJ9E+Ms\nepBZPHs4FEWCWZFRsr3MK/h4utlkLM8f2SJ4qD4tgJYtDpcK/d/auf2BWtMacTlVRCkyuiRaYYuP\nwtw7B/gNLO1591sTxJYTHUUkrI4QCd8x0jEJIQQUWULm1SkoXLMdc4u24uG1OzD0unT8v9y+XgPy\nFy5wmGaLxvHKOj1VuWjDbsRHW/wOBsoS8Ovnd8HtFn6Pt3xfW3wU0mzReG/PcTzy/D9R1+jE2fom\nZF+X7jVw//C9ObCaJEBV23UgvTWD961dJfpytdfnhJqRA8ftJRK+Y6RjAAoBVRV6Vxdw/iI2anAP\nDOvXFQ6nWx8DAqCPfWza9oX+HpX2RtTUNaFgund21/y8LJyq8UzqdLrcmJ+X5XW8sMX7Fs7IQXSU\nCQ9MHoii/BGYPWkAGh1uFL24By+9eRizJw1AUf4IPDB5IBLjLEHvbtpeLjdDraN9TqiFKhOwI4uE\n7xjpOu1vefToURQUFKCmpgZJSUlYsWIFevbs2SafpWqa34uYvbYJsyb2R5RZwfaPv8G9E/p70pdl\nCeteO6ivkgB4Z4NdOCE0yqLguddKkWaLRnyMBYnxUVh63w0QEFBVYOfBb3DP+H64947+MCueLbM1\nlwpbvFW/s1/x05FeKxM0W1c4psP+AbRXhlq4ZsJFwjykSPiOka7TtoAWL16MqVOn4p133sHUqVOx\naNGiNvssRZH9dhWcqXNCAFiz6WNk9e2KOocTDqcbkgTcNe5arzu7eVOysHXPMbhVAZMiIyneswbc\nc6+V6mvJ1dQ3YcWLe3Hf8q0QAnhy4368/M4R/GTlNty/fCtUTYPLqXqduOsKx6BLYnTYdWW0191v\nON9lR8I8pEj4jpGsU2bBVVVVITc3F7t27YKiKFBVFcOGDcOWLVuQnJzcyvdofRacEmWC/WyT3/Ti\nCSN74VfP/hPrCsdg/eZS3DuhP6KiZMiShEaHqq9esHXPMUy8qTcAz7I2S+Z8F1aLCW5VQAiB02ca\nvTaxW5A3GAvX7tDLcLHsIKPTWS81g6e95oAYNdckHDKbjMB68RUOdcIsuHPKy8vRtWtXKIoCAFAU\nBWlpaSgvL291AAqmIk/ZG3Do36e9tiH464dluGvctVi76SDSbNFwqwJ3jbsWsuwZM9q8vQxjcq5E\ng8OF+BgzbhveE7b4KERZFKycdxOS4qIgyxI0TeDYybNe82x+OXMYzKbzK1g3P9YlKQZygFZNUpLA\nb+bfBJdbg9kkIzE2KuBz20Jqany7fVY4Yb34x3rx1RnrpFMGoFAIpgUUE2NGv15dsH5zqT4PaNbE\n/vhg33/01a5jrApirAqaXCrqGl0YNfgKbN39Ncbm9ISiSDApEqwWxbM/T5MLVU3nM9sSrSaffnAA\nPo9VVdV9a1klAG63iiqH61ufGyrhcPdmBNaLf6wXX+FQJ2wBnZORkYGKigqoqqp3wVVWViIjI6NN\nPs+tCaTbrJj7/YFQVQFFkWAxyRg1+AqMzr4S5xpiAABZkpEQHYUoi4wJI3tD1TQosowoswRno/+g\nEGjWP1dHJqJw1imTEFJSUpCZmYmSkhIAQElJCTIzM1vd/RYsp8MNtyY8W3LCk1nlcnvGECTJM7ag\nqgKSKqBoGqCqaGp0QdLn36hwOjr6zBMiotDqlC0gAFiyZAkKCgqwZs0aJCQkYMWKFW36eU6HGxKA\njAuayiYAcENf2p+tFCKi8zptAOrduzf+/Oc/G10MIiIKoFN2wRERUcfHAERERIZgACIiIkN02jGg\ny3U5kzTbc4JnuGCd+Md68Y/14quj18mllK9TLsVDREQdH7vgiIjIEAxARERkCAYgIiIyBAMQEREZ\nggGIiIgMwQBERESGYAAiIiJDMAAREZEhGICIiMgQDEAhcvToUUyZMgW5ubmYMmUKvvrqK6OL1C7s\ndjvmzJmD3Nxc3HHHHfjpT3+K6upqAMCBAwcwceJE5ObmYubMmaiqqtJfd7FjncnTTz+Nvn374vPP\nPwfAOmlqasLixYsxbtw43HHHHfjVr34F4OLnTyScW++//z7uvPNOTJo0CRMnTsSWLVsAREC9CAqJ\nadOmiddff10IIcTrr78upk2bZnCJ2ofdbhcfffSR/u/ly5eLwsJCoaqqGDt2rNizZ48QQojVq1eL\ngoICIYS46LHOpLS0VMyaNUvccsst4siRI6wTIcTSpUvFY489JjRNE0IIcerUKSHExc+fzn5uaZom\nsrOzxZEjR4QQQhw+fFgMGjRIqKra6euFASgETp8+LYYMGSLcbrcQQgi32y2GDBkiqqqqDC5Z+3v7\n7bfF9OnTxccffyxuv/12/fGqqioxaNAgIYS46LHOoqmpSfz4xz8Wx48f1wNQpNdJXV2dGDJkiKir\nq/N6/GLnTyScW5qmiZycHLF3714hhBC7d+8W48aNi4h64WrYIVBeXo6uXbtCURQAgKIoSEtLQ3l5\nOZKTkw0uXfvRNA2vvPIKRo8ejfLycnTr1k0/lpycDE3TUFNTc9FjSUlJRhQ95FatWoWJEyeiR48e\n+mORXifHjx9HUlISnn76aezatQuxsbGYP38+rFZrwPNHCNHpzy1JkvD73/8e+fn5iImJQX19Pdat\nW3fR60pnqReOAVHILF26FDExMbj77ruNLoqh9u/fj9LSUkydOtXoonQoqqri+PHjuO666/Dqq6/i\nF7/4BX72s5+hoaHB6KIZyu1249lnn8WaNWvw/vvvY+3atViwYEFE1AtbQCGQkZGBiooKqKoKRVGg\nqioqKyuRkZFhdNHazYoVK3Ds2DE888wzkGUZGRkZOHHihH68uroasiwjKSnposc6gz179qCsrAxj\nxowBAJw8eRKzZs3CtGnTIrZOAM95YjKZMGHCBADA9ddfD5vNBqvVGvD8EUJ0+nPr8OHDqKysxJAh\nQwAAQ4YMQXR0NKKiojp9vbAFFAIpKSnIzMxESUkJAKCkpASZmZlh1RS+HE888QRKS0uxevVqWCwW\nAED//v3hcDiwd+9eAEBxcTFuu+22bz3WGcydOxfbt2/Htm3bsG3bNqSnp2P9+vWYPXt2xNYJ4OlW\nHDZsGHbs2AHAk8VVVVWFnj17Bjx/IuHcSk9Px8mTJ/Hvf/8bAFBWVoaqqipcddVVnb5euCFdiJSV\nlaGgoABnz55FQkICVqxYgV69ehldrDb3xRdfYMKECejZsyesVisAoEePHli9ejX27duHxYsXo6mp\nCd27d8fKlSvRpUsXALjosc5m9OjReOaZZ9CnT5+Ir5Pjx49j4cKFqKmpgclkwoIFCzBq1KiLnj+R\ncG5t3rwZzz33HCTJs6vovHnzMHbs2E5fLwxARERkCHbBERGRIRiAiIjIEAxARERkCAYgIiIyBAMQ\nEREZggGIiIgMwQBEdIleffVV3HXXXUYX47IUFBTgd7/7ndHFoAjFAERERIZgACJqBVVVO/T7dQSd\n8TtR22IAoohWVlaGadOmITs7G7fffju2bt0KwNM1tXjxYsyZMweDBg3Crl27YLfbcf/992Pw4MH4\n4Q9/iK+//trnve69917k5OQgNzcXb731ln7M3/sFUlBQgEceeQRz585FVlYWfvSjH+mf9Z///Ad9\n+/aF2+3Wnz9t2jT8+c9/BuDpFszLy8OyZcuQnZ2NMWPGYN++fXj11VcxatQoDB8+HK+99prX59nt\ndtx7773IysrC3XffjW+++eaSv9MHH3yA8ePHIysrCzfeeCPWr18f7E9CkcS4rYiIjOV0OsXYsWPF\n2rVrRVNTk9i5c6cYNGiQKCsrEw899JAYPHiw2Lt3r1BVVTgcDrFgwQIxb948UV9fL44cOSJGjhwp\n8vLyhBBC1NfXi5tuukn85S9/ES6XSxw6dEjk5OSIL774Qggh/L5fIA899JDIyckRH3/8sXC5XOLn\nP/+5WLBggRBCiOPHj4s+ffoIl8ulP//uu+8Wf/rTn4QQQmzatElkZmaKv/zlL8LtdosnnnhCjBo1\nSixZskQ0NTWJDz/8UAwaNEjfFO6hhx4SgwYNErt37xZNTU1i6dKll/WdRowYoe/qWlNTI0pLS0P5\nk1EnwxYQRayPP/4YDQ0NmDt3LiwWC4YPH45bbrkFb775JgBgzJgxGDJkCGRZhslkwpYtWzBv3jzE\nxMSgT58++P73v6+/19///nd0794dkydPhslkwnXXXYfc3Fy8/fbb+nMufL+oqKiLlm3s2LEYOHAg\nTCYTJk6ciMOHD7f6e/Xo0QOTJ0+GoigYP348ysvL8ZOf/AQWiwUjR46ExWLxar3dfPPNGDp0KCwW\nCx588EEcOHAA5eXll/SdTCYTvvzyS9TV1SExMRH9+vVrdbkp8nA/IIpYlZWVSE9Phyyfvw/r1q0b\nKioqAMBrb5Xq6mq43W6vxy7cwfSbb77BwYMHkZ2drT+mqiomTpyo/zuYvVouXAXbarUGtTlZSkqK\n12tbvl9UVBTq6+v1f6enp+v/Hxsbi8TERFRWVl7Sd3ryySexdu1a/Pa3v0Xfvn3xX//1X8jKymp1\n2SmyMABRxEpLS8PJkyehaZoehMrLy9GzZ0+vcRDAs5eNyWRCeXk5evfurT+3WUZGBoYOHYoXXnih\nTcscExMDAHA4HIiLiwMAnDp16rLe8+TJk/r/19fX48yZM0hLS7uk7zRw4ECsXbsWLpcLL7/8MhYs\nWIAPPvjgsspHnRe74ChiDRw4EFarFc8//zxcLhd27dqFbdu2Yfz48T7PVRQFt956K55++mk0Njbi\nyy+/9BrMv/nmm/HVV1/h9ddfh8vlgsvlwsGDB1FWVhbSMicnJ6Nr16544403oKoq/vKXv+D48eOX\n9Z4ffPAB9u7dC6fTiVWrVuH6669HRkZG0N/J6XRi8+bNqK2thdlsRmxsrFfrkqgl/nVQxLJYLHjm\nmWfwj3/8A9/97nfxyCOP4PHHH9dbOC0tWrQIDQ0NGDFiBAoKCvCDH/xAPxYXF4f169fjrbfewo03\n3oiRI0fiN7/5DZxOZ8jLvXTpUqxfvx7Dhg3Dl19+edldXBMmTMDq1asxbNgwHDp0CCtXrgRwad/p\njTfewOjRozF48GAUFxfr70XkDzekIyIiQ7AFREREhmASApEBbr/9dpw4ccLn8UceecQry4yoM2MX\nHBERGYJdcEREZAgGICIiMgQDEBERGYIBiIiIDMEAREREhvj/BXHrtIGvkscAAAAASUVORK5CYII=\n",
            "text/plain": [
              "<Figure size 432x288 with 1 Axes>"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jfx_HDGjktEf",
        "colab_type": "text"
      },
      "source": [
        "Q2) Given an Order Table with the schema(id, user_id, total, created). \n",
        "\n",
        "Write a SQL Query to create a retention plot. The format for the raw data and output are given. \n",
        "\n",
        "Week Start Date is the 1st Week in which the User_Id Placed the order,  Week 0 is Unique User ids who placed their 1st Order in this week.\n",
        "\n",
        "Out of those ids, Week 1 is unique users who placed an order in 1st Week + 1, Then Week 2 is 1st Week + 2 and so on till Week 10.\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TIFWhMlekvBN",
        "colab_type": "code",
        "outputId": "ef294eda-1c3c-438d-fd81-f9f6374a1487",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 71
        }
      },
      "source": [
        "db2 = sqlite3.connect(':memory:')\n",
        "dfs = pd.read_excel('Q2 (3).xlsx', sheet_name=None)\n",
        "for table, df2 in dfs.items():\n",
        "    df2.to_sql(table, db2)"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py:2531: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
            "  dtype=dtype, method=method)\n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ibQ9uHP1lUKt",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def run_query2(query):\n",
        "  return pd.read_sql_query(query,db2)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ho71v63olanP",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "70807789-9dbd-4916-c0d1-2ce333303efa"
      },
      "source": [
        "query = 'select * from Sheet1 limit 5;'\n",
        "run_query2(query)"
      ],
      "execution_count": 85,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>index</th>\n",
              "      <th>id</th>\n",
              "      <th>userId</th>\n",
              "      <th>total</th>\n",
              "      <th>created</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>194</td>\n",
              "      <td>330</td>\n",
              "      <td>4760</td>\n",
              "      <td>2017-03-01 14:22:30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>201</td>\n",
              "      <td>142</td>\n",
              "      <td>10395</td>\n",
              "      <td>2017-03-01 16:41:53</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>208</td>\n",
              "      <td>324</td>\n",
              "      <td>10395</td>\n",
              "      <td>2017-03-01 21:51:09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>215</td>\n",
              "      <td>258</td>\n",
              "      <td>4165</td>\n",
              "      <td>2017-03-02 08:22:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>222</td>\n",
              "      <td>232</td>\n",
              "      <td>2940</td>\n",
              "      <td>2017-03-02 11:51:57</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   index   id  userId  total              created\n",
              "0      0  194     330   4760  2017-03-01 14:22:30\n",
              "1      1  201     142  10395  2017-03-01 16:41:53\n",
              "2      2  208     324  10395  2017-03-01 21:51:09\n",
              "3      3  215     258   4165  2017-03-02 08:22:00\n",
              "4      4  222     232   2940  2017-03-02 11:51:57"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 85
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XTn84FSRzae5",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1969
        },
        "outputId": "ccbec3bb-c66c-4daf-ecde-880550fa1a34"
      },
      "source": [
        "query = '''SELECT id, userId,\n",
        "  DATE(created) AS created_at\n",
        "FROM Sheet1 ;'''\n",
        "run_query2(query)\n"
      ],
      "execution_count": 97,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>userId</th>\n",
              "      <th>created_at</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>194</td>\n",
              "      <td>330</td>\n",
              "      <td>2017-03-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>201</td>\n",
              "      <td>142</td>\n",
              "      <td>2017-03-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>208</td>\n",
              "      <td>324</td>\n",
              "      <td>2017-03-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>215</td>\n",
              "      <td>258</td>\n",
              "      <td>2017-03-02</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>222</td>\n",
              "      <td>232</td>\n",
              "      <td>2017-03-02</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>229</td>\n",
              "      <td>157</td>\n",
              "      <td>2017-03-02</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>236</td>\n",
              "      <td>189</td>\n",
              "      <td>2017-03-02</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>243</td>\n",
              "      <td>57</td>\n",
              "      <td>2017-03-03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>250</td>\n",
              "      <td>257</td>\n",
              "      <td>2017-03-03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>257</td>\n",
              "      <td>95</td>\n",
              "      <td>2017-03-03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>264</td>\n",
              "      <td>293</td>\n",
              "      <td>2017-03-03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>271</td>\n",
              "      <td>309</td>\n",
              "      <td>2017-03-03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>278</td>\n",
              "      <td>33</td>\n",
              "      <td>2017-03-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>285</td>\n",
              "      <td>63</td>\n",
              "      <td>2017-03-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>292</td>\n",
              "      <td>89</td>\n",
              "      <td>2017-03-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>299</td>\n",
              "      <td>211</td>\n",
              "      <td>2017-03-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>306</td>\n",
              "      <td>31</td>\n",
              "      <td>2017-03-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>313</td>\n",
              "      <td>257</td>\n",
              "      <td>2017-03-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>320</td>\n",
              "      <td>69</td>\n",
              "      <td>2017-03-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>327</td>\n",
              "      <td>530</td>\n",
              "      <td>2017-03-05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>334</td>\n",
              "      <td>540</td>\n",
              "      <td>2017-03-06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>341</td>\n",
              "      <td>156</td>\n",
              "      <td>2017-03-06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>348</td>\n",
              "      <td>366</td>\n",
              "      <td>2017-03-06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>355</td>\n",
              "      <td>111</td>\n",
              "      <td>2017-03-06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>362</td>\n",
              "      <td>525</td>\n",
              "      <td>2017-03-06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>369</td>\n",
              "      <td>207</td>\n",
              "      <td>2017-03-06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>26</th>\n",
              "      <td>376</td>\n",
              "      <td>83</td>\n",
              "      <td>2017-03-06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27</th>\n",
              "      <td>383</td>\n",
              "      <td>305</td>\n",
              "      <td>2017-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28</th>\n",
              "      <td>390</td>\n",
              "      <td>579</td>\n",
              "      <td>2017-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29</th>\n",
              "      <td>397</td>\n",
              "      <td>83</td>\n",
              "      <td>2017-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39910</th>\n",
              "      <td>279564</td>\n",
              "      <td>211648</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39911</th>\n",
              "      <td>279571</td>\n",
              "      <td>56975</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39912</th>\n",
              "      <td>279578</td>\n",
              "      <td>87255</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39913</th>\n",
              "      <td>279585</td>\n",
              "      <td>167123</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39914</th>\n",
              "      <td>279592</td>\n",
              "      <td>206981</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39915</th>\n",
              "      <td>279599</td>\n",
              "      <td>213074</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39916</th>\n",
              "      <td>279606</td>\n",
              "      <td>155843</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39917</th>\n",
              "      <td>279613</td>\n",
              "      <td>103411</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39918</th>\n",
              "      <td>279620</td>\n",
              "      <td>120031</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39919</th>\n",
              "      <td>279627</td>\n",
              "      <td>138518</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39920</th>\n",
              "      <td>279634</td>\n",
              "      <td>223565</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39921</th>\n",
              "      <td>279641</td>\n",
              "      <td>176593</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39922</th>\n",
              "      <td>279648</td>\n",
              "      <td>20544</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39923</th>\n",
              "      <td>279655</td>\n",
              "      <td>168974</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39924</th>\n",
              "      <td>279662</td>\n",
              "      <td>206020</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39925</th>\n",
              "      <td>279669</td>\n",
              "      <td>100654</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39926</th>\n",
              "      <td>279676</td>\n",
              "      <td>183554</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39927</th>\n",
              "      <td>279683</td>\n",
              "      <td>179156</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39928</th>\n",
              "      <td>279690</td>\n",
              "      <td>157925</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39929</th>\n",
              "      <td>279697</td>\n",
              "      <td>221650</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39930</th>\n",
              "      <td>279704</td>\n",
              "      <td>127693</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39931</th>\n",
              "      <td>279711</td>\n",
              "      <td>175197</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39932</th>\n",
              "      <td>279718</td>\n",
              "      <td>204111</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39933</th>\n",
              "      <td>279725</td>\n",
              "      <td>186283</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39934</th>\n",
              "      <td>279732</td>\n",
              "      <td>167592</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39935</th>\n",
              "      <td>279739</td>\n",
              "      <td>95413</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39936</th>\n",
              "      <td>279746</td>\n",
              "      <td>196386</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39937</th>\n",
              "      <td>279753</td>\n",
              "      <td>172328</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39938</th>\n",
              "      <td>279760</td>\n",
              "      <td>181371</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39939</th>\n",
              "      <td>279767</td>\n",
              "      <td>217279</td>\n",
              "      <td>2018-03-07</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>39940 rows × 3 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "           id  userId  created_at\n",
              "0         194     330  2017-03-01\n",
              "1         201     142  2017-03-01\n",
              "2         208     324  2017-03-01\n",
              "3         215     258  2017-03-02\n",
              "4         222     232  2017-03-02\n",
              "5         229     157  2017-03-02\n",
              "6         236     189  2017-03-02\n",
              "7         243      57  2017-03-03\n",
              "8         250     257  2017-03-03\n",
              "9         257      95  2017-03-03\n",
              "10        264     293  2017-03-03\n",
              "11        271     309  2017-03-03\n",
              "12        278      33  2017-03-04\n",
              "13        285      63  2017-03-04\n",
              "14        292      89  2017-03-04\n",
              "15        299     211  2017-03-04\n",
              "16        306      31  2017-03-04\n",
              "17        313     257  2017-03-05\n",
              "18        320      69  2017-03-05\n",
              "19        327     530  2017-03-05\n",
              "20        334     540  2017-03-06\n",
              "21        341     156  2017-03-06\n",
              "22        348     366  2017-03-06\n",
              "23        355     111  2017-03-06\n",
              "24        362     525  2017-03-06\n",
              "25        369     207  2017-03-06\n",
              "26        376      83  2017-03-06\n",
              "27        383     305  2017-03-07\n",
              "28        390     579  2017-03-07\n",
              "29        397      83  2017-03-07\n",
              "...       ...     ...         ...\n",
              "39910  279564  211648  2018-03-07\n",
              "39911  279571   56975  2018-03-07\n",
              "39912  279578   87255  2018-03-07\n",
              "39913  279585  167123  2018-03-07\n",
              "39914  279592  206981  2018-03-07\n",
              "39915  279599  213074  2018-03-07\n",
              "39916  279606  155843  2018-03-07\n",
              "39917  279613  103411  2018-03-07\n",
              "39918  279620  120031  2018-03-07\n",
              "39919  279627  138518  2018-03-07\n",
              "39920  279634  223565  2018-03-07\n",
              "39921  279641  176593  2018-03-07\n",
              "39922  279648   20544  2018-03-07\n",
              "39923  279655  168974  2018-03-07\n",
              "39924  279662  206020  2018-03-07\n",
              "39925  279669  100654  2018-03-07\n",
              "39926  279676  183554  2018-03-07\n",
              "39927  279683  179156  2018-03-07\n",
              "39928  279690  157925  2018-03-07\n",
              "39929  279697  221650  2018-03-07\n",
              "39930  279704  127693  2018-03-07\n",
              "39931  279711  175197  2018-03-07\n",
              "39932  279718  204111  2018-03-07\n",
              "39933  279725  186283  2018-03-07\n",
              "39934  279732  167592  2018-03-07\n",
              "39935  279739   95413  2018-03-07\n",
              "39936  279746  196386  2018-03-07\n",
              "39937  279753  172328  2018-03-07\n",
              "39938  279760  181371  2018-03-07\n",
              "39939  279767  217279  2018-03-07\n",
              "\n",
              "[39940 rows x 3 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 97
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Gg__G0gfzYy6",
        "colab_type": "text"
      },
      "source": [
        "Q3) Given the tables Order_Timeline(schema id,order_id,  message, created) & Order_Shipment  Table(schema id, order_id,actual_dispatch_date,created) ,\n",
        "\n",
        "write a SQL Query to find\n",
        "\n",
        "1. % orders shipped before first message date(OTIF)\n",
        "2. % orders shipped on first message date+1(OTIF+1)\n",
        "3. % orders shipped on first message date+2(OTIF+2)\n",
        "4. %orders shipped after that(OTIF+>2)\n",
        "\n",
        "Order_Timeline contains the message for expected dispatch date, Order_shipment gives you the real dispatch date. They are combined using order_id.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ee9sA3oQzn68",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "db3 = sqlite3.connect(':memory:')\n",
        "dfs3 = pd.read_excel('Q3 (5).xlsx', sheet_name=None)\n",
        "for table, df3 in dfs3.items():\n",
        "    df3.to_sql(table, db3)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uVmzayCPz6zy",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def run_query3(query):\n",
        "  return pd.read_sql_query(query,db3)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SJy3_wBR0AZY",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "86fd9c0f-04ba-4722-88d1-98192a18e12b"
      },
      "source": [
        "query = 'select * from order_timeline limit 5;'\n",
        "run_query3(query)\n"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>index</th>\n",
              "      <th>id</th>\n",
              "      <th>order_id</th>\n",
              "      <th>message</th>\n",
              "      <th>created</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>14</td>\n",
              "      <td>1609</td>\n",
              "      <td>{\"dispatch_date\":\"2017-03-31\"}</td>\n",
              "      <td>2017-03-27 14:12:07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>31</td>\n",
              "      <td>1690</td>\n",
              "      <td>{\"dispatch_date\":\"2017-03-31\"}</td>\n",
              "      <td>2017-03-27 14:44:48</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>55</td>\n",
              "      <td>1723</td>\n",
              "      <td>{\"dispatch_date\":\"2017-03-31\"}</td>\n",
              "      <td>2017-03-27 15:58:28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>105</td>\n",
              "      <td>1669</td>\n",
              "      <td>{\"dispatch_date\":\"2017-04-01\"}</td>\n",
              "      <td>2017-03-27 16:32:53</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>138</td>\n",
              "      <td>1749</td>\n",
              "      <td>{\"dispatch_date\":\"2017-03-31\"}</td>\n",
              "      <td>2017-03-27 18:31:40</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   index   id  order_id                         message              created\n",
              "0      0   14      1609  {\"dispatch_date\":\"2017-03-31\"}  2017-03-27 14:12:07\n",
              "1      1   31      1690  {\"dispatch_date\":\"2017-03-31\"}  2017-03-27 14:44:48\n",
              "2      2   55      1723  {\"dispatch_date\":\"2017-03-31\"}  2017-03-27 15:58:28\n",
              "3      3  105      1669  {\"dispatch_date\":\"2017-04-01\"}  2017-03-27 16:32:53\n",
              "4      4  138      1749  {\"dispatch_date\":\"2017-03-31\"}  2017-03-27 18:31:40"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NhTo28320efa",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "6fa08841-2426-423f-cfd9-5fbd8abf099b"
      },
      "source": [
        "query = 'select * from order_shipment limit 5;'\n",
        "run_query3(query)"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>index</th>\n",
              "      <th>id</th>\n",
              "      <th>order_id</th>\n",
              "      <th>actual_dispatch_date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>1514</td>\n",
              "      <td>1514</td>\n",
              "      <td>2017-03-28 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>1669</td>\n",
              "      <td>1669</td>\n",
              "      <td>2017-03-29 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>1723</td>\n",
              "      <td>1723</td>\n",
              "      <td>2017-03-27 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>1772</td>\n",
              "      <td>1772</td>\n",
              "      <td>2017-03-28 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>1880</td>\n",
              "      <td>1880</td>\n",
              "      <td>2017-03-30 00:00:00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   index    id  order_id actual_dispatch_date\n",
              "0      0  1514      1514  2017-03-28 00:00:00\n",
              "1      1  1669      1669  2017-03-29 00:00:00\n",
              "2      2  1723      1723  2017-03-27 00:00:00\n",
              "3      3  1772      1772  2017-03-28 00:00:00\n",
              "4      4  1880      1880  2017-03-30 00:00:00"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NM40jPGb4rWG",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "9e2548a2-4414-4dbe-b609-bbf6f4a834d4"
      },
      "source": [
        "# query = '''\n",
        "# SELECT count(*) as total from (Select ot.order_id,  JSON_EXTRACT(ot.message,'$.dispatch_date') as message_date, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot\n",
        "# inner join order_shipment os\n",
        "# on ot.order_id = os.order_id where dispatch_date != \"None\");'''\n",
        "# run_query3(query)"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>total</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>27720</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   total\n",
              "0  27720"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MEB_cryFPo3o",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "2d76a909-a2fc-445d-ecb6-adc310f9f5d3"
      },
      "source": [
        "query = '''\n",
        "select ROUND(CAST((m.OTIF * 100.0 / total) AS FLOAT), 2) as OTIF, ROUND(CAST((m.OTIF1 * 100.0 / total) AS FLOAT), 2) as OTIF1, ROUND(CAST((m.OTIF2 * 100.0 / total) AS FLOAT), 2) as OTIF2, ROUND(CAST((m.OTIF3 * 100.0 / total) AS FLOAT), 2) as OTIF3 FROM \n",
        "(select a.OTIF, b.OTIF1, c.OTIF2, d.OTIF3, e.total FROM\n",
        "(SELECT count(*) as total from (Select ot.order_id,  JSON_EXTRACT(ot.message,'$.dispatch_date') as message_date, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot\n",
        "inner join order_shipment os\n",
        "on ot.order_id = os.order_id where dispatch_date != \"None\")) as e\n",
        "inner join \n",
        "(Select count(*) as OTIF FROM (Select * FROM (select ot.order_id,  JSON_EXTRACT(ot.message,'$.dispatch_date') as message_date, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot\n",
        "inner join order_shipment os\n",
        "on ot.order_id = os.order_id where dispatch_date != \"None\") as x\n",
        "where x.message_date >= x.dispatch_date) as y) as a\n",
        "inner join\n",
        "(Select count(*) as OTIF1 FROM (Select * FROM (select ot.order_id,  JSON_EXTRACT(ot.message,'$.dispatch_date') as message_date, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot\n",
        "inner join order_shipment os\n",
        "on ot.order_id = os.order_id where dispatch_date != \"None\") as x\n",
        "where x.dispatch_date = date(x.message_date, '+1 day')) as z) as b\n",
        "inner join\n",
        "(Select count(*) as OTIF2 FROM (Select * FROM (select ot.order_id,  JSON_EXTRACT(ot.message,'$.dispatch_date') as message_date, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot\n",
        "inner join order_shipment os\n",
        "on ot.order_id = os.order_id where dispatch_date != \"None\") as x\n",
        "where x.dispatch_date = date(x.message_date, '+2 day')) as z) as c\n",
        "inner join \n",
        "(Select count(*) as OTIF3 FROM (Select * FROM (select ot.order_id,  JSON_EXTRACT(ot.message,'$.dispatch_date') as message_date, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot\n",
        "inner join order_shipment os\n",
        "on ot.order_id = os.order_id where dispatch_date != \"None\") as x\n",
        "where x.dispatch_date > date(x.message_date, '+2 day')) as z) as d) as m\n",
        ";'''\n",
        "run_query3(query)"
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>OTIF</th>\n",
              "      <th>OTIF1</th>\n",
              "      <th>OTIF2</th>\n",
              "      <th>OTIF3</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>95.0</td>\n",
              "      <td>1.99</td>\n",
              "      <td>1.15</td>\n",
              "      <td>1.86</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   OTIF  OTIF1  OTIF2  OTIF3\n",
              "0  95.0   1.99   1.15   1.86"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 33
        }
      ]
    }
  ]
}