5_SQL_Date and Time Functions .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f3f6d67e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.set_option('display.max_rows',100)\n",
    "import mysql.connector as connection\n",
    "import pymysql\n",
    "dbname = \"farmers_market\"\n",
    "dbhost = \"localhost\"\n",
    "dbusername = \"root\"\n",
    "dbpassword = \"9723811710\"\n",
    "conn = pymysql.connect(host = dbhost,\n",
    "                           port = int(3306),\n",
    "                             user = \"root\",\n",
    "                             passwd = dbpassword,\n",
    "                            db = dbname)\n",
    "%load_ext sql\n",
    "%sql mysql+mysqldb://root:9723811710@localhost/farmers_market"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ac46481b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>market_date</th>\n",
       "      <th>market_start_time</th>\n",
       "      <th>market_end_time</th>\n",
       "      <th>market_start_datetime</th>\n",
       "      <th>market_end_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-03-02</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-03-02 08:00:00</td>\n",
       "      <td>2019-03-02 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-03-09</td>\n",
       "      <td>9:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-03-09 09:00:00</td>\n",
       "      <td>2019-03-09 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-03-13</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-03-13 16:00:00</td>\n",
       "      <td>2019-03-13 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-03-16</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-03-16 08:00:00</td>\n",
       "      <td>2019-03-16 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-03-20</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-03-20 16:00:00</td>\n",
       "      <td>2019-03-20 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-03-23</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-03-23 08:00:00</td>\n",
       "      <td>2019-03-23 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-03-27</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-03-27 16:00:00</td>\n",
       "      <td>2019-03-27 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-04-03 16:00:00</td>\n",
       "      <td>2019-04-03 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-04-06 08:00:00</td>\n",
       "      <td>2019-04-06 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2019-04-10</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-04-10 16:00:00</td>\n",
       "      <td>2019-04-10 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2019-04-13</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-04-13 08:00:00</td>\n",
       "      <td>2019-04-13 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2019-04-17</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-04-17 16:00:00</td>\n",
       "      <td>2019-04-17 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019-04-20</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-04-20 08:00:00</td>\n",
       "      <td>2019-04-20 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2019-04-24</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-04-24 16:00:00</td>\n",
       "      <td>2019-04-24 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2019-04-27</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-04-27 08:00:00</td>\n",
       "      <td>2019-04-27 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-05-01 16:00:00</td>\n",
       "      <td>2019-05-01 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2019-05-04</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-05-04 08:00:00</td>\n",
       "      <td>2019-05-04 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-05-08 16:00:00</td>\n",
       "      <td>2019-05-08 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2019-05-11</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-05-11 08:00:00</td>\n",
       "      <td>2019-05-11 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2019-05-15</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-05-15 16:00:00</td>\n",
       "      <td>2019-05-15 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2019-05-18</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-05-18 08:00:00</td>\n",
       "      <td>2019-05-18 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2019-05-22</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-05-22 16:00:00</td>\n",
       "      <td>2019-05-22 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2019-05-25</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-05-25 08:00:00</td>\n",
       "      <td>2019-05-25 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2019-05-29</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-05-29 16:00:00</td>\n",
       "      <td>2019-05-29 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2019-06-01</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-06-01 08:00:00</td>\n",
       "      <td>2019-06-01 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-06-05 16:00:00</td>\n",
       "      <td>2019-06-05 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2019-06-08</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-06-08 08:00:00</td>\n",
       "      <td>2019-06-08 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2019-06-12</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-06-12 16:00:00</td>\n",
       "      <td>2019-06-12 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2019-06-15</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-06-15 08:00:00</td>\n",
       "      <td>2019-06-15 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2019-06-19</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-06-19 16:00:00</td>\n",
       "      <td>2019-06-19 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-06-22 08:00:00</td>\n",
       "      <td>2019-06-22 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2019-06-26</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-06-26 16:00:00</td>\n",
       "      <td>2019-06-26 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2019-06-29</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-06-29 08:00:00</td>\n",
       "      <td>2019-06-29 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2019-07-03</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-07-03 16:00:00</td>\n",
       "      <td>2019-07-03 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2019-07-06</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-07-06 08:00:00</td>\n",
       "      <td>2019-07-06 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2019-07-10</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-07-10 16:00:00</td>\n",
       "      <td>2019-07-10 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2019-07-13</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-07-13 08:00:00</td>\n",
       "      <td>2019-07-13 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2019-07-17</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-07-17 16:00:00</td>\n",
       "      <td>2019-07-17 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2019-07-20</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-07-20 08:00:00</td>\n",
       "      <td>2019-07-20 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2019-07-24</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-07-24 16:00:00</td>\n",
       "      <td>2019-07-24 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2019-07-27</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-07-27 08:00:00</td>\n",
       "      <td>2019-07-27 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-07-31 16:00:00</td>\n",
       "      <td>2019-07-31 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2019-08-03</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-08-03 08:00:00</td>\n",
       "      <td>2019-08-03 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2019-08-07</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-08-07 16:00:00</td>\n",
       "      <td>2019-08-07 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2019-08-10</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-08-10 08:00:00</td>\n",
       "      <td>2019-08-10 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2019-08-14</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-08-14 16:00:00</td>\n",
       "      <td>2019-08-14 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2019-08-17</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-08-17 08:00:00</td>\n",
       "      <td>2019-08-17 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2019-08-21</td>\n",
       "      <td>4:00 PM</td>\n",
       "      <td>7:00 PM</td>\n",
       "      <td>2019-08-21 16:00:00</td>\n",
       "      <td>2019-08-21 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2019-08-24</td>\n",
       "      <td>8:00 AM</td>\n",
       "      <td>2:00 PM</td>\n",
       "      <td>2019-08-24 08:00:00</td>\n",
       "      <td>2019-08-24 14:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   market_date market_start_time market_end_time market_start_datetime  \\\n",
       "0   2019-03-02           8:00 AM         2:00 PM   2019-03-02 08:00:00   \n",
       "1   2019-03-09           9:00 AM         2:00 PM   2019-03-09 09:00:00   \n",
       "2   2019-03-13           4:00 PM         7:00 PM   2019-03-13 16:00:00   \n",
       "3   2019-03-16           8:00 AM         2:00 PM   2019-03-16 08:00:00   \n",
       "4   2019-03-20           4:00 PM         7:00 PM   2019-03-20 16:00:00   \n",
       "5   2019-03-23           8:00 AM         2:00 PM   2019-03-23 08:00:00   \n",
       "6   2019-03-27           4:00 PM         7:00 PM   2019-03-27 16:00:00   \n",
       "7   2019-03-30           8:00 AM         2:00 PM   2019-03-30 08:00:00   \n",
       "8   2019-04-03           4:00 PM         7:00 PM   2019-04-03 16:00:00   \n",
       "9   2019-04-06           8:00 AM         2:00 PM   2019-04-06 08:00:00   \n",
       "10  2019-04-10           4:00 PM         7:00 PM   2019-04-10 16:00:00   \n",
       "11  2019-04-13           8:00 AM         2:00 PM   2019-04-13 08:00:00   \n",
       "12  2019-04-17           4:00 PM         7:00 PM   2019-04-17 16:00:00   \n",
       "13  2019-04-20           8:00 AM         2:00 PM   2019-04-20 08:00:00   \n",
       "14  2019-04-24           4:00 PM         7:00 PM   2019-04-24 16:00:00   \n",
       "15  2019-04-27           8:00 AM         2:00 PM   2019-04-27 08:00:00   \n",
       "16  2019-05-01           4:00 PM         7:00 PM   2019-05-01 16:00:00   \n",
       "17  2019-05-04           8:00 AM         2:00 PM   2019-05-04 08:00:00   \n",
       "18  2019-05-08           4:00 PM         7:00 PM   2019-05-08 16:00:00   \n",
       "19  2019-05-11           8:00 AM         2:00 PM   2019-05-11 08:00:00   \n",
       "20  2019-05-15           4:00 PM         7:00 PM   2019-05-15 16:00:00   \n",
       "21  2019-05-18           8:00 AM         2:00 PM   2019-05-18 08:00:00   \n",
       "22  2019-05-22           4:00 PM         7:00 PM   2019-05-22 16:00:00   \n",
       "23  2019-05-25           8:00 AM         2:00 PM   2019-05-25 08:00:00   \n",
       "24  2019-05-29           4:00 PM         7:00 PM   2019-05-29 16:00:00   \n",
       "25  2019-06-01           8:00 AM         2:00 PM   2019-06-01 08:00:00   \n",
       "26  2019-06-05           4:00 PM         7:00 PM   2019-06-05 16:00:00   \n",
       "27  2019-06-08           8:00 AM         2:00 PM   2019-06-08 08:00:00   \n",
       "28  2019-06-12           4:00 PM         7:00 PM   2019-06-12 16:00:00   \n",
       "29  2019-06-15           8:00 AM         2:00 PM   2019-06-15 08:00:00   \n",
       "30  2019-06-19           4:00 PM         7:00 PM   2019-06-19 16:00:00   \n",
       "31  2019-06-22           8:00 AM         2:00 PM   2019-06-22 08:00:00   \n",
       "32  2019-06-26           4:00 PM         7:00 PM   2019-06-26 16:00:00   \n",
       "33  2019-06-29           8:00 AM         2:00 PM   2019-06-29 08:00:00   \n",
       "34  2019-07-03           4:00 PM         7:00 PM   2019-07-03 16:00:00   \n",
       "35  2019-07-06           8:00 AM         2:00 PM   2019-07-06 08:00:00   \n",
       "36  2019-07-10           4:00 PM         7:00 PM   2019-07-10 16:00:00   \n",
       "37  2019-07-13           8:00 AM         2:00 PM   2019-07-13 08:00:00   \n",
       "38  2019-07-17           4:00 PM         7:00 PM   2019-07-17 16:00:00   \n",
       "39  2019-07-20           8:00 AM         2:00 PM   2019-07-20 08:00:00   \n",
       "40  2019-07-24           4:00 PM         7:00 PM   2019-07-24 16:00:00   \n",
       "41  2019-07-27           8:00 AM         2:00 PM   2019-07-27 08:00:00   \n",
       "42  2019-07-31           4:00 PM         7:00 PM   2019-07-31 16:00:00   \n",
       "43  2019-08-03           8:00 AM         2:00 PM   2019-08-03 08:00:00   \n",
       "44  2019-08-07           4:00 PM         7:00 PM   2019-08-07 16:00:00   \n",
       "45  2019-08-10           8:00 AM         2:00 PM   2019-08-10 08:00:00   \n",
       "46  2019-08-14           4:00 PM         7:00 PM   2019-08-14 16:00:00   \n",
       "47  2019-08-17           8:00 AM         2:00 PM   2019-08-17 08:00:00   \n",
       "48  2019-08-21           4:00 PM         7:00 PM   2019-08-21 16:00:00   \n",
       "49  2019-08-24           8:00 AM         2:00 PM   2019-08-24 08:00:00   \n",
       "\n",
       "   market_end_datetime  \n",
       "0  2019-03-02 14:00:00  \n",
       "1  2019-03-09 14:00:00  \n",
       "2  2019-03-13 19:00:00  \n",
       "3  2019-03-16 14:00:00  \n",
       "4  2019-03-20 19:00:00  \n",
       "5  2019-03-23 14:00:00  \n",
       "6  2019-03-27 19:00:00  \n",
       "7  2019-03-30 14:00:00  \n",
       "8  2019-04-03 19:00:00  \n",
       "9  2019-04-06 14:00:00  \n",
       "10 2019-04-10 19:00:00  \n",
       "11 2019-04-13 14:00:00  \n",
       "12 2019-04-17 19:00:00  \n",
       "13 2019-04-20 14:00:00  \n",
       "14 2019-04-24 19:00:00  \n",
       "15 2019-04-27 14:00:00  \n",
       "16 2019-05-01 19:00:00  \n",
       "17 2019-05-04 14:00:00  \n",
       "18 2019-05-08 19:00:00  \n",
       "19 2019-05-11 14:00:00  \n",
       "20 2019-05-15 19:00:00  \n",
       "21 2019-05-18 14:00:00  \n",
       "22 2019-05-22 19:00:00  \n",
       "23 2019-05-25 14:00:00  \n",
       "24 2019-05-29 19:00:00  \n",
       "25 2019-06-01 14:00:00  \n",
       "26 2019-06-05 19:00:00  \n",
       "27 2019-06-08 14:00:00  \n",
       "28 2019-06-12 19:00:00  \n",
       "29 2019-06-15 14:00:00  \n",
       "30 2019-06-19 19:00:00  \n",
       "31 2019-06-22 14:00:00  \n",
       "32 2019-06-26 19:00:00  \n",
       "33 2019-06-29 14:00:00  \n",
       "34 2019-07-03 19:00:00  \n",
       "35 2019-07-06 14:00:00  \n",
       "36 2019-07-10 19:00:00  \n",
       "37 2019-07-13 14:00:00  \n",
       "38 2019-07-17 19:00:00  \n",
       "39 2019-07-20 14:00:00  \n",
       "40 2019-07-24 19:00:00  \n",
       "41 2019-07-27 14:00:00  \n",
       "42 2019-07-31 19:00:00  \n",
       "43 2019-08-03 14:00:00  \n",
       "44 2019-08-07 19:00:00  \n",
       "45 2019-08-10 14:00:00  \n",
       "46 2019-08-14 19:00:00  \n",
       "47 2019-08-17 14:00:00  \n",
       "48 2019-08-21 19:00:00  \n",
       "49 2019-08-24 14:00:00  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "select \n",
    "* \n",
    "from\n",
    "datetime_demo;\n",
    "\n",
    "\"\"\",conn).head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bbfddc81",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>market_date</th>\n",
       "      <th>EXTRACT(DAY FROM market_date)</th>\n",
       "      <th>EXTRACT(MONTH FROM market_date)</th>\n",
       "      <th>EXTRACT(YEAR FROM market_date)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-03-09</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-03-13</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-03-16</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-03-20</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-03-23</td>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-03-27</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  market_date  EXTRACT(DAY FROM market_date)  EXTRACT(MONTH FROM market_date)  \\\n",
       "0  2019-03-02                              2                                3   \n",
       "1  2019-03-09                              9                                3   \n",
       "2  2019-03-13                             13                                3   \n",
       "3  2019-03-16                             16                                3   \n",
       "4  2019-03-20                             20                                3   \n",
       "5  2019-03-23                             23                                3   \n",
       "6  2019-03-27                             27                                3   \n",
       "7  2019-03-30                             30                                3   \n",
       "8  2019-04-03                              3                                4   \n",
       "9  2019-04-06                              6                                4   \n",
       "\n",
       "   EXTRACT(YEAR FROM market_date)  \n",
       "0                            2019  \n",
       "1                            2019  \n",
       "2                            2019  \n",
       "3                            2019  \n",
       "4                            2019  \n",
       "5                            2019  \n",
       "6                            2019  \n",
       "7                            2019  \n",
       "8                            2019  \n",
       "9                            2019  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "\n",
    "select \n",
    "market_date,\n",
    "EXTRACT(DAY FROM market_date),\n",
    "EXTRACT(MONTH FROM market_date),\n",
    "EXTRACT(YEAR FROM market_date)\n",
    "\n",
    "FROM\n",
    "\n",
    "datetime_demo;\n",
    "\n",
    "\"\"\",conn).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a6c322b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b2b47626",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>market_start_datetime</th>\n",
       "      <th>day</th>\n",
       "      <th>dayName</th>\n",
       "      <th>MonthName</th>\n",
       "      <th>Hour</th>\n",
       "      <th>minute</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-03-02 08:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>March</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 08:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-03-09 09:00:00</td>\n",
       "      <td>9</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>March</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-03-13 16:00:00</td>\n",
       "      <td>13</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>March</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 16:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-03-16 08:00:00</td>\n",
       "      <td>16</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>March</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 08:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-03-20 16:00:00</td>\n",
       "      <td>20</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>March</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 16:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-03-23 08:00:00</td>\n",
       "      <td>23</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>March</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 08:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-03-27 16:00:00</td>\n",
       "      <td>27</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>March</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 16:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>March</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 08:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-04-03 16:00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>April</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 16:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-04-06 08:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>April</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0 days 08:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  market_start_datetime  day    dayName MonthName  Hour  minute  \\\n",
       "0   2019-03-02 08:00:00    2   Saturday     March     8       0   \n",
       "1   2019-03-09 09:00:00    9   Saturday     March     9       0   \n",
       "2   2019-03-13 16:00:00   13  Wednesday     March    16       0   \n",
       "3   2019-03-16 08:00:00   16   Saturday     March     8       0   \n",
       "4   2019-03-20 16:00:00   20  Wednesday     March    16       0   \n",
       "5   2019-03-23 08:00:00   23   Saturday     March     8       0   \n",
       "6   2019-03-27 16:00:00   27  Wednesday     March    16       0   \n",
       "7   2019-03-30 08:00:00   30   Saturday     March     8       0   \n",
       "8   2019-04-03 16:00:00    3  Wednesday     April    16       0   \n",
       "9   2019-04-06 08:00:00    6   Saturday     April     8       0   \n",
       "\n",
       "             time  \n",
       "0 0 days 08:00:00  \n",
       "1 0 days 09:00:00  \n",
       "2 0 days 16:00:00  \n",
       "3 0 days 08:00:00  \n",
       "4 0 days 16:00:00  \n",
       "5 0 days 08:00:00  \n",
       "6 0 days 16:00:00  \n",
       "7 0 days 08:00:00  \n",
       "8 0 days 16:00:00  \n",
       "9 0 days 08:00:00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "\n",
    "select \n",
    "market_start_datetime,\n",
    "DAY(market_start_datetime) AS day,\n",
    "DAYNAME(market_start_datetime) as dayName,\n",
    "MONTHNAME(market_start_datetime) MonthName,\n",
    "HOUR(market_start_datetime) as Hour,\n",
    "MINUTE(market_start_datetime) as minute,\n",
    "TIME(market_start_datetime) AS time\n",
    "\n",
    "FROM\n",
    "\n",
    "datetime_demo;\n",
    "\n",
    "\"\"\",conn).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4c39911e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# DATE_ADD(time , inverval [#])\n",
    "# DATE_SUB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b337eda7",
   "metadata": {},
   "source": [
    "### Question: Let’s say you want to calculate how many sales occurred within the first 30 minutes after the farmer’s market opened, how would you dynamically determine what  cutoff time to use?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "053dd9e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>market_start_datetime</th>\n",
       "      <th>plus30min</th>\n",
       "      <th>plus-30min</th>\n",
       "      <th>sub30min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-03-02 08:00:00</td>\n",
       "      <td>2019-03-02 08:30:00</td>\n",
       "      <td>2019-03-02 07:30:00</td>\n",
       "      <td>2019-03-02 07:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-03-09 09:00:00</td>\n",
       "      <td>2019-03-09 09:30:00</td>\n",
       "      <td>2019-03-09 08:30:00</td>\n",
       "      <td>2019-03-09 08:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-03-13 16:00:00</td>\n",
       "      <td>2019-03-13 16:30:00</td>\n",
       "      <td>2019-03-13 15:30:00</td>\n",
       "      <td>2019-03-13 15:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-03-16 08:00:00</td>\n",
       "      <td>2019-03-16 08:30:00</td>\n",
       "      <td>2019-03-16 07:30:00</td>\n",
       "      <td>2019-03-16 07:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-03-20 16:00:00</td>\n",
       "      <td>2019-03-20 16:30:00</td>\n",
       "      <td>2019-03-20 15:30:00</td>\n",
       "      <td>2019-03-20 15:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-03-23 08:00:00</td>\n",
       "      <td>2019-03-23 08:30:00</td>\n",
       "      <td>2019-03-23 07:30:00</td>\n",
       "      <td>2019-03-23 07:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-03-27 16:00:00</td>\n",
       "      <td>2019-03-27 16:30:00</td>\n",
       "      <td>2019-03-27 15:30:00</td>\n",
       "      <td>2019-03-27 15:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30 08:30:00</td>\n",
       "      <td>2019-03-30 07:30:00</td>\n",
       "      <td>2019-03-30 07:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-04-03 16:00:00</td>\n",
       "      <td>2019-04-03 16:30:00</td>\n",
       "      <td>2019-04-03 15:30:00</td>\n",
       "      <td>2019-04-03 15:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-04-06 08:00:00</td>\n",
       "      <td>2019-04-06 08:30:00</td>\n",
       "      <td>2019-04-06 07:30:00</td>\n",
       "      <td>2019-04-06 07:30:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  market_start_datetime           plus30min          plus-30min  \\\n",
       "0   2019-03-02 08:00:00 2019-03-02 08:30:00 2019-03-02 07:30:00   \n",
       "1   2019-03-09 09:00:00 2019-03-09 09:30:00 2019-03-09 08:30:00   \n",
       "2   2019-03-13 16:00:00 2019-03-13 16:30:00 2019-03-13 15:30:00   \n",
       "3   2019-03-16 08:00:00 2019-03-16 08:30:00 2019-03-16 07:30:00   \n",
       "4   2019-03-20 16:00:00 2019-03-20 16:30:00 2019-03-20 15:30:00   \n",
       "5   2019-03-23 08:00:00 2019-03-23 08:30:00 2019-03-23 07:30:00   \n",
       "6   2019-03-27 16:00:00 2019-03-27 16:30:00 2019-03-27 15:30:00   \n",
       "7   2019-03-30 08:00:00 2019-03-30 08:30:00 2019-03-30 07:30:00   \n",
       "8   2019-04-03 16:00:00 2019-04-03 16:30:00 2019-04-03 15:30:00   \n",
       "9   2019-04-06 08:00:00 2019-04-06 08:30:00 2019-04-06 07:30:00   \n",
       "\n",
       "             sub30min  \n",
       "0 2019-03-02 07:30:00  \n",
       "1 2019-03-09 08:30:00  \n",
       "2 2019-03-13 15:30:00  \n",
       "3 2019-03-16 07:30:00  \n",
       "4 2019-03-20 15:30:00  \n",
       "5 2019-03-23 07:30:00  \n",
       "6 2019-03-27 15:30:00  \n",
       "7 2019-03-30 07:30:00  \n",
       "8 2019-04-03 15:30:00  \n",
       "9 2019-04-06 07:30:00  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "select \n",
    "    market_start_datetime,\n",
    "    date_add(market_start_datetime, interval 30 minute) as plus30min,\n",
    "    date_add(market_start_datetime, interval -30 minute) as 'plus-30min',\n",
    "    date_sub(market_start_datetime, interval 30 minute) as sub30min\n",
    "    \n",
    "from\n",
    "    datetime_demo\n",
    "    \n",
    ";\n",
    "\n",
    "\"\"\",conn).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "029fd604",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65973572",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "a5cfd4b1",
   "metadata": {},
   "source": [
    "### Question: Let’s say we wanted to get a profile of each farmer’s market customer’s habits over time.\n",
    "1. First purchase - date\n",
    "2. Last purchase - date\n",
    "3. Count of distinct purchases "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ff9868dd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>customer_id</th>\n",
       "      <th>first_pruchase_date</th>\n",
       "      <th>last_purchase_date</th>\n",
       "      <th>active_days</th>\n",
       "      <th>from_today</th>\n",
       "      <th>num_purchases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>553</td>\n",
       "      <td>1244</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>553</td>\n",
       "      <td>1244</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>553</td>\n",
       "      <td>1247</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-03</td>\n",
       "      <td>549</td>\n",
       "      <td>1247</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>556</td>\n",
       "      <td>1247</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>556</td>\n",
       "      <td>1247</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>556</td>\n",
       "      <td>1247</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>553</td>\n",
       "      <td>1244</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>556</td>\n",
       "      <td>1247</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>556</td>\n",
       "      <td>1247</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>556</td>\n",
       "      <td>1247</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-03</td>\n",
       "      <td>549</td>\n",
       "      <td>1247</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>2019-04-13</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>546</td>\n",
       "      <td>1237</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>543</td>\n",
       "      <td>1244</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>2019-04-20</td>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>536</td>\n",
       "      <td>1230</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>553</td>\n",
       "      <td>1247</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>543</td>\n",
       "      <td>1244</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>550</td>\n",
       "      <td>1244</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>2019-04-24</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>535</td>\n",
       "      <td>1226</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-03</td>\n",
       "      <td>549</td>\n",
       "      <td>1247</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>2020-09-26</td>\n",
       "      <td>539</td>\n",
       "      <td>1244</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>2019-04-17</td>\n",
       "      <td>2020-09-19</td>\n",
       "      <td>521</td>\n",
       "      <td>1233</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>556</td>\n",
       "      <td>1247</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>525</td>\n",
       "      <td>1219</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>2019-04-10</td>\n",
       "      <td>2020-10-03</td>\n",
       "      <td>542</td>\n",
       "      <td>1240</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>2019-04-20</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>539</td>\n",
       "      <td>1230</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id first_pruchase_date last_purchase_date  active_days  \\\n",
       "0             1          2019-04-06         2020-10-10          553   \n",
       "1             2          2019-04-06         2020-10-10          553   \n",
       "2             3          2019-04-03         2020-10-07          553   \n",
       "3             4          2019-04-03         2020-10-03          549   \n",
       "4             5          2019-04-03         2020-10-10          556   \n",
       "5             6          2019-04-03         2020-10-10          556   \n",
       "6             7          2019-04-03         2020-10-10          556   \n",
       "7             8          2019-04-06         2020-10-10          553   \n",
       "8             9          2019-04-03         2020-10-10          556   \n",
       "9            10          2019-04-03         2020-10-10          556   \n",
       "10           11          2019-04-03         2020-10-10          556   \n",
       "11           12          2019-04-03         2020-10-03          549   \n",
       "12           13          2019-04-13         2020-10-10          546   \n",
       "13           14          2019-04-06         2020-09-30          543   \n",
       "14           15          2019-04-20         2020-10-07          536   \n",
       "15           16          2019-04-03         2020-10-07          553   \n",
       "16           17          2019-04-06         2020-09-30          543   \n",
       "17           18          2019-04-06         2020-10-07          550   \n",
       "18           19          2019-04-24         2020-10-10          535   \n",
       "19           20          2019-04-03         2020-10-03          549   \n",
       "20           21          2019-04-06         2020-09-26          539   \n",
       "21           22          2019-04-17         2020-09-19          521   \n",
       "22           23          2019-04-03         2020-10-10          556   \n",
       "23           24          2019-05-01         2020-10-07          525   \n",
       "24           25          2019-04-10         2020-10-03          542   \n",
       "25           26          2019-04-20         2020-10-10          539   \n",
       "\n",
       "    from_today  num_purchases  \n",
       "0         1244            107  \n",
       "1         1244            117  \n",
       "2         1247            112  \n",
       "3         1247            115  \n",
       "4         1247            113  \n",
       "5         1247             95  \n",
       "6         1247            100  \n",
       "7         1244            101  \n",
       "8         1247             92  \n",
       "9         1247             96  \n",
       "10        1247            100  \n",
       "11        1247            103  \n",
       "12        1237             71  \n",
       "13        1244             68  \n",
       "14        1230             55  \n",
       "15        1247             73  \n",
       "16        1244             78  \n",
       "17        1244             66  \n",
       "18        1226             44  \n",
       "19        1247             40  \n",
       "20        1244             39  \n",
       "21        1233             47  \n",
       "22        1247             49  \n",
       "23        1219             43  \n",
       "24        1240             44  \n",
       "25        1230             50  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "select \n",
    "    customer_id,\n",
    "    min(market_date) as first_pruchase_date,\n",
    "    max(market_date) as last_purchase_date,\n",
    "    datediff(max(market_date),min(market_date)) as active_days,\n",
    "    datediff(now(),min(market_date)) as from_today,\n",
    "    count(distinct(market_date)) as num_purchases\n",
    "from\n",
    "    customer_purchases\n",
    "group by \n",
    "    customer_id\n",
    "    \n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f617856",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c460e482",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "1677d36c",
   "metadata": {},
   "source": [
    "### Question: today’s date is May 31, 2019, and the marketing director of the farmer’s market wants to give infrequent customers an incentive to return to the market in June.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3a0a6dcf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>customer_id</th>\n",
       "      <th>market_date</th>\n",
       "      <th>last_purcahase</th>\n",
       "      <th>count_btwn_purchases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-04-13</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-04-17</td>\n",
       "      <td>2019-04-13</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-04-17</td>\n",
       "      <td>2019-04-17</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-04-20</td>\n",
       "      <td>2019-04-17</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4216</th>\n",
       "      <td>26</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2020-09-19</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4217</th>\n",
       "      <td>26</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4218</th>\n",
       "      <td>26</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4219</th>\n",
       "      <td>26</td>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4220</th>\n",
       "      <td>26</td>\n",
       "      <td>2020-10-10</td>\n",
       "      <td>2020-10-07</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4221 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      customer_id market_date last_purcahase  count_btwn_purchases\n",
       "0               1  2019-04-06           None                   NaN\n",
       "1               1  2019-04-13     2019-04-06                   7.0\n",
       "2               1  2019-04-17     2019-04-13                   4.0\n",
       "3               1  2019-04-17     2019-04-17                   0.0\n",
       "4               1  2019-04-20     2019-04-17                   3.0\n",
       "...           ...         ...            ...                   ...\n",
       "4216           26  2020-09-30     2020-09-19                  11.0\n",
       "4217           26  2020-09-30     2020-09-30                   0.0\n",
       "4218           26  2020-09-30     2020-09-30                   0.0\n",
       "4219           26  2020-10-07     2020-09-30                   7.0\n",
       "4220           26  2020-10-10     2020-10-07                   3.0\n",
       "\n",
       "[4221 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "select \n",
    "customer_id,\n",
    "market_date,\n",
    "LAG(market_date,1) OVER (PARTITION BY\n",
    "                            customer_id\n",
    "                        order by market_date) as last_purcahase,\n",
    "DATEDIFF(market_date,   (Lag(market_date,1) over (partition by\n",
    "                            customer_id\n",
    "                        order by market_date))) as count_btwn_purchases\n",
    "\n",
    "from\n",
    "customer_purchases\n",
    "\n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ae4da926",
   "metadata": {},
   "outputs": [],
   "source": [
    "## FURTHER CONDITIONS  ON ABOVE QUERY "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cb7e08a3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>customer_id</th>\n",
       "      <th>market_date</th>\n",
       "      <th>last_purcahase</th>\n",
       "      <th>count_btwn_purchases</th>\n",
       "      <th>number_of_purcahses_made</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>2019-05-29</td>\n",
       "      <td>2019-04-13</td>\n",
       "      <td>46</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19</td>\n",
       "      <td>2019-05-29</td>\n",
       "      <td>2019-04-24</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id market_date last_purcahase  count_btwn_purchases  \\\n",
       "0           14  2019-05-29     2019-04-13                    46   \n",
       "1           19  2019-05-29     2019-04-24                    35   \n",
       "\n",
       "   number_of_purcahses_made  \n",
       "0                         1  \n",
       "1                         1  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "*,\n",
    "count(distinct(X.market_date)) as number_of_purcahses_made\n",
    "FROM\n",
    "(\n",
    "\n",
    "select \n",
    "customer_id,\n",
    "market_date,\n",
    "LAG(market_date,1) OVER (PARTITION BY\n",
    "                            customer_id\n",
    "                        order by market_date) as last_purcahase,\n",
    "DATEDIFF(market_date,   (Lag(market_date,1) over (partition by\n",
    "                            customer_id\n",
    "                        order by market_date))) as count_btwn_purchases\n",
    "\n",
    "from\n",
    "customer_purchases\n",
    ")\n",
    "AS X\n",
    "\n",
    "WHERE \n",
    "    datediff(\"2019-05-31\",X.market_date) between 0 and 31\n",
    "GROUP BY X.customer_id\n",
    "HAVING count(distinct(X.market_date)) = 1\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7272269f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>customer_id</th>\n",
       "      <th>count(distinct(market_date))</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  count(distinct(market_date))\n",
       "0           14                             1\n",
       "1           19                             1"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "    distinct\n",
    "    customer_id,\n",
    "    count(distinct(market_date))\n",
    "FROM\n",
    "    customer_purchases \n",
    "WHERE\n",
    "    datediff(\"2019-05-31\",market_date) between 0 and 31\n",
    "GROUP BY customer_id\n",
    "HAVING count(distinct(market_date)) = 1\n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "305c6a32",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67bd2c84",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c52d880e",
   "metadata": {},
   "outputs": [],
   "source": [
    "###################################################################\n",
    "import mysql.connector as connection\n",
    "import pymysql\n",
    "dbname = \"hr\"\n",
    "dbhost = \"localhost\"\n",
    "dbusername = \"root\"\n",
    "dbpassword = \"9723811710\"\n",
    "conn = pymysql.connect(host = dbhost,\n",
    "                           port = int(3306),\n",
    "                             user = \"root\",\n",
    "                             passwd = dbpassword,\n",
    "                            db = dbname)\n",
    "\n",
    "%sql mysql+mysqldb://root:9723811710@localhost/hr\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55bbb91d",
   "metadata": {},
   "source": [
    "# Assignment Problems : "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9f2e784",
   "metadata": {},
   "source": [
    "#### Problem Statement:\n",
    "\n",
    "### Display the employee's details like employee id, first_name, last_name, and calculate the years the employees have been working in the company as 'Total_years' till 8th June 2022 of those employees who have been working more than 28 years.\n",
    "\n",
    "Return the columns 'employee_id', 'first_name', 'last_name', 'Total_years'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "86a9359f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>employee_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>hire_date</th>\n",
       "      <th>Total_years_</th>\n",
       "      <th>Total_years</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>Steven</td>\n",
       "      <td>King</td>\n",
       "      <td>1987-06-17</td>\n",
       "      <td>35.0000</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "      <td>1989-09-21</td>\n",
       "      <td>32.7342</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>Lex</td>\n",
       "      <td>De Haan</td>\n",
       "      <td>1993-01-13</td>\n",
       "      <td>29.4192</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Hunold</td>\n",
       "      <td>1990-01-03</td>\n",
       "      <td>32.4493</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>104</td>\n",
       "      <td>Bruce</td>\n",
       "      <td>Ernst</td>\n",
       "      <td>1991-05-21</td>\n",
       "      <td>31.0712</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>200</td>\n",
       "      <td>Jennifer</td>\n",
       "      <td>Whalen</td>\n",
       "      <td>1987-09-17</td>\n",
       "      <td>34.7479</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>203</td>\n",
       "      <td>Susan</td>\n",
       "      <td>Mavris</td>\n",
       "      <td>1994-06-07</td>\n",
       "      <td>28.0219</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>204</td>\n",
       "      <td>Hermann</td>\n",
       "      <td>Baer</td>\n",
       "      <td>1994-06-07</td>\n",
       "      <td>28.0219</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>205</td>\n",
       "      <td>Shelley</td>\n",
       "      <td>Higgins</td>\n",
       "      <td>1994-06-07</td>\n",
       "      <td>28.0219</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>206</td>\n",
       "      <td>William</td>\n",
       "      <td>Gietz</td>\n",
       "      <td>1994-06-07</td>\n",
       "      <td>28.0219</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id first_name last_name   hire_date  Total_years_  Total_years\n",
       "0          100     Steven      King  1987-06-17       35.0000           35\n",
       "1          101      Neena   Kochhar  1989-09-21       32.7342           33\n",
       "2          102        Lex   De Haan  1993-01-13       29.4192           29\n",
       "3          103  Alexander    Hunold  1990-01-03       32.4493           32\n",
       "4          104      Bruce     Ernst  1991-05-21       31.0712           31\n",
       "5          200   Jennifer    Whalen  1987-09-17       34.7479           35\n",
       "6          203      Susan    Mavris  1994-06-07       28.0219           28\n",
       "7          204    Hermann      Baer  1994-06-07       28.0219           28\n",
       "8          205    Shelley   Higgins  1994-06-07       28.0219           28\n",
       "9          206    William     Gietz  1994-06-07       28.0219           28"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "    employee_id,\n",
    "    first_name,\n",
    "    last_name,\n",
    "    hire_date,\n",
    "    DATEDIFF(\"2022-06-08\",hire_date)/365 as Total_years_,\n",
    "    YEAR(\"2022-06-08\")-YEAR(hire_date) as Total_years\n",
    "    \n",
    "FROM\n",
    "    employees\n",
    "WHERE\n",
    "    (DATEDIFF(\"2022-06-08\",hire_date)/365) >= 28\n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9b99200",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f3ffbb1a",
   "metadata": {},
   "source": [
    "#### Problem Statement:\n",
    "\n",
    "### Extract the day, month, year from the hire_date of the employees and save the columns as 'Day', 'Month', 'Year'. Display the the extracted columns and the details of those employees who were hired in the year 2000 and in January month and also salary is greater than 5000.\n",
    "\n",
    "Return the columns 'employee_id', 'first_name', 'last_name', 'salary', 'hire_date', 'Day', 'Month', 'Year'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e86cb1bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>employee_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>salary</th>\n",
       "      <th>hire_date</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>149</td>\n",
       "      <td>Eleni</td>\n",
       "      <td>Zlotkey</td>\n",
       "      <td>10500</td>\n",
       "      <td>2000-01-29</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>164</td>\n",
       "      <td>Mattea</td>\n",
       "      <td>Marvins</td>\n",
       "      <td>7200</td>\n",
       "      <td>2000-01-24</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>179</td>\n",
       "      <td>Charles</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>6200</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id first_name last_name  salary   hire_date  Day  Month  Year\n",
       "0          149      Eleni   Zlotkey   10500  2000-01-29   29      1  2000\n",
       "1          164     Mattea   Marvins    7200  2000-01-24   24      1  2000\n",
       "2          179    Charles   Johnson    6200  2000-01-04    4      1  2000"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "    employee_id,\n",
    "    first_name,\n",
    "    last_name,\n",
    "    salary,\n",
    "    hire_date,\n",
    "    day(hire_date) as Day,\n",
    "    MONTH(hire_date) as Month,\n",
    "    YEAR(hire_date) as Year\n",
    "    \n",
    "    \n",
    "FROM\n",
    "    employees\n",
    "WHERE\n",
    "    Year(hire_date) = 2000\n",
    "    and\n",
    "    MONTH(hire_date)  = 1\n",
    "    and\n",
    "    salary >= 5000 \n",
    "    \n",
    "    \n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "651a8af1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2477829a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "c3758aca",
   "metadata": {},
   "source": [
    "#### Problem Statement:\n",
    "\n",
    "### Display the employees details like employee_id, first_name, last_name who were hired in the month of October and their salary is greater than 4000.\n",
    "\n",
    "Return the columns 'employee_id', 'first_name', 'last_name'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2b2ab912",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>employee_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>salary</th>\n",
       "      <th>hire_date</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>Shanta</td>\n",
       "      <td>Vollman</td>\n",
       "      <td>6500</td>\n",
       "      <td>1997-10-10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>145</td>\n",
       "      <td>John</td>\n",
       "      <td>Russell</td>\n",
       "      <td>14000</td>\n",
       "      <td>1996-10-01</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>148</td>\n",
       "      <td>Gerald</td>\n",
       "      <td>Cambrault</td>\n",
       "      <td>11000</td>\n",
       "      <td>1999-10-15</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>1999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id first_name  last_name  salary   hire_date  Day  Month  Year\n",
       "0          123     Shanta    Vollman    6500  1997-10-10   10     10  1997\n",
       "1          145       John    Russell   14000  1996-10-01    1     10  1996\n",
       "2          148     Gerald  Cambrault   11000  1999-10-15   15     10  1999"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "    employee_id,\n",
    "    first_name,\n",
    "    last_name,\n",
    "    salary,\n",
    "    hire_date,\n",
    "    day(hire_date) as Day,\n",
    "    MONTH(hire_date) as Month,\n",
    "    YEAR(hire_date) as Year\n",
    "    \n",
    "    \n",
    "FROM\n",
    "    employees\n",
    "WHERE\n",
    "    \n",
    "    MONTH(hire_date)  = 10\n",
    "    and\n",
    "    salary >= 4000 \n",
    "    \n",
    "    \n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a52c329",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e8d16a2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd5197d4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "56b0bf44",
   "metadata": {},
   "source": [
    "#### Problem Statement:\n",
    "\n",
    "### Display the manager details like first_name, last_name, manager_id, salary, department_name, and no.of years of experience as 'Experience' till 8th June 2022 of those managers who have experience as a manager is more than 25 years.\n",
    "\n",
    "Return the columns 'first_name', 'last_name', 'manager_id', 'salary', 'department_name', 'Experience'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ea74e24",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "90797f60",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>manager_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>experience</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "      <td>101</td>\n",
       "      <td>17000</td>\n",
       "      <td>32.7342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alexander</td>\n",
       "      <td>Hunold</td>\n",
       "      <td>103</td>\n",
       "      <td>9000</td>\n",
       "      <td>32.4493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lex</td>\n",
       "      <td>De Haan</td>\n",
       "      <td>102</td>\n",
       "      <td>17000</td>\n",
       "      <td>29.4192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shelley</td>\n",
       "      <td>Higgins</td>\n",
       "      <td>205</td>\n",
       "      <td>12000</td>\n",
       "      <td>28.0219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Nancy</td>\n",
       "      <td>Greenberg</td>\n",
       "      <td>108</td>\n",
       "      <td>12000</td>\n",
       "      <td>27.8274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Den</td>\n",
       "      <td>Raphaely</td>\n",
       "      <td>114</td>\n",
       "      <td>11000</td>\n",
       "      <td>27.5205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Payam</td>\n",
       "      <td>Kaufling</td>\n",
       "      <td>122</td>\n",
       "      <td>7900</td>\n",
       "      <td>27.1233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Michael</td>\n",
       "      <td>Hartstein</td>\n",
       "      <td>201</td>\n",
       "      <td>13000</td>\n",
       "      <td>26.3233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Matthew</td>\n",
       "      <td>Weiss</td>\n",
       "      <td>120</td>\n",
       "      <td>8000</td>\n",
       "      <td>25.9068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>John</td>\n",
       "      <td>Russell</td>\n",
       "      <td>145</td>\n",
       "      <td>14000</td>\n",
       "      <td>25.7014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Karen</td>\n",
       "      <td>Partners</td>\n",
       "      <td>146</td>\n",
       "      <td>13500</td>\n",
       "      <td>25.4384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Alberto</td>\n",
       "      <td>Errazuriz</td>\n",
       "      <td>147</td>\n",
       "      <td>12000</td>\n",
       "      <td>25.2630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Adam</td>\n",
       "      <td>Fripp</td>\n",
       "      <td>121</td>\n",
       "      <td>8200</td>\n",
       "      <td>25.1781</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   first_name  last_name manager_id  salary  experience\n",
       "0       Neena    Kochhar        101   17000     32.7342\n",
       "1   Alexander     Hunold        103    9000     32.4493\n",
       "2         Lex    De Haan        102   17000     29.4192\n",
       "3     Shelley    Higgins        205   12000     28.0219\n",
       "4       Nancy  Greenberg        108   12000     27.8274\n",
       "5         Den   Raphaely        114   11000     27.5205\n",
       "6       Payam   Kaufling        122    7900     27.1233\n",
       "7     Michael  Hartstein        201   13000     26.3233\n",
       "8     Matthew      Weiss        120    8000     25.9068\n",
       "9        John    Russell        145   14000     25.7014\n",
       "10      Karen   Partners        146   13500     25.4384\n",
       "11    Alberto  Errazuriz        147   12000     25.2630\n",
       "12       Adam      Fripp        121    8200     25.1781"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "        \n",
    "    distinct\n",
    "    m.first_name,\n",
    "    m.last_name,\n",
    "    e.manager_id,\n",
    "    m.salary,\n",
    "    (datediff(\"2022-06-08\",m.hire_date))/365 as experience \n",
    "    \n",
    "FROM\n",
    "    employees as m\n",
    "    join employees as e\n",
    "        on e.manager_id = m.employee_id\n",
    "WHERE\n",
    "    m.manager_id is not null and \n",
    "    (datediff(\"2022-06-08\",m.hire_date))/365 >= 25\n",
    "ORDER BY \n",
    "    experience desc\n",
    "    \n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d75c84ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>manager_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>department_name</th>\n",
       "      <th>Experience</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "      <td>100</td>\n",
       "      <td>17000</td>\n",
       "      <td>Executive</td>\n",
       "      <td>32.7342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Lex</td>\n",
       "      <td>De Haan</td>\n",
       "      <td>100</td>\n",
       "      <td>17000</td>\n",
       "      <td>Executive</td>\n",
       "      <td>29.4192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alexander</td>\n",
       "      <td>Hunold</td>\n",
       "      <td>102</td>\n",
       "      <td>9000</td>\n",
       "      <td>IT</td>\n",
       "      <td>32.4493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Nancy</td>\n",
       "      <td>Greenberg</td>\n",
       "      <td>101</td>\n",
       "      <td>12000</td>\n",
       "      <td>Finance</td>\n",
       "      <td>27.8274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Den</td>\n",
       "      <td>Raphaely</td>\n",
       "      <td>100</td>\n",
       "      <td>11000</td>\n",
       "      <td>Purchasing</td>\n",
       "      <td>27.5205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Matthew</td>\n",
       "      <td>Weiss</td>\n",
       "      <td>100</td>\n",
       "      <td>8000</td>\n",
       "      <td>Shipping</td>\n",
       "      <td>25.9068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Adam</td>\n",
       "      <td>Fripp</td>\n",
       "      <td>100</td>\n",
       "      <td>8200</td>\n",
       "      <td>Shipping</td>\n",
       "      <td>25.1781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Payam</td>\n",
       "      <td>Kaufling</td>\n",
       "      <td>100</td>\n",
       "      <td>7900</td>\n",
       "      <td>Shipping</td>\n",
       "      <td>27.1233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>John</td>\n",
       "      <td>Russell</td>\n",
       "      <td>100</td>\n",
       "      <td>14000</td>\n",
       "      <td>Sales</td>\n",
       "      <td>25.7014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Karen</td>\n",
       "      <td>Partners</td>\n",
       "      <td>100</td>\n",
       "      <td>13500</td>\n",
       "      <td>Sales</td>\n",
       "      <td>25.4384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Alberto</td>\n",
       "      <td>Errazuriz</td>\n",
       "      <td>100</td>\n",
       "      <td>12000</td>\n",
       "      <td>Sales</td>\n",
       "      <td>25.2630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Michael</td>\n",
       "      <td>Hartstein</td>\n",
       "      <td>100</td>\n",
       "      <td>13000</td>\n",
       "      <td>Marketing</td>\n",
       "      <td>26.3233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Shelley</td>\n",
       "      <td>Higgins</td>\n",
       "      <td>101</td>\n",
       "      <td>12000</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>28.0219</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   first_name  last_name manager_id  salary department_name  Experience\n",
       "0       Neena    Kochhar        100   17000       Executive     32.7342\n",
       "1         Lex    De Haan        100   17000       Executive     29.4192\n",
       "2   Alexander     Hunold        102    9000              IT     32.4493\n",
       "3       Nancy  Greenberg        101   12000         Finance     27.8274\n",
       "4         Den   Raphaely        100   11000      Purchasing     27.5205\n",
       "5     Matthew      Weiss        100    8000        Shipping     25.9068\n",
       "6        Adam      Fripp        100    8200        Shipping     25.1781\n",
       "7       Payam   Kaufling        100    7900        Shipping     27.1233\n",
       "8        John    Russell        100   14000           Sales     25.7014\n",
       "9       Karen   Partners        100   13500           Sales     25.4384\n",
       "10    Alberto  Errazuriz        100   12000           Sales     25.2630\n",
       "11    Michael  Hartstein        100   13000       Marketing     26.3233\n",
       "12    Shelley    Higgins        101   12000      Accounting     28.0219"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "e.first_name,\n",
    "e.last_name,\n",
    "e.manager_id,\n",
    "e.salary,\n",
    "d.department_name,\n",
    "(datediff(\"2022-06-08\",e.hire_date))/365 as Experience     \n",
    "    \n",
    "FROM\n",
    "    employees as e\n",
    "    join departments as d on d.department_id = e.department_id\n",
    "WHERE \n",
    "    e.employee_id in (\n",
    "                    select\n",
    "                        distinct(manager_id)\n",
    "                        from\n",
    "                        employees\n",
    "                        )\n",
    "                    and\n",
    "                    e.manager_id is not null \n",
    "                    \n",
    "                    and\n",
    "                    (datediff(\"2022-06-08\",e.hire_date))/365  > 25\n",
    "\n",
    "    \n",
    "        \n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdb198dc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "c6475275",
   "metadata": {},
   "source": [
    "#### Problem Statement:\n",
    "\n",
    "### Display the year from the hire_date as 'Year' and count the number of employees as 'Employees' joined in that year and order by the count of the number of employees in descending order using the employees table.\n",
    "\n",
    "Return the columns 'Year', 'Employees'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3241e04a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Year</th>\n",
       "      <th>Employees</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1997</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1998</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1996</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1994</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1995</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1987</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1989</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1993</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1990</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1991</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year  Employees\n",
       "0   1997         28\n",
       "1   1998         23\n",
       "2   1999         18\n",
       "3   2000         11\n",
       "4   1996         10\n",
       "5   1994          7\n",
       "6   1995          4\n",
       "7   1987          2\n",
       "8   1989          1\n",
       "9   1993          1\n",
       "10  1990          1\n",
       "11  1991          1"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "Year(hire_date) as Year,\n",
    "count(*) as Employees \n",
    "\n",
    "from\n",
    "employees\n",
    "group by year(hire_date)\n",
    "order by 2 desc\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4df2d39",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "217d34dc",
   "metadata": {},
   "source": [
    "#### Problem Statement:\n",
    "\n",
    "### Display the employee details like employee_id,first_name, last_name,salary, city , department_name ,hire_date of those employees who were hired between the three months from the given date '1998-01-01'.\n",
    "\n",
    "Return the columns 'employee_id', 'first_name', 'last_name', 'salary', 'department_name', 'hire_date', 'city'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "262b42bf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>employee_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>salary</th>\n",
       "      <th>department_name</th>\n",
       "      <th>hire_date</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>106</td>\n",
       "      <td>Valli</td>\n",
       "      <td>Pataballa</td>\n",
       "      <td>4800</td>\n",
       "      <td>IT</td>\n",
       "      <td>1998-02-05</td>\n",
       "      <td>Southlake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>139</td>\n",
       "      <td>John</td>\n",
       "      <td>Seo</td>\n",
       "      <td>2700</td>\n",
       "      <td>Shipping</td>\n",
       "      <td>1998-02-12</td>\n",
       "      <td>South San Francisco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>143</td>\n",
       "      <td>Randall</td>\n",
       "      <td>Matos</td>\n",
       "      <td>2600</td>\n",
       "      <td>Shipping</td>\n",
       "      <td>1998-03-15</td>\n",
       "      <td>South San Francisco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180</td>\n",
       "      <td>Winston</td>\n",
       "      <td>Taylor</td>\n",
       "      <td>3200</td>\n",
       "      <td>Shipping</td>\n",
       "      <td>1998-01-24</td>\n",
       "      <td>South San Francisco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>181</td>\n",
       "      <td>Jean</td>\n",
       "      <td>Fleaur</td>\n",
       "      <td>3100</td>\n",
       "      <td>Shipping</td>\n",
       "      <td>1998-02-23</td>\n",
       "      <td>South San Francisco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>112</td>\n",
       "      <td>Jose Manuel</td>\n",
       "      <td>Urman</td>\n",
       "      <td>7800</td>\n",
       "      <td>Finance</td>\n",
       "      <td>1998-03-07</td>\n",
       "      <td>Seattle</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id   first_name  last_name  salary department_name   hire_date  \\\n",
       "0          106        Valli  Pataballa    4800              IT  1998-02-05   \n",
       "1          139         John        Seo    2700        Shipping  1998-02-12   \n",
       "2          143      Randall      Matos    2600        Shipping  1998-03-15   \n",
       "3          180      Winston     Taylor    3200        Shipping  1998-01-24   \n",
       "4          181         Jean     Fleaur    3100        Shipping  1998-02-23   \n",
       "5          112  Jose Manuel      Urman    7800         Finance  1998-03-07   \n",
       "\n",
       "                  city  \n",
       "0            Southlake  \n",
       "1  South San Francisco  \n",
       "2  South San Francisco  \n",
       "3  South San Francisco  \n",
       "4  South San Francisco  \n",
       "5              Seattle  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT\n",
    "\n",
    "e.employee_id,\n",
    "e.first_name,\n",
    "e.last_name,\n",
    "e.salary,\n",
    "d.department_name,\n",
    "e.hire_date,\n",
    "l.city\n",
    "\n",
    "\n",
    "from\n",
    "employees as e\n",
    "join departments as d on d.department_id = e.department_id\n",
    "join locations as l on l.location_id = d.location_id\n",
    "\n",
    "where\n",
    "e.hire_date between \"1998-01-01\" and date_add(\"1998-01-01\",interval 3 month)\n",
    "\n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9c796e38",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>TIMESTAMPADD(MONTH, 3, SYSDATE())</th>\n",
       "      <th>date_add(\"1998-01-01\",interval 3 month)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-12-01 19:25:12</td>\n",
       "      <td>1998-04-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TIMESTAMPADD(MONTH, 3, SYSDATE()) date_add(\"1998-01-01\",interval 3 month)\n",
       "0               2022-12-01 19:25:12                              1998-04-01"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "select\n",
    "TIMESTAMPADD(MONTH, 3, SYSDATE()),\n",
    "date_add(\"1998-01-01\",interval 3 month)\n",
    "\n",
    "\n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe4f6235",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "73e71f2e",
   "metadata": {},
   "source": [
    "### Problem Statement:\n",
    "\n",
    "### Display the employee details like employee_id,first_name, last_name,salary,hire_date,department_id, of those employees who were hired between the six months from the given date '1998-01-01' (use date_sub()) and also whose salary is highest in that each department.\n",
    "\n",
    "Return the columns 'employee_id', 'first_name', 'last_name', 'salary', 'hire_date', 'department_id'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0030cb80",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>employee_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>salary</th>\n",
       "      <th>hire_date</th>\n",
       "      <th>department_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>186</td>\n",
       "      <td>Julia</td>\n",
       "      <td>Dellinger</td>\n",
       "      <td>3400</td>\n",
       "      <td>1998-06-24</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>106</td>\n",
       "      <td>Valli</td>\n",
       "      <td>Pataballa</td>\n",
       "      <td>4800</td>\n",
       "      <td>1998-02-05</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>169</td>\n",
       "      <td>Harrison</td>\n",
       "      <td>Bloom</td>\n",
       "      <td>10000</td>\n",
       "      <td>1998-03-23</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>112</td>\n",
       "      <td>Jose Manuel</td>\n",
       "      <td>Urman</td>\n",
       "      <td>7800</td>\n",
       "      <td>1998-03-07</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id   first_name  last_name  salary   hire_date  department_id\n",
       "0          186        Julia  Dellinger    3400  1998-06-24             50\n",
       "1          106        Valli  Pataballa    4800  1998-02-05             60\n",
       "2          169     Harrison      Bloom   10000  1998-03-23             80\n",
       "3          112  Jose Manuel      Urman    7800  1998-03-07            100"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "select\n",
    "    x.employee_id,\n",
    "    x.first_name,\n",
    "    x.last_name,\n",
    "    x.salary,\n",
    "    x.hire_date,\n",
    "    x.department_id\n",
    "from\n",
    "        (\n",
    "        select \n",
    "            employee_id,\n",
    "            first_name,\n",
    "            last_name,\n",
    "            salary,\n",
    "            max(salary) over (partition by department_id) as max_salary,\n",
    "            hire_date,\n",
    "            department_id\n",
    "        from\n",
    "            employees\n",
    "        where\n",
    "            hire_date between  \"1998-01-01\" and date_add(\"1998-01-01\", interval 6 month) \n",
    "        ) as x\n",
    "where x.salary = x.max_salary\n",
    "\n",
    "\n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0541b5e4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "1cafe935",
   "metadata": {},
   "source": [
    "#### Problem Statement:\n",
    "\n",
    "### Display employee name(first name and last name) as 'full_name', job title for the jobs who worked less than twelve months.\n",
    "\n",
    "Return the columns 'full_name', 'job_title'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f52d6bb3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aadfba86",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "d7f094c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>full_name</th>\n",
       "      <th>job_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jonathon Taylor</td>\n",
       "      <td>Sales Representative</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         full_name             job_title\n",
       "0  Jonathon Taylor  Sales Representative"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "select \n",
    "concat(first_name,' ',last_name) 'full_name', \n",
    "job_title \n",
    "\n",
    "from employees emp \n",
    "    join job_history  jh  on  jh.employee_id = emp.employee_id \n",
    "    join jobs job on  jh.job_id = job.job_id  \n",
    "\n",
    "where \n",
    "    (datediff(end_date,start_date) /30) < 12;\n",
    "\n",
    "\"\"\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5728dc1f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0bde00e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46153643",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ace2325",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "d0e27e32",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (window._pyforest_update_imports_cell) { window._pyforest_update_imports_cell('import pandas as pd'); }\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>TIMESTAMPDIFF(MONTH, (date_sub(\"2022-01-01\", interval 12 month)), \"2022-01-01\")</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TIMESTAMPDIFF(MONTH, (date_sub(\"2022-01-01\", interval 12 month)), \"2022-01-01\")\n",
       "0                                                 12                              "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"\n",
    "\n",
    "SELECT \n",
    "TIMESTAMPDIFF(MONTH, (date_sub(\"2022-01-01\", interval 12 month)), \"2022-01-01\") \n",
    "\n",
    "\n",
    ";\n",
    "\n",
    "\"\"\",conn)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}