Section 06 Subqueries/25 An Essential Guide to MySQL Derived Tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "03915665-8b6f-4c9b-ad53-c4e052de8b2b",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[An Essential Guide to MySQL Derived Tables](https://www.mysqltutorial.org/mysql-derived-table/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d65443e5-32f1-4663-849c-d825dbb0cebe",
   "metadata": {},
   "source": [
    "Use MySQL derived tables to simplify complex queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "27c64be4-0663-476d-be1f-29cf3ce5ac1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "eeb7e38d-daa4-4b1c-867d-155c48de5450",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "214c1f54-2b82-441f-92cf-219028757389",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to MySQL derived tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c6567554-2228-4996-b44b-73fcef49c6ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAApMAAADoCAMAAACEsp/kAAAB2lBMVEUAAAAAsFAIslUQtVsStFAYt2AgumZwMKBw0513O6V/16eGUK+OW7SP3LKS0FCU0VOVZrmV0VWdcb6j12uke8On2XGo2nSs23qt3Hyv3X+6m9HB5JvF5qLLtNzM6a3P6rLR67XUwOLe0One8cnf9enn9dnn+O/s5PPugzvuhDzviUTvikbvi0jv+Obv+vTwj07wkVHxmFvxnGLxnWTynmXyomvypG/zpnLzqnj1tIj1tYr1upL2vZf3xKL3yKj3+/L3/fr4za/50LX51Lv51r7518D74tL86dv869/87OH8/vr97uT98ej98uv99e/9/vz+9e7+9fD+9vH+9/H+9/P++PT++fT++vf++/j/+vf/+/j/+/n//Pn//Pr//fv///9bm9VpaWkjIyPn8PlHR0fs7Oy41O14rdzDw8MRERFin9fQ0NCGteDT5PSkyOjd3d2JiYl/sd40NDSYmJiuzuqVvuR6enpxqNuNuuJXV1dqpNmnp6e1tbWcw+bE2/DtfTH+9/Luh0H2vpj0rn/wlVj75NXvjUv//fz++fb+9e/zqHX3xqX/+PT4zrH97+X3xKH74M/1tovuhT70sIL++/n0soX87eL62ML63Mj63svxml7zrHv98+z++fUrJy0cAAAV2klEQVR4XuzcB0/jzhrF4febnte9t/QGbN9/b7e373rlyciI+MqY3ZAbZ89PiuIxCAvpUYYZCwuuK8a+MZOMJhmjSUaTjNEko0nGaJLRJGM0yRhNMppkjCazxK3wlTGalDZ8YbGIiA8bapEatxdNesu16m5TAMBSjy0BQBVdaoMpbFJNmxCI1KbRZU0Of07WLiYcTXpri2q8ydwOeiZH5gu+IvHHXGDC0WSu63uguMuPJkOYeiatOQt3WcBbRHZ4FEyT54omo47hSJNeqiVMwybjrYhbBwAQOK7INu6REXMQt8pqCXzxsRK3agfxYSbiBIMmpQ22zEkkcSoD0jZdmDS5eZnJUtcYYbIWU22Wx2LaD5pciZiX0w4caXNGm7SX8G/BJE0uVKPwJSZzXY4wGYvUFap6bpRsK1SOJMGQSdnPRZzMDmYxDiIDJp/qdqQOEOydW5i7aRKLnequ9KyvY2HP5LHITvbPm9zKHLZMXCvlMGTSR9yqPQ5mgaEVjzTZXe4mTNIkUEaqaXhek5LAhrnU3fuAyQNiWcGarPEyk7GIvw9uxiRNAkWjaTFy7o76c/ewHCsM8bDJ2HzDF5pE7IvINrsZkzQJNLocaXKpzRiTSc/k/DVNAtV8Jkl1OyZpcjPa5L1q+LxJ99FTLDM8Euub3H6lyf4fsb5kmG40GZUF4JWpLkaaRKTp83vmtST7wK67Xbvudk8luVKj8uUMJp15BlRb2cMqrzDZaFJtef/eou302FvbwZBJzB73Jw9iSjJj1RZ3A2e8SV9spz/JfmEW2BWPCdOMJsN8p7prFhhtEriLVNM8HDSJ+ay7j5Nt7U2WE0lYJeLOcQaTleOKzOoAxw6zaZqkyenHaJIxmvTlsRrXEaNJmqRJxmiS0SRjNMkYTTKaZIwmGU0yRpOMJhmjScZoktEkYzTJaDLwM1xTjCZ92eN8MZpUW3e8W3po8zY71SY8ni8BYK0R+q2kxqvFaNK09gDcp2oqgXvVBuYtQq9YfJw1RpMRuozMMm0dYq1NgWKjaYFQc/WAzXoXoZcrFV4zRpP2OS0LXaOt0Q1CXaR3QFpGfZN7cXCJGE3mWqLtXiOEGuZrLFIvUpy2lQyvGqNJ7061ACItunOhhqEWeY6+SUiC88Zosv84lrQEHvmpotQQu7t00TeJ7PVXOIwm0xAnJpcaYqM7c/LSq27GudvbaOoZkx5MmhqThW7+HyYZTRqNOYBGF2gLtTEmTbmGl5+7GU2Gqh5Q2vORlp1Jc3DpNQ6jSfsUfS81e+a57jxstOiZvOBeEKPJUFMPWKgpvW+RojN58T1zRpN2vgbum1TTvEDP5HXfW2R85sDhylY5jCbhyArXFKPJYCZ7MMb/fWA0eckYTTJGk4zRJKNJxmiS0SRjNMlo8ucPbx/eAe8eHm707e2Hf/0Dk4km3wC/fPweN933H3/8DROJJj+/+/QZ30b/+fXPuP5o8vM/f/g3vpHevP8wAZQ0+fcf8A31/ndMIM7duJGyxK3wTG/+MvzrMu4FSRu+sFhExIcNtUiNyUeTf/sVT/OWa9XdpgCA5dMnZ6BLbTCFTappEwKR2jS6rMnhz8naxYSiyT/9eEJybVGNN5nbQc/kyHzBVyT+Cy/w2++46mjyw0c8Kdf1PVDc5U8ePNAzac1ZuMsC3iKyw6Pg6zX510+46mjy7clWedQxHGnSS7WEadhkvBVx6wAAAscV2cY9MiLdc4lqCXzxsRK3agfxYSbiBIMmpQ22Q3c1X2yCY2/+wFVHk+9xanLzMpOlrjHCZC2m2iyPxbQfNLkSMS+nHTjS5ow2ue+u1jeJB0wqrrsXqlH4EpO5LkeYjEXqClU9N0q2FSpHkmDIpOznIk5mB7MYB5EBk091u3KAvVp3uusXTCqaxGKnuis96+tY2DN5LLKT/fMmtzKHLRPXSjkMmfQRt2qPg1lgaMVjTSaSoXd6ItHkz+hVRqppeF6TksCGudTd+4DJA2JZwZqs8TKTtYhzmKhJmnxAPxSNpsXIuTvqz93DcqwwxMMmY/MNX2gSe1ckcYL/afITphdNAo0uR5pcajPGZNIzOX9Nk0C2cmXWOz3dNQ5NbkabvFcNnzfpPnqKLRVLrGdyex6TQOBKZk9nUzbJvaCyALwy1cVIk4g0fX7PvJZkH9iVsGvX3W5/tVyj8uUMJv19BWSuVLDKq+ma5LpbbXn/3qLt9Nhb28GQScwedwwPYkoyY9UWdwNnvElfbKc/yR46MMVimuheEE2G+U511yww2iRwF6mmeThoEvNZdx8n2yaSOBVOJWGViDvHGUzG20TE38N2mBmTU4l7QROIcY1z233CJfvpO9x43/30bZv05bEaA13Jr/xfdu6nt2kYDAN4Pg6adkA5bT74lkiRAGExAIbQ6640W7O260pRS/fHG4cJBNphlNP4sPhJXDWaaTlMHXHjR82i2trtJ1t+m7y7O9tXas1z9evbe3dMepM7l6oGuXmxglqQm6m+yR/bqha5fXUPkz4PWgv6eqVqkc03zpj0UTXJmtWCvElv0teCamAyImWHzwbjxJusVpR7JjkhzBpnxL3Jh4o3aRPjFNsm2XKTnBww6WwtyJtUfIHAtTHpa0HTqWMmlYiVCokIpAQXFIXE9AfKhFIJEYW5RxLGJIaIYYQ4TOKfQ2MyxlSVTPpMv29cu2YySTRJnt+VgDKABFQlIgxCnb6YvsrrZBTPZggUYbJQzIj5WlCVRCrlmklYihIFSyEoFiYjgXuoP8ZmbO/dhV58AercZM4xiati0teCnjxWTsReJxNCYDIqpOGCVE55Ij1hmcQ2bUyCLUwyylMdk/7cffHlxsF10qyCyNykSvQon6+Itkls16FlMqzUGcebLFS6ZpJrR7GwTHIRUqksZO/dIrL2btyqZ9LXgi6mrtUnYQt/WFI2qShKYBFaRaii2WkGYRTOjkSsGI2FOeMQ/LIan7uDwOpk+t9y7ejvOOAFhkTQVzIZEzeFH/OVEgZxZjCEmAgmBSOwnNeCuHLBpP0q2ORTk5DmeCLlcVqM93Abyf2FPXHvb/Ko0Q78Mxj+uSDbJDICyvNMItB4rm1SfrNNfgiWmzRZbtK0qwy8ScQ/F1R2lsvsZXBII3ncpe4YjYRS2ZGa6Xg0WZ3JvWDv4K8m/XNB3qTp0zKUI9NIaKxNDrMzoqy3XzZp9R89RDvewYkx2OprZqe2yZOB3shPLQD9PiD737sXx5vsmA7R53o+lWlnRMOsuS+XmSy/yhUMBgFycNdkY96z14r7Jl9vqFrk8t0Ka0G2yeaZlF10BerOxmAyld1ORw8u27sHaNpzErSNz36r1QDFskk0tDokOm0HrbU0+fKnqkW2VvuOmN2OJesRTM6d9mRKk7NsuNCkPWBWwnZwx+TnAuPHfNo26Xwt6PmNqkEe/aaHNZmldMck2q2N5YT+YfKosVfax83sYdkkkJr0F5h0PG+f3a69ys2tp7t/2LljnoSBMIzj941g0EjihgacnY3PlRaAWgAKA5EEnBgckKFRGPyuJq9NTmoaJpq79vkv3Af40etdk/eid0HZvdtf6tAXkz4kHYrJkV6eMTkRb1mTg1OTquQmgccnlLznN6DgM05fR3LeXgiAnp6JSSmSRY5JT14VEfw1GatJ1iTyc+QuiBVusqe1D4xTq309NiZlYTK7s3kmdmNjEhPVgTF58uAs9NztdjSZTtH3Q7kzj/Srj6Ue5Zo0M3ED5XW7q45KTQ5kDOQ0Y3Iu83udN8kKvQsCevJGudBSuJajTa5JMxN3paRhalIKspNMEafr3GFVg6qZpEmcNyn7NbCehTqMRsg1mZ2JO++ooffi/a6DoXzT+WcS07hokztYEE26E6PJ9h5WxWjy/trOcaeXq3GA1dFkUv+qlslWLYHV0SRa2wQV6nt7gOXRJB5QpXa3cCGeuxNYFaPJfe2ISnRswI1oEq36TRul7+7dnf8eTSJpfgJXm83mWOKfj2YCR6JJiTGaZDTJGE2yH3bub7VxHAoD+On+Y1kYD2XYZhcPU4PHe+d9Bc/13PQRDjYWSBiwkcFg4offnBO5iqI07qa7idP4A1opyJ8L/JDb0Ojp8eE+eN+5f3h8uiKTC8k/glvIn09XY3LJY3Abebwak0sebsTkw8kmRVnhW7KGEv9NltzfiMn7k01WSh1DOSmufO0nEV5vt4FmdwJA8/eT4FZyskmsoBCnS+J98mSTfb+Y9LOYxBbyt+1up5uEcsIkRy4mX58sfqukaAYmUYOcvcnFZDxqDhFX/6/JEKNLmxygRz+i0QAayv1DShuQVQ/Q1+4p5GvQaI+14IPHi1z4TTbAGQuGXgH00txB9gC58EyKpgDVVwdsC7rfgG6TBNlBUa+VGsajp3VzrSaTzEhJswMmMfpPTYaXNokFCJ+ktmCcQ0ob2M60axIVkFJsGVClgKKFbTpuUpqx3LlD6Zi0TcpDOZ6uWrtNEjoAyBVAYc87yK/WZJLSYIXRhMnrf3YzueHAa7oiDuX+IaUNgJZYF/sn9HXQ2hOpSmgF1jl0tun4s1v2g0DRQf98B6lAOiYZa42iBY0+70aIEhq3SQLkFYAWChAF75aVhgFfzrdvMzYZssU4pe9xRkMkooiriHoTYzJBJJchv7S9Jt1oTmnhCBcR6eU4GS/bFqVbkyvaJyOkHr4uvoDJBhp/nRJGjXNIKcPYEpSuq4qp1DytGCOiKmzTMZMu0ga0MDuua1JtMfZQeybXyOzdJkm3BpBYAtUxxgryl0V++PnrjE0GWcJmHJO0L6a7+6R59oYZb6kBg8KIWY4bbBaOTGNaY0hSd/xsMkoD6mH8yer8JqVvUkJvkTiHlDYgD7vSUI9uWxhjmqZNyp7uYkw25kdwTVYwRnom0Zq0TRI6RNDIJnswKV8WeXc3Z5NsMMLAMYmR/+zmL2aSsK0Ns5XzYOeCNAn4iwmPR5Omla6nzMNkY9XAq0yuoUXUSvCaMaZp0uQAHNdk6ZqU0ybdJsZMAzJZTpj8+svdefLTqSZ5T0sT12SMuPJNJoR3k5gXJwnNeG61PJs06EJaYE1SMfIY40uYbA+Z7GggjEk0OWZSKI015GhVOU3HTRaQ11xmTQ6Q75n0KnyTtsk3KfFo/vr1h3nvk+SHuOyaJFSYHjAZYbidZBE9qyMMbIhevGcyxMjZJ2Nc8ZhxX+DZ3bnM7G+HmDODEuSkSV5ct1BZHV6TF1CuqtqapLq1axJBiUmTtsk1ya0T+fu3H+dtMkIS5JrkuW8ySc0kTsMssG/vmPWeSS60JmlIY9M5j/eCCmixLgFKe0jplEkJrS6Qo6Cv/CYvCjrTC7SoUcZkXm8mJNA12YMeJk2aJs9kBdDUkyq/zdpkgBixKd7WIsQgTO2fOo5J8prgZrLCmOmRuWxl3k3adOyZjAg6WpO8LWNEl9D6c5uUkKOXASgNlO4hpa7JEkyMPg2tvZrSOE1eGqCQpJxHo0mOYn88NItqZccHTdom3yR2JxxeOTeTCT7vc4hphPydphFivGuSQGGa0iSlLZLn2wEvRnef5GrMEmsyyBCTjMYXeS+ohBr9tAUUAxalPaR00mQLzxtu1SuW6DT5aQpDTHQKik7woirXACqvcd8kiryYMGmbPJM4lFdjcvkfjA6XzMjkYrJSWuB8sphcTPL/9J4pEnZyhqZ55uONkPx45s8+LCZPz+8egOXzOJfNki83YvLL1Zhc8vRwG9vk0/WYXPL986d3L/LT5+//sHP+um3rUBw+m1G0Q3HRqUCHdCkMLykMBB6awUA2P8ShFNkmLSdmVDuJ/zR8hRZ+4QvSCo5J2lRSA8W9xflNEiUeafhwCFrwh38Xkxz2BXE4zCSHw0xymEkOh5nkMJMcDjPJYSY5HGaSw0xyOMxkqfIZ/rlwmMmiCXoNoE+rFAfoVvJq/T/DTI7FDbqMxC0uxKgeHNmBXcaIuHBHd/aqO3FZJEg6oU+mK+n8P8ek0xria/Lt3JhuH4+EmawxtDwmmXS5PYHJEztuujoEw3+QSbl87d8vLozLNzwYZvIxwSRBt7DHN3diVd9J85nJHHJ5kMmzd2eHu6RpX+KgZ9oDPBRmcvFyJvFaiDST6wIgV4CBp1eCthbdStqTfUMBbJAcLKTgpUoRkPuKgzLLAap1zaCdnm9CJkkwHLNtn6cxqATaSn3LHHRKC+zK1jO0pjbt5WPrzSc8kHPjlu2u+YU2V+3OT/TCTM6fD/BUJjMCxvP0Sli6MzUjJvfd/koNPQUvVUoySaYN8vTKgEkSDEelMrpAldBJfQtbbOa9UzTdfxzggZy9aX3EKAPTsQeXbdNFm60xW2ZyPz8sbWKKOG1k8ulBTFNMrqH24GLg6ZUAao3DJXz3198hKNKnkYKXKqXX7mo9c7eSpzeDImCSBMMxVMsZ3kMRVNpJfVW5BJnQAmvnTyqzNJP4+W3rHwzTtywOesaYr4f6JDPpyJuLieWQdi9if4+zoG3N+BrpxCeT5GsFhJ5euWuXG9ABV0u3eGcgPQUvVUowGQzUDU9BwCQJhuM+STBRpVrqq1GDTGiBFZSJLRbly/vWuy8Rk1vsd0ynb5k8Emby8UHMcTxpZPIOMcUkKIKEPL2kRfMxog6pcl/BS5XSTM6yHGwICixCxzoJhqM6kuZRpVrqO7RMeu8UOYyTTFI+tN6ehUz2esb0Bphgkplc3a6mOB43rN2jlR1IrN1QEDX6RUxirp7bp/SYLJqYJMeaz2ToWIdGJoNKJKuUKI8zqU9h0ph2H3FwhElm8kbcPYnr6aqRSTsgbpJMVrXV3PP0JpnUsLEiVZKo+pWSTGZQlbR2AxnVfSbRT8wkVYqYpHeK++Rvr90/jekOEPHSXOChMJMoxj8ecC6eRAOTbvgxxeRuI7EGIMQamHSmabUkBW9cKWap9KgaVrBv+c2pZ9NNaSapUsDkcS3wjC68fo+DHXNlD74e/9GcmVwtECdT8djI5EhMrhNMZpANh/cAQJ7eJiaxUBtYk4I3qhRlCcWsnmpvWudQMykRNxVoj0kSDCeYpEoBk5EW+B7y52e7C2X2O78F4dZ0+ji4MB2s993tAE5mciKeEKd2L5P6trjYjS8S3xZ3Et2iAM/TGzCp/W3DGipFs20Kr9Ixd5Wb6qJqJl2KwNNKguEEk1TJZzLSAgOA9l9W7j9Opn8zp5wbm/YVuvwyps1MehmLCSLOX8TkSKwSTKKsQGnMgDy9jUyicj7WQMFLleJsHGW1/VdlZbY7LpT7rhIxibpqYJIqhUzSO4V9Eof3uXtezGTzt0UcbDvGdK+ez86N+be9OzYBEAYCKOo66a60cIMMYeMAbpAZJAsHbFJY2pzy3gYHHw6uOU2mQm9Fk5mwlbZq8kuOZTrfzX1L8Pnhca2MqslMNBnX/mt2N5oETaJJ0CSaBE2CJtEkaBJNgibRJGgSTYImQZNoEjSJJkGTaBI0iSZBk6BJNAmaRJOgSTQJmoQBCqYux9DoIH0AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/MySQL-Derived-Table.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42abcbf0-81a3-4cae-a943-05f85508b7b7",
   "metadata": {
    "tags": []
   },
   "source": [
    "### A simple MySQL derived table example"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "283bdeac-50b6-4f9b-9d41-a3950f4e8192",
   "metadata": {},
   "source": [
    "Get the top five products by sales revenue in 2003 from the `orders` and `orderdetails` tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "edaba773-928e-45f1-a3f3-fef3854f863b",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT \n",
    "    productCode,\n",
    "    ROUND(SUM(quantityOrdered * priceEach)) sales\n",
    "FROM orderdetails\n",
    "INNER JOIN orders USING (orderNumber)\n",
    "WHERE YEAR(shippedDate) = 2003\n",
    "GROUP BY productCode\n",
    "ORDER BY sales DESC\n",
    "LIMIT 5\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "544bb58f-cb67-4944-a5ff-c8fcdee08cfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "def round_sum(s):\n",
    "    return s.sum().round()\n",
    "\n",
    "df2 = (\n",
    "    pd.read_sql_table('orderdetails', engine)\n",
    "    .merge(pd.read_sql_table('orders', engine),\n",
    "           on='orderNumber',\n",
    "           how='inner')\n",
    "    .query('shippedDate.dt.year == 2003')\n",
    "    .assign(sales = lambda df: df.quantityOrdered * df.priceEach)\n",
    "    .groupby('productCode', as_index=False)\n",
    "    .agg({'sales': round_sum})\n",
    "    .sort_values('sales', ascending=False, ignore_index=True)\n",
    "    .head(5)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b5c82b24-7cdd-4045-9f0a-61a3c22523a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5562e233-d3ed-43c3-b7c6-ab1d7c627919",
   "metadata": {},
   "outputs": [
    {
     "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>productCode</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S18_3232</td>\n",
       "      <td>103480.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>67985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>59852.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S12_3891</td>\n",
       "      <td>57403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>56462.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  productCode     sales\n",
       "0    S18_3232  103480.0\n",
       "1    S10_1949   67985.0\n",
       "2    S12_1108   59852.0\n",
       "3    S12_3891   57403.0\n",
       "4    S12_1099   56462.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1a527845-4737-4fbf-917d-bbd66fb7a260",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT productName, sales\n",
    "FROM\n",
    "    (SELECT\n",
    "        productCode,\n",
    "        ROUND(SUM(quantityOrdered * priceEach)) sales\n",
    "    FROM orderdetails\n",
    "    INNER JOIN orders USING (orderNumber)\n",
    "    WHERE YEAR(shippedDate) = 2003\n",
    "    GROUP BY productCode\n",
    "    ORDER BY sales DESC\n",
    "    LIMIT 5) top5product2003\n",
    "INNER JOIN products USING (productCode)\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "985a3017-aff1-491e-bcea-41277f63cedd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def round_sum(s):\n",
    "    return s.sum().round()\n",
    "\n",
    "top5product2003 = (\n",
    "    pd.read_sql_table('orderdetails', engine)\n",
    "    .merge(pd.read_sql_table('orders', engine),\n",
    "           on='orderNumber', how='inner')\n",
    "    .query('shippedDate.dt.year == 2003')\n",
    "    .assign(sales = lambda df: df.quantityOrdered * df.priceEach)\n",
    "    .groupby('productCode', as_index=False)\n",
    "    .agg({'sales': round_sum})\n",
    "    .sort_values('sales', ascending=False, ignore_index=True)\n",
    "    .head(5)\n",
    ")\n",
    "\n",
    "df2 = (\n",
    "    top5product2003\n",
    "    .merge(pd.read_sql_table('products', engine),\n",
    "           on = 'productCode', how='inner')\n",
    "    [['productName', 'sales']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a5087a17-2acc-4c51-9580-00c147d79744",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "257cbd50-9c61-416d-917f-1d6af209be3e",
   "metadata": {},
   "outputs": [
    {
     "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>productName</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1992 Ferrari 360 Spider red</td>\n",
       "      <td>103480.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>67985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>59852.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1969 Ford Falcon</td>\n",
       "      <td>57403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>56462.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   productName     sales\n",
       "0  1992 Ferrari 360 Spider red  103480.0\n",
       "1     1952 Alpine Renault 1300   67985.0\n",
       "2            2001 Ferrari Enzo   59852.0\n",
       "3             1969 Ford Falcon   57403.0\n",
       "4            1968 Ford Mustang   56462.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd2b1059-8cbd-4cfe-b5f4-7b09c2f6fa39",
   "metadata": {
    "tags": []
   },
   "source": [
    "### A more complex MySQL derived table example"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55664598-9505-4867-90ef-caaad8028af9",
   "metadata": {},
   "source": [
    "- Platinum customers who have orders with the volumn > 100K\n",
    "- Gold customers who have orders with the volumn between 10K and 100K\n",
    "- Silver customers who have orders with the volumn < 10K"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0ff1f6e6-ebd4-46af-a4ef-7cc4e46b9088",
   "metadata": {},
   "outputs": [
    {
     "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>customerNumber</th>\n",
       "      <th>sales</th>\n",
       "      <th>customerGroup</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>363</td>\n",
       "      <td>65650.0</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>128</td>\n",
       "      <td>34651.0</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>181</td>\n",
       "      <td>5495.0</td>\n",
       "      <td>Silver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>121</td>\n",
       "      <td>51710.0</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>141</td>\n",
       "      <td>189840.0</td>\n",
       "      <td>Platinum</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber     sales customerGroup\n",
       "0             363   65650.0          Gold\n",
       "1             128   34651.0          Gold\n",
       "2             181    5495.0        Silver\n",
       "3             121   51710.0          Gold\n",
       "4             141  189840.0      Platinum"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    customerNumber,\n",
    "    ROUND(SUM(quantityOrdered * priceEach)) sales,\n",
    "    (CASE\n",
    "        WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'\n",
    "        WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'\n",
    "        WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'\n",
    "    END) customerGroup\n",
    "FROM orderdetails\n",
    "INNER JOIN orders USING (orderNumber)\n",
    "WHERE YEAR(shippedDate) = 2003\n",
    "GROUP BY customerNumber\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "aeacb7ff-8edf-4737-9b02-38ac27426b7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%timeit\n",
    "# 5.77 ms ± 295 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
    "string = '''\n",
    "SELECT \n",
    "    customerGroup,\n",
    "    COUNT(cg.customerGroup) AS groupCount\n",
    "FROM\n",
    "    (SELECT\n",
    "        customerNumber,\n",
    "        ROUND(SUM(quantityOrdered * priceEach)) sales,\n",
    "        (CASE\n",
    "            WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'\n",
    "            WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'\n",
    "            WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'\n",
    "        END) customerGroup\n",
    "    FROM orderdetails\n",
    "    INNER JOIN orders USING (orderNumber)\n",
    "    WHERE YEAR(shippedDate) = 2003\n",
    "    GROUP BY customerNumber) cg\n",
    "GROUP BY cg.customerGroup\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d98e5c1b-e6dc-444f-ae18-9448ce81803c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%timeit\n",
    "# 154 ms ± 11 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n",
    "def round_sum(s):\n",
    "    return s.sum().round()\n",
    "\n",
    "def group(s):\n",
    "    condlist = [s < 10000, s > 100000]\n",
    "    choicelist = ['Silver', 'Platinum']\n",
    "    default = 'Gold'\n",
    "    return np.select(condlist, choicelist, default)\n",
    "\n",
    "cg = (\n",
    "    pd.read_sql_table('orderdetails', engine)\n",
    "    .merge(pd.read_sql_table('orders', engine),\n",
    "           on='orderNumber', how='inner')\n",
    "    .query('shippedDate.dt.year == 2003')\n",
    "    .assign(sales = lambda df: df.quantityOrdered * df.priceEach)\n",
    "    .groupby('customerNumber', as_index=False)\n",
    "    .agg({'sales': round_sum})\n",
    "    .assign(customerGroup = lambda df: group(df.sales))\n",
    "    [['customerGroup']]\n",
    "    .value_counts()\n",
    ")\n",
    "\n",
    "df2 = pd.DataFrame(\n",
    "    cg\n",
    "    .rename('groupCount')\n",
    "    .reset_index()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "163dc304-61e9-4952-9c3d-8ab13f2333f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "38f6bc59-5b25-46ff-b0a2-d04273426d9e",
   "metadata": {},
   "outputs": [
    {
     "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>customerGroup</th>\n",
       "      <th>groupCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gold</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Silver</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Platinum</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  customerGroup  groupCount\n",
       "0          Gold          61\n",
       "1        Silver           8\n",
       "2      Platinum           4"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  }
 ],
 "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.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}