Shopify_Technical_Challenge_Submission.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "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.7.6"
    },
    "colab": {
      "name": "Shopify Technical Challenge Submission.ipynb",
      "provenance": [],
      "collapsed_sections": []
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GLOOZC_dSWBS"
      },
      "source": [
        "# Q1\n",
        "## a\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p8_6JTQHSWBT"
      },
      "source": [
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt \n",
        "data = pd.read_excel(\"2019 Winter Data Science Intern Challenge Data Set.xlsx\")"
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W9sAM9YgWJ2O"
      },
      "source": [
        "Check the order amount information first."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "56XlUXFWSWBU",
        "outputId": "d92299e6-b34b-4f15-fdc9-16f6a4c1d234"
      },
      "source": [
        "data.order_amount.describe()"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count      5000.000000\n",
              "mean       3145.128000\n",
              "std       41282.539349\n",
              "min          90.000000\n",
              "25%         163.000000\n",
              "50%         284.000000\n",
              "75%         390.000000\n",
              "max      704000.000000\n",
              "Name: order_amount, dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "r7ubWRA4SWBV"
      },
      "source": [
        "Since the standard deviation of the order amount is as high as 41282 and 75% of the order amount is not greater than 390, the AOV of 3145.13 is affected by the outliers like 704000. "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZSBAXUikWTrD"
      },
      "source": [
        "Then check the total items to find the relationship between order amount and total items."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "iBika8rASWBW",
        "outputId": "8b1f9c44-c5c0-41ce-9d45-77334a7fdf8e"
      },
      "source": [
        "data.total_items.describe()"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    5000.00000\n",
              "mean        8.78720\n",
              "std       116.32032\n",
              "min         1.00000\n",
              "25%         1.00000\n",
              "50%         2.00000\n",
              "75%         3.00000\n",
              "max      2000.00000\n",
              "Name: total_items, dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "960_hDGnSWBW"
      },
      "source": [
        "The outliers of 704000 could be result from large orders with 2000 items."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Cm3asAJmWVjC"
      },
      "source": [
        "Plot the orders(total items, order amount) to find the distribution and outliers."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 282
        },
        "id": "ha5i1In9SWBW",
        "outputId": "b4fb4f04-2e1b-4ae8-be13-59aafb7f12c4"
      },
      "source": [
        "plt.scatter(data.total_items, data.order_amount)"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<matplotlib.collections.PathCollection at 0x7efe82b43210>"
            ]
          },
          "metadata": {},
          "execution_count": 7
        },
        {
          "output_type": "display_data",
          "data": {
            "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYkAAAD4CAYAAAAZ1BptAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAXqklEQVR4nO3df5BdZ33f8fcnEqYegpEMW40jmUg0qjNKM/GPHaNMCDPFQT9MilyaofZkKpV6UDtAB4eWRC4zdQqdxpRpSNwSZRzsWsoQbIcA1iQ2QhVM809lvMLCwjaO1sYeS5GtjeUfafEAdr794z6Cq/Weq117966sfb9m7txzvuec+zx79u757Dnn2bupKiRJmspPzHcHJEmnL0NCktTJkJAkdTIkJEmdDAlJUqfF892B2famN72pVq5cOd/dkKRXlf379/9NVY1Mrp9xIbFy5UrGxsbmuxuS9KqS5LGp6l5ukiR1MiQkSZ0MCUlSJ0NCktTJkJAkdTrjRjdJ0kKycttfvKT26PXvmrXXP+WZRJILkhzoezyX5Jok5ybZk+RQe17a1k+SG5KMJ7kvycV9r7WlrX8oyZa++iVJDrZtbkiSVp+yDUnS1AExqP5ynDIkquqhqrqwqi4ELgG+B3wJ2AbsrarVwN42D7ARWN0eW4Ht0DvgA9cBbwUuBa7rO+hvB97ft92GVu9qQ5I0BDO9J3EZ8HBVPQZsAna0+g7gija9CdhZPfuAJUnOA9YDe6rqeFU9DewBNrRl51TVvur9c4udk15rqjYkSUMw05C4Evh8m15WVUfb9BPAsja9HHi8b5vDrTaofniK+qA2TpJka5KxJGMTExMz/JIkSV2mHRJJzgLeDfzp5GXtDGBO/8XdoDaq6saqGq2q0ZGRl3z0iCTpZZrJmcRG4JtV9WSbf7JdKqI9H2v1I8D5fdutaLVB9RVT1Ae1IUkLXtcoptkc3TSTIbBX8eNLTQC7gC3A9e35jr76h5LcSu8m9bNVdTTJbuC/9N2sXgdcW1XH24iptcDdwGbgv5+iDUkSsxsIU5lWSCR5HfBO4F/3la8Hbk9yNfAY8N5WvxO4HBinNxLqfQAtDD4B3NPW+3hVHW/THwBuAc4G7mqPQW1IkoYgvUv9Z47R0dHyo8IlaWaS7K+q0cl1P5ZDktTJkJAkdTIkJEmdDAlJUidDQpLUyZCQJHUyJCRJnQwJSVInQ0KS1MmQkCR1MiQkSZ0MCUlSJ0NCktTJkJAkdTIkJEmdDAlJUidDQpLUyZCQJHUyJCRJnaYVEkmWJPlCku8keTDJLyY5N8meJIfa89K2bpLckGQ8yX1JLu57nS1t/UNJtvTVL0lysG1zQ5K0+pRtSJKGY7pnEr8PfKWqfhb4BeBBYBuwt6pWA3vbPMBGYHV7bAW2Q++AD1wHvBW4FLiu76C/HXh/33YbWr2rDUnSEJwyJJK8AXg7cBNAVf2gqp4BNgE72mo7gCva9CZgZ/XsA5YkOQ9YD+ypquNV9TSwB9jQlp1TVfuqqoCdk15rqjYkSUMwnTOJVcAE8D+T3Jvks0leByyrqqNtnSeAZW16OfB43/aHW21Q/fAUdQa0cZIkW5OMJRmbmJiYxpckSZqO6YTEYuBiYHtVXQT8PyZd9mlnADX73ZteG1V1Y1WNVtXoyMjIXHZDkhaU6YTEYeBwVd3d5r9ALzSebJeKaM/H2vIjwPl9269otUH1FVPUGdCGJGkIThkSVfUE8HiSC1rpMuABYBdwYoTSFuCONr0L2NxGOa0Fnm2XjHYD65IsbTes1wG727Lnkqxto5o2T3qtqdqQJA3B4mmu92+BzyU5C3gEeB+9gLk9ydXAY8B727p3ApcD48D32rpU1fEknwDuaet9vKqOt+kPALcAZwN3tQfA9R1tSJKGIL1L/WeO0dHRGhsbm+9uSNKrSpL9VTU6ue5fXEuSOhkSkqROhoQkqZMhIUnqZEhIkjoZEpKkToaEJKmTISFJ6mRISJI6GRKSpE6GhCSpkyEhSepkSEiSOhkSkqROhoQkqZMhIUnqZEhIkjoZEpKkToaEJKnTtEIiyaNJDiY5kGSs1c5NsifJofa8tNWT5IYk40nuS3Jx3+tsaesfSrKlr35Je/3xtm0GtSFJGo6ZnEn846q6sO8fZW8D9lbVamBvmwfYCKxuj63Adugd8IHrgLcClwLX9R30twPv79tuwynakCQNwSu53LQJ2NGmdwBX9NV3Vs8+YEmS84D1wJ6qOl5VTwN7gA1t2TlVta+qCtg56bWmakOSNATTDYkCvppkf5Ktrbasqo626SeAZW16OfB437aHW21Q/fAU9UFtSJKGYPE013tbVR1J8veBPUm+07+wqipJzX73ptdGC66tAG9+85vnshuStKBM60yiqo6052PAl+jdU3iyXSqiPR9rqx8Bzu/bfEWrDaqvmKLOgDYm9+/GqhqtqtGRkZHpfEmSpGk4ZUgkeV2S15+YBtYB3wZ2ASdGKG0B7mjTu4DNbZTTWuDZdsloN7AuydJ2w3odsLstey7J2jaqafOk15qqDUnSEEznctMy4EttVOpi4E+q6itJ7gFuT3I18Bjw3rb+ncDlwDjwPeB9AFV1PMkngHvaeh+vquNt+gPALcDZwF3tAXB9RxuSpCFIb0DRmWN0dLTGxsbmuxuS9KqSZH/fnzj8iH9xLUnqZEhIkjoZEpKkToaEJKmTISFJ6mRISJI6GRKSpE6GhCSpkyEhSepkSEiSOhkSkqROhoQkqZMhIUnqZEhIkjoZEpKkToaEJKmTISFJ6mRISJI6GRKSpE7TDokki5Lcm+TP2/yqJHcnGU9yW5KzWv21bX68LV/Z9xrXtvpDSdb31Te02niSbX31KduQJA3HTM4kPgw82Df/SeDTVfUzwNPA1a1+NfB0q3+6rUeSNcCVwM8BG4A/aMGzCPgMsBFYA1zV1h3UhiRpCKYVEklWAO8CPtvmA7wD+EJbZQdwRZve1OZpyy9r628Cbq2q71fVd4Fx4NL2GK+qR6rqB8CtwKZTtCFJGoLpnkn8HvCbwN+1+TcCz1TVC23+MLC8TS8HHgdoy59t6/+oPmmbrvqgNiRJQ3DKkEjyq8Cxqto/hP68LEm2JhlLMjYxMTHf3ZGkM8Z0ziR+CXh3kkfpXQp6B/D7wJIki9s6K4AjbfoIcD5AW/4G4Kn++qRtuupPDWjjJFV1Y1WNVtXoyMjINL4kSdJ0nDIkquraqlpRVSvp3Xj+WlX9OvB14NfaaluAO9r0rjZPW/61qqpWv7KNfloFrAa+AdwDrG4jmc5qbexq23S1IUkaglfydxK/BXwkyTi9+wc3tfpNwBtb/SPANoCquh+4HXgA+Arwwap6sd1z+BCwm97oqdvbuoPakCQNQXq/sJ85RkdHa2xsbL67IUmvKkn2V9Xo5Lp/cS1J6mRISJI6GRKSpE6GhCSpkyEhSepkSEiSOhkSkqROhoQkqZMhIUnqZEhIkjoZEpKkToaEJKmTISFJ6mRISJI6GRKSpE6GhCSpkyEhSepkSEiSOhkSkqROhoQkqdMpQyLJ30vyjSTfSnJ/kv/U6quS3J1kPMltSc5q9de2+fG2fGXfa13b6g8lWd9X39Bq40m29dWnbEOSNBzTOZP4PvCOqvoF4EJgQ5K1wCeBT1fVzwBPA1e39a8Gnm71T7f1SLIGuBL4OWAD8AdJFiVZBHwG2AisAa5q6zKgDUnSEJwyJKrn/7bZ17RHAe8AvtDqO4Ar2vSmNk9bflmStPqtVfX9qvouMA5c2h7jVfVIVf0AuBXY1LbpakOSNATTuifRfuM/ABwD9gAPA89U1QttlcPA8ja9HHgcoC1/Fnhjf33SNl31Nw5oY3L/tiYZSzI2MTExnS9JkjQN0wqJqnqxqi4EVtD7zf9n57RXM1RVN1bVaFWNjoyMzHd3JOmMMaPRTVX1DPB14BeBJUkWt0UrgCNt+ghwPkBb/gbgqf76pG266k8NaEOSNATTGd00kmRJmz4beCfwIL2w+LW22hbgjja9q83Tln+tqqrVr2yjn1YBq4FvAPcAq9tIprPo3dze1bbpakOSNASLT70K5wE72iiknwBur6o/T/IAcGuS/wzcC9zU1r8J+OMk48Bxegd9qur+JLcDDwAvAB+sqhcBknwI2A0sAm6uqvvba/1WRxuSpCFI7xf2M8fo6GiNjY3Ndzck6VUlyf6qGp1c9y+uJUmdDAlJUidDQpLUyZCQJHUyJCRJnQwJSVInQ0KS1MmQkCR1MiQkSZ0MCUlSJ0NCktTJkJAkdTIkJEmdDAlJUidDQpLUyZCQJHUyJCRJnQwJSVInQ0KS1OmUIZHk/CRfT/JAkvuTfLjVz02yJ8mh9ry01ZPkhiTjSe5LcnHfa21p6x9KsqWvfkmSg22bG5JkUBuSpOGYzpnEC8C/q6o1wFrgg0nWANuAvVW1Gtjb5gE2AqvbYyuwHXoHfOA64K3ApcB1fQf97cD7+7bb0OpdbUiShuCUIVFVR6vqm236b4EHgeXAJmBHW20HcEWb3gTsrJ59wJIk5wHrgT1Vdbyqngb2ABvasnOqal9VFbBz0mtN1YYkaQhmdE8iyUrgIuBuYFlVHW2LngCWtenlwON9mx1utUH1w1PUGdDG5H5tTTKWZGxiYmImX5IkaYBph0SSnwT+DLimqp7rX9bOAGqW+3aSQW1U1Y1VNVpVoyMjI3PZDUlaUKYVEkleQy8gPldVX2zlJ9ulItrzsVY/Apzft/mKVhtUXzFFfVAbkqQhmM7opgA3AQ9W1e/2LdoFnBihtAW4o6++uY1yWgs82y4Z7QbWJVnablivA3a3Zc8lWdva2jzptaZqQ5I0BIunsc4vAf8COJjkQKv9B+B64PYkVwOPAe9ty+4ELgfGge8B7wOoquNJPgHc09b7eFUdb9MfAG4Bzgbuag8GtCFJGoL0LvWfOUZHR2tsbGy+uyFJrypJ9lfV6OS6f3EtSepkSEiSOhkSkqROhoQkqdN0RjctCF++9wif2v0Qf/3M8/zUkrP56PoLuOKi5afeUJLOYIYEvYC49osHef6HLwJw5JnnufaLBwEMCkkLmpebgE/tfuhHAXHC8z98kU/tfmieeiRJpwdDAvjrZ56fUV2SFgpDAvipJWfPqC5JC4UhAXx0/QWc/ZpFJ9XOfs0iPrr+gnnqkSSdHrxxzY9vTju6SZJOZkg0V1y03FCQpEm83CRJ6mRISJI6GRKSpE6GhCSpkyEhSepkSEiSOjkEFj8BVpK6nPJMIsnNSY4l+XZf7dwke5Icas9LWz1JbkgynuS+JBf3bbOlrX8oyZa++iVJDrZtbkiSQW3MthOfAHvkmecpfvwJsF++98hcNCdJryrTudx0C7BhUm0bsLeqVgN72zzARmB1e2wFtkPvgA9cB7wVuBS4ru+gvx14f992G07RxqzyE2AlqdspQ6Kq/hI4Pqm8CdjRpncAV/TVd1bPPmBJkvOA9cCeqjpeVU8De4ANbdk5VbWvqgrYOem1pmpjVvkJsJLU7eXeuF5WVUfb9BPAsja9HHi8b73DrTaofniK+qA2XiLJ1iRjScYmJiZm9IX4CbCS1O0Vj25qZwA1C3152W1U1Y1VNVpVoyMjIzN6bT8BVpK6vdyQeLJdKqI9H2v1I8D5feutaLVB9RVT1Ae1MauuuGg5v/Oen2f5krMJsHzJ2fzOe37e0U2SxMsPiV3AiRFKW4A7+uqb2yintcCz7ZLRbmBdkqXthvU6YHdb9lyStW1U0+ZJrzVVG7PuN247cNLopt+47cBcNSVJryrTGQL7eeD/ABckOZzkauB64J1JDgG/0uYB7gQeAcaBPwI+AFBVx4FPAPe0x8dbjbbOZ9s2DwN3tXpXG7Nq1ba/eMl1rGp1SVroTvnHdFV1Vceiy6ZYt4APdrzOzcDNU9THgH80Rf2pqdqYbV03Oub0JoskvUr4sRySpE6GhCSp04IPicywLkkLyYIPie9e/66XBEJaXZIWugUfEvDSm9TetJakngUfEis7hrp21SVpIVnwISFJ6mZISJI6GRKSpE6GhCSp04IPiUc7hrp21SVpITnlZzctBAaCJE3NkGDq4a4GhyR5ucm/k5CkARZ8SEiSuhkSkqROhoQkqZMhIUnqtOBDomsU0+/98wuH3BNJOv2c9iGRZEOSh5KMJ9k2rHavue0AX773yLCak6TT0mkdEkkWAZ8BNgJrgKuSrJnNNgYNdb3mtgOz2ZQkveqc1iEBXAqMV9UjVfUD4FZg0zz3SZIWjNM9JJYDj/fNH261kyTZmmQsydjExMTQOidJZ7rTPSSmpapurKrRqhodGRmZ7+5I0hnjdA+JI8D5ffMrWk2SNASne0jcA6xOsirJWcCVwK7ZbGDQB/n5IX+SFrrT+lNgq+qFJB8CdgOLgJur6v7ZbscwkKSpndYhAVBVdwJ3znc/JGkhOt0vN0mS5pEhIUnqZEhIkjoZEpKkTqmq+e7DrEoyATz2Mjd/E/A3s9id2WK/ZsZ+zYz9mpkztV8/XVUv+WvkMy4kXokkY1U1Ot/9mMx+zYz9mhn7NTMLrV9ebpIkdTIkJEmdDImT3TjfHehgv2bGfs2M/ZqZBdUv70lIkjp5JiFJ6mRISJI6GRJAkg1JHkoynmTbkNs+P8nXkzyQ5P4kH271305yJMmB9ri8b5trW18fSrJ+Dvv2aJKDrf2xVjs3yZ4kh9rz0lZPkhtav+5LcvEc9emCvn1yIMlzSa6Zr/2V5OYkx5J8u682432UZEtb/1CSLXPUr08l+U5r+0tJlrT6yiTP9+27P+zb5pL2Hhhvfc8c9GvG37vZ/pnt6NdtfX16NMmBVh/m/uo6PgzvPVZVC/pB7yPIHwbeApwFfAtYM8T2zwMubtOvB/4KWAP8NvDvp1h/Tevja4FVre+L5qhvjwJvmlT7r8C2Nr0N+GSbvhy4CwiwFrh7SN+7J4Cfnq/9BbwduBj49svdR8C5wCPteWmbXjoH/VoHLG7Tn+zr18r+9Sa9zjdaX9P6vnEO+jWj791c/MxO1a9Jy/8b8B/nYX91HR+G9h7zTAIuBcar6pGq+gFwK7BpWI1X1dGq+mab/lvgQab4P959NgG3VtX3q+q7wDi9r2FYNgE72vQO4Iq++s7q2QcsSXLeHPflMuDhqhr0F/Zzur+q6i+B41O0OZN9tB7YU1XHq+ppYA+wYbb7VVVfraoX2uw+ev/psVPr2zlVta96R5qdfV/LrPVrgK7v3az/zA7qVzsbeC/w+UGvMUf7q+v4MLT3mCHR2+GP980fZvBBes4kWQlcBNzdSh9qp4w3nzidZLj9LeCrSfYn2dpqy6rqaJt+Alg2D/064UpO/sGd7/11wkz30Xz08V/R+43zhFVJ7k3yv5P8cqstb30ZRr9m8r0b9v76ZeDJqjrUVxv6/pp0fBjae8yQOE0k+Ungz4Brquo5YDvwD4ALgaP0TneH7W1VdTGwEfhgkrf3L2y/Lc3LGOr0/p3tu4E/baXTYX+9xHzuoy5JPga8AHyulY4Cb66qi4CPAH+S5Jwhdum0/N71uYqTfxkZ+v6a4vjwI3P9HjMk4Ahwft/8ilYbmiSvofcG+FxVfRGgqp6sqher6u+AP+LHl0iG1t+qOtKejwFfan148sRlpPZ8bNj9ajYC36yqJ1sf531/9ZnpPhpaH5P8S+BXgV9vBxfa5Zyn2vR+etf7/2HrQ/8lqTnp18v43g1zfy0G3gPc1tffoe6vqY4PDPE9ZkjAPcDqJKvab6dXAruG1Xi73nkT8GBV/W5fvf96/j8FToy62AVcmeS1SVYBq+ndLJvtfr0uyetPTNO76fnt1v6JkRFbgDv6+rW5ja5YCzzbdzo8F0767W6+99ckM91Hu4F1SZa2Sy3rWm1WJdkA/Cbw7qr6Xl99JMmiNv0Wevvokda355Ksbe/TzX1fy2z2a6bfu2H+zP4K8J2q+tFlpGHur67jA8N8j72SO+9nyoPeiIC/ovcbwceG3Pbb6J0q3gccaI/LgT8GDrb6LuC8vm0+1vr6EK9w9MSAfr2F3qiRbwH3n9gvwBuBvcAh4H8B57Z6gM+0fh0ERudwn70OeAp4Q19tXvYXvaA6CvyQ3nXeq1/OPqJ3j2C8Pd43R/0ap3dd+sT77A/buv+sfY8PAN8E/knf64zSO2g/DPwP2qc0zHK/Zvy9m+2f2an61eq3AP9m0rrD3F9dx4ehvcf8WA5JUicvN0mSOhkSkqROhoQkqZMhIUnqZEhIkjoZEpKkToaEJKnT/webeVVmGwDNTQAAAABJRU5ErkJggg==\n",
            "text/plain": [
              "<Figure size 432x288 with 1 Axes>"
            ]
          },
          "metadata": {
            "needs_background": "light"
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IoMdbcfnSWBX"
      },
      "source": [
        "According to the scatter plot of items and amount of each order, we can see that there are some orders with 2000 items and large amount, which made the AOV of 3145.13 unreasonable.\n",
        "\n",
        "A better way to evaluate AOV is median, which would be affected by the outliers less. The median of order amount is $284."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5-IR7qSVSWBX"
      },
      "source": [
        "## b"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vQyl5lLbSWBX"
      },
      "source": [
        "Another metric I would report for this dataset is the mode of order amount as it would not be affected by the outliers and reflect the most common orders."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5s0eL9zTSWBX"
      },
      "source": [
        "## c"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vK1Nav2gSWBX",
        "outputId": "b28ce765-ff56-434b-fbf8-054ec6669715"
      },
      "source": [
        "data.order_amount.mode()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "data": {
            "text/plain": [
              "0    153\n",
              "dtype: int64"
            ]
          },
          "execution_count": 5,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-uf4_agESWBX"
      },
      "source": [
        "The value of the mode of order amount is $153."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zYq8CImISWBY"
      },
      "source": [
        "# Q2"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KoPxAx29SWBY"
      },
      "source": [
        "## a"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ct7Ot2aKSWBY"
      },
      "source": [
        "SELECT COUNT(*) as SENum\n",
        "\n",
        "FROM Orders JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID\n",
        "\n",
        "WHERE Shippers.ShipperName = \"Speedy Express\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9BKFQS9tSWBY"
      },
      "source": [
        "**output:**\n",
        "\n",
        "54"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8vGRrJqHSWBY"
      },
      "source": [
        "## b"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-DeXBUVUSWBY"
      },
      "source": [
        "SELECT Employees.LastName, COUNT(Orders.OrderID) AS OrderNum\n",
        "\n",
        "FROM Orders JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID\n",
        "\n",
        "GROUP BY Employees.LastName \n",
        "\n",
        "ORDER BY OrderNum DESC\n",
        "\n",
        "LIMIT 1"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3KNh-5QiSWBY"
      },
      "source": [
        "**output:**\n",
        "\n",
        "Peacock with 40 orders."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "myqVdiwDSWBZ"
      },
      "source": [
        "## c"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4H1V5mW0SWBZ"
      },
      "source": [
        "SELECT Products.ProductName, SUM(OrderDetails.Quantity) AS OrderQuantity\n",
        "\n",
        "FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID JOIN Products ON Products.ProductID = OrderDetails.ProductID WHERE Customers.Country = 'Germany'\n",
        "\n",
        "GROUP BY OrderDetails.ProductID\n",
        "\n",
        "ORDER BY OrderQuantity DESC\n",
        "\n",
        "LIMIT 1\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JebAMKu-SWBZ"
      },
      "source": [
        "**output:**\n",
        "\n",
        "Boston Crab Meat with 160 ordered."
      ]
    }
  ]
}