windowfunctions-RANK.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load SQL Magics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip3 install --user openpyxl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load sqlalchemy to create a local environment of PostgreSQL server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %sql dialect+driver://username:password@host:port/database\n",
    "%sql postgresql://jovyan:postgres@localhost:8765/rsm-docker"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"postgresql://jovyan:postgres@localhost:8765/rsm-docker\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(postgresql://jovyan:***@localhost:8765/rsm-docker)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get files as data frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "accounts = pd.read_excel(\"data/accounts.xlsx\")\n",
    "orders = pd.read_excel(\"data/orders.xlsx\")\n",
    "region = pd.read_excel(\"data/region.xlsx\")\n",
    "sales_reps = pd.read_excel(\"data/sales_reps.xlsx\")\n",
    "web_events = pd.read_excel(\"data/web_events.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data ingestion to tables in a database (here - rsm-docker)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "InternalError",
     "evalue": "(psycopg2.errors.DependentObjectsStillExist) cannot drop table orders because other objects depend on it\nDETAIL:  view total_orders_ranked depends on table orders\nHINT:  Use DROP ... CASCADE to drop the dependent objects too.\n\n[SQL: \nDROP TABLE orders]\n(Background on this error at: http://sqlalche.me/e/14/2j85)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mDependentObjectsStillExist\u001b[0m                Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1704\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1705\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1706\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    691\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 692\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    693\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDependentObjectsStillExist\u001b[0m: cannot drop table orders because other objects depend on it\nDETAIL:  view total_orders_ranked depends on table orders\nHINT:  Use DROP ... CASCADE to drop the dependent objects too.\n",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mInternalError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-8-a39e8aa89954>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0maccounts\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"accounts\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"replace\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0morders\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"orders\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"replace\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0mregion\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"region\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"replace\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0msales_reps\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"sales_reps\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"replace\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mweb_events\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"web_events\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"replace\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   2777\u001b[0m         \u001b[0;32mfrom\u001b[0m \u001b[0mpandas\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mio\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2778\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2779\u001b[0;31m         sql.to_sql(\n\u001b[0m\u001b[1;32m   2780\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2781\u001b[0m             \u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m    599\u001b[0m         )\n\u001b[1;32m    600\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 601\u001b[0;31m     pandas_sql.to_sql(\n\u001b[0m\u001b[1;32m    602\u001b[0m         \u001b[0mframe\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    603\u001b[0m         \u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   1404\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1405\u001b[0m         )\n\u001b[0;32m-> 1406\u001b[0;31m         \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1407\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1408\u001b[0m         \u001b[0;32mfrom\u001b[0m \u001b[0msqlalchemy\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mcreate\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    738\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"Table '{self.name}' already exists.\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    739\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"replace\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 740\u001b[0;31m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpd_sql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    741\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_create\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    742\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"append\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mdrop_table\u001b[0;34m(self, table_name, schema)\u001b[0m\n\u001b[1;32m   1479\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhas_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtable_name\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1480\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreflect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0monly\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mtable_name\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1481\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtable_name\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1482\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclear\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1483\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/schema.py\u001b[0m in \u001b[0;36mdrop\u001b[0;34m(self, bind, checkfirst)\u001b[0m\n\u001b[1;32m    958\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mbind\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    959\u001b[0m             \u001b[0mbind\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_bind_or_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 960\u001b[0;31m         \u001b[0mbind\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_run_ddl_visitor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mddl\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSchemaDropper\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcheckfirst\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcheckfirst\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    961\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    962\u001b[0m     @util.deprecated(\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_run_ddl_visitor\u001b[0;34m(self, visitorcallable, element, **kwargs)\u001b[0m\n\u001b[1;32m   3006\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_run_ddl_visitor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvisitorcallable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0melement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3007\u001b[0m         \u001b[0;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbegin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3008\u001b[0;31m             \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_run_ddl_visitor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvisitorcallable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0melement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3009\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3010\u001b[0m     @util.deprecated_20(\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_run_ddl_visitor\u001b[0;34m(self, visitorcallable, element, **kwargs)\u001b[0m\n\u001b[1;32m   2014\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2015\u001b[0m         \"\"\"\n\u001b[0;32m-> 2016\u001b[0;31m         \u001b[0mvisitorcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtraverse_single\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0melement\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2017\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2018\u001b[0m     @util.deprecated(\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/visitors.py\u001b[0m in \u001b[0;36mtraverse_single\u001b[0;34m(self, obj, **kw)\u001b[0m\n\u001b[1;32m    481\u001b[0m             \u001b[0mmeth\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mv\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"visit_%s\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__visit_name__\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    482\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 483\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    484\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    485\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0miterate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mobj\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/ddl.py\u001b[0m in \u001b[0;36mvisit_table\u001b[0;34m(self, table, drop_ok, _is_metadata_operation, _ignore_sequences)\u001b[0m\n\u001b[1;32m   1095\u001b[0m         )\n\u001b[1;32m   1096\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1097\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mDropTable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1098\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1099\u001b[0m         \u001b[0;31m# traverse client side defaults which may refer to server-side\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   1198\u001b[0m             )\n\u001b[1;32m   1199\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1200\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_EMPTY_EXECUTION_OPTS\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1201\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1202\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/ddl.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params, execution_options)\u001b[0m\n\u001b[1;32m     75\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     76\u001b[0m     ):\n\u001b[0;32m---> 77\u001b[0;31m         return connection._execute_ddl(\n\u001b[0m\u001b[1;32m     78\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     79\u001b[0m         )\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_ddl\u001b[0;34m(self, ddl, multiparams, params, execution_options)\u001b[0m\n\u001b[1;32m   1288\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema_translate_map\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mschema_translate_map\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1289\u001b[0m         )\n\u001b[0;32m-> 1290\u001b[0;31m         ret = self._execute_context(\n\u001b[0m\u001b[1;32m   1291\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1292\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_init_ddl\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1746\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1747\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1748\u001b[0;31m             self._handle_dbapi_exception(\n\u001b[0m\u001b[1;32m   1749\u001b[0m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1750\u001b[0m             )\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1927\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1928\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1929\u001b[0;31m                 util.raise_(\n\u001b[0m\u001b[1;32m   1930\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1931\u001b[0m                 )\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    209\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    210\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 211\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    212\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    213\u001b[0m             \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1703\u001b[0m                             \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1704\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1705\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1706\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1707\u001b[0m                     )\n",
      "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    690\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    691\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 692\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    693\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    694\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mInternalError\u001b[0m: (psycopg2.errors.DependentObjectsStillExist) cannot drop table orders because other objects depend on it\nDETAIL:  view total_orders_ranked depends on table orders\nHINT:  Use DROP ... CASCADE to drop the dependent objects too.\n\n[SQL: \nDROP TABLE orders]\n(Background on this error at: http://sqlalche.me/e/14/2j85)"
     ]
    }
   ],
   "source": [
    "accounts.to_sql(\"accounts\", engine, if_exists=\"replace\")\n",
    "orders.to_sql(\"orders\", engine, if_exists=\"replace\")\n",
    "region.to_sql(\"region\", engine, if_exists=\"replace\")\n",
    "sales_reps.to_sql(\"sales_reps\", engine, if_exists=\"replace\")\n",
    "web_events.to_sql(\"web_events\", engine, if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Ranking Total Paper Ordered by Account: Select the id, account_id, and total variable from the orders table, then create a column called total_rank that ranks this total amount of paper ordered (from highest to lowest) for each account using a partition. Your final table should have these four columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Limited to 100 rows to avoid scrolling. Total 6912 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jovyan:***@localhost:8765/rsm-docker\n",
      "100 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>account_id</th>\n",
       "        <th>total</th>\n",
       "        <th>total_rank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4308</td>\n",
       "        <td>1001</td>\n",
       "        <td>1410</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4309</td>\n",
       "        <td>1001</td>\n",
       "        <td>1405</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4316</td>\n",
       "        <td>1001</td>\n",
       "        <td>1384</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4317</td>\n",
       "        <td>1001</td>\n",
       "        <td>1347</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4314</td>\n",
       "        <td>1001</td>\n",
       "        <td>1343</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4307</td>\n",
       "        <td>1001</td>\n",
       "        <td>1321</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4311</td>\n",
       "        <td>1001</td>\n",
       "        <td>1307</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4310</td>\n",
       "        <td>1001</td>\n",
       "        <td>1280</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4312</td>\n",
       "        <td>1001</td>\n",
       "        <td>1267</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4313</td>\n",
       "        <td>1001</td>\n",
       "        <td>1254</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4315</td>\n",
       "        <td>1001</td>\n",
       "        <td>1238</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4318</td>\n",
       "        <td>1001</td>\n",
       "        <td>1205</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>1001</td>\n",
       "        <td>294</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1001</td>\n",
       "        <td>293</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1001</td>\n",
       "        <td>288</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>1001</td>\n",
       "        <td>269</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>1001</td>\n",
       "        <td>226</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>1001</td>\n",
       "        <td>210</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>1001</td>\n",
       "        <td>196</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1001</td>\n",
       "        <td>176</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>1001</td>\n",
       "        <td>173</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1001</td>\n",
       "        <td>169</td>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1001</td>\n",
       "        <td>165</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>1001</td>\n",
       "        <td>158</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>1001</td>\n",
       "        <td>148</td>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1001</td>\n",
       "        <td>137</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1001</td>\n",
       "        <td>132</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1001</td>\n",
       "        <td>129</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>1011</td>\n",
       "        <td>541</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>1021</td>\n",
       "        <td>578</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>1021</td>\n",
       "        <td>569</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>1021</td>\n",
       "        <td>558</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>1021</td>\n",
       "        <td>539</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>1021</td>\n",
       "        <td>515</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>1021</td>\n",
       "        <td>504</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4319</td>\n",
       "        <td>1021</td>\n",
       "        <td>347</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4322</td>\n",
       "        <td>1021</td>\n",
       "        <td>102</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4320</td>\n",
       "        <td>1021</td>\n",
       "        <td>66</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4321</td>\n",
       "        <td>1021</td>\n",
       "        <td>32</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>1031</td>\n",
       "        <td>1363</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4324</td>\n",
       "        <td>1041</td>\n",
       "        <td>791</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4323</td>\n",
       "        <td>1041</td>\n",
       "        <td>525</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>1041</td>\n",
       "        <td>395</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>1041</td>\n",
       "        <td>329</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>1041</td>\n",
       "        <td>212</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4327</td>\n",
       "        <td>1051</td>\n",
       "        <td>648</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>1051</td>\n",
       "        <td>595</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>1051</td>\n",
       "        <td>589</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>1051</td>\n",
       "        <td>542</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>1051</td>\n",
       "        <td>508</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>1051</td>\n",
       "        <td>497</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>1051</td>\n",
       "        <td>487</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4326</td>\n",
       "        <td>1051</td>\n",
       "        <td>150</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4325</td>\n",
       "        <td>1051</td>\n",
       "        <td>104</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4329</td>\n",
       "        <td>1061</td>\n",
       "        <td>830</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4330</td>\n",
       "        <td>1061</td>\n",
       "        <td>828</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4328</td>\n",
       "        <td>1061</td>\n",
       "        <td>770</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>1061</td>\n",
       "        <td>365</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>1061</td>\n",
       "        <td>330</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>1061</td>\n",
       "        <td>269</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>1071</td>\n",
       "        <td>853</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4331</td>\n",
       "        <td>1071</td>\n",
       "        <td>725</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4337</td>\n",
       "        <td>1081</td>\n",
       "        <td>1592</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4335</td>\n",
       "        <td>1081</td>\n",
       "        <td>1311</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4333</td>\n",
       "        <td>1081</td>\n",
       "        <td>1200</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4346</td>\n",
       "        <td>1081</td>\n",
       "        <td>1051</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4344</td>\n",
       "        <td>1081</td>\n",
       "        <td>987</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4343</td>\n",
       "        <td>1081</td>\n",
       "        <td>919</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4339</td>\n",
       "        <td>1081</td>\n",
       "        <td>886</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4336</td>\n",
       "        <td>1081</td>\n",
       "        <td>858</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4334</td>\n",
       "        <td>1081</td>\n",
       "        <td>848</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4338</td>\n",
       "        <td>1081</td>\n",
       "        <td>839</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4341</td>\n",
       "        <td>1081</td>\n",
       "        <td>834</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4340</td>\n",
       "        <td>1081</td>\n",
       "        <td>827</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4332</td>\n",
       "        <td>1081</td>\n",
       "        <td>820</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4342</td>\n",
       "        <td>1081</td>\n",
       "        <td>778</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4345</td>\n",
       "        <td>1081</td>\n",
       "        <td>763</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>1081</td>\n",
       "        <td>542</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>1081</td>\n",
       "        <td>542</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>1081</td>\n",
       "        <td>524</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>1081</td>\n",
       "        <td>465</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>1081</td>\n",
       "        <td>389</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>1081</td>\n",
       "        <td>379</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>1081</td>\n",
       "        <td>376</td>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>1081</td>\n",
       "        <td>375</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>1081</td>\n",
       "        <td>375</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>1081</td>\n",
       "        <td>374</td>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>1081</td>\n",
       "        <td>367</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>1081</td>\n",
       "        <td>349</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>1081</td>\n",
       "        <td>349</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>1081</td>\n",
       "        <td>343</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>1081</td>\n",
       "        <td>341</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>1081</td>\n",
       "        <td>334</td>\n",
       "        <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>1081</td>\n",
       "        <td>333</td>\n",
       "        <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>1081</td>\n",
       "        <td>331</td>\n",
       "        <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>1081</td>\n",
       "        <td>330</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>1081</td>\n",
       "        <td>327</td>\n",
       "        <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>1081</td>\n",
       "        <td>322</td>\n",
       "        <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>1081</td>\n",
       "        <td>322</td>\n",
       "        <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4352</td>\n",
       "        <td>1091</td>\n",
       "        <td>399</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4308, 1001, 1410, 1),\n",
       " (4309, 1001, 1405, 2),\n",
       " (4316, 1001, 1384, 3),\n",
       " (4317, 1001, 1347, 4),\n",
       " (4314, 1001, 1343, 5),\n",
       " (4307, 1001, 1321, 6),\n",
       " (4311, 1001, 1307, 7),\n",
       " (4310, 1001, 1280, 8),\n",
       " (4312, 1001, 1267, 9),\n",
       " (4313, 1001, 1254, 10),\n",
       " (4315, 1001, 1238, 11),\n",
       " (4318, 1001, 1205, 12),\n",
       " (14, 1001, 294, 13),\n",
       " (8, 1001, 293, 14),\n",
       " (2, 1001, 288, 15),\n",
       " (16, 1001, 269, 16),\n",
       " (7, 1001, 226, 17),\n",
       " (15, 1001, 210, 18),\n",
       " (12, 1001, 196, 19),\n",
       " (4, 1001, 176, 20),\n",
       " (6, 1001, 173, 21),\n",
       " (1, 1001, 169, 22),\n",
       " (5, 1001, 165, 23),\n",
       " (13, 1001, 158, 24),\n",
       " (10, 1001, 148, 25),\n",
       " (11, 1001, 137, 26),\n",
       " (3, 1001, 132, 27),\n",
       " (9, 1001, 129, 28),\n",
       " (17, 1011, 541, 1),\n",
       " (23, 1021, 578, 1),\n",
       " (21, 1021, 569, 2),\n",
       " (19, 1021, 558, 3),\n",
       " (18, 1021, 539, 4),\n",
       " (22, 1021, 515, 5),\n",
       " (20, 1021, 504, 6),\n",
       " (4319, 1021, 347, 7),\n",
       " (4322, 1021, 102, 8),\n",
       " (4320, 1021, 66, 9),\n",
       " (4321, 1021, 32, 10),\n",
       " (24, 1031, 1363, 1),\n",
       " (4324, 1041, 791, 1),\n",
       " (4323, 1041, 525, 2),\n",
       " (25, 1041, 395, 3),\n",
       " (26, 1041, 329, 4),\n",
       " (27, 1041, 212, 5),\n",
       " (4327, 1051, 648, 1),\n",
       " (31, 1051, 595, 2),\n",
       " (28, 1051, 589, 3),\n",
       " (29, 1051, 542, 4),\n",
       " (32, 1051, 508, 5),\n",
       " (33, 1051, 497, 6),\n",
       " (30, 1051, 487, 7),\n",
       " (4326, 1051, 150, 8),\n",
       " (4325, 1051, 104, 9),\n",
       " (4329, 1061, 830, 1),\n",
       " (4330, 1061, 828, 2),\n",
       " (4328, 1061, 770, 3),\n",
       " (34, 1061, 365, 4),\n",
       " (36, 1061, 330, 5),\n",
       " (35, 1061, 269, 6),\n",
       " (37, 1071, 853, 1),\n",
       " (4331, 1071, 725, 2),\n",
       " (4337, 1081, 1592, 1),\n",
       " (4335, 1081, 1311, 2),\n",
       " (4333, 1081, 1200, 3),\n",
       " (4346, 1081, 1051, 4),\n",
       " (4344, 1081, 987, 5),\n",
       " (4343, 1081, 919, 6),\n",
       " (4339, 1081, 886, 7),\n",
       " (4336, 1081, 858, 8),\n",
       " (4334, 1081, 848, 9),\n",
       " (4338, 1081, 839, 10),\n",
       " (4341, 1081, 834, 11),\n",
       " (4340, 1081, 827, 12),\n",
       " (4332, 1081, 820, 13),\n",
       " (4342, 1081, 778, 14),\n",
       " (4345, 1081, 763, 15),\n",
       " (51, 1081, 542, 16),\n",
       " (48, 1081, 542, 16),\n",
       " (43, 1081, 524, 18),\n",
       " (56, 1081, 465, 19),\n",
       " (42, 1081, 389, 20),\n",
       " (58, 1081, 379, 21),\n",
       " (52, 1081, 376, 22),\n",
       " (46, 1081, 375, 23),\n",
       " (54, 1081, 375, 23),\n",
       " (53, 1081, 374, 25),\n",
       " (38, 1081, 367, 26),\n",
       " (47, 1081, 349, 27),\n",
       " (49, 1081, 349, 27),\n",
       " (45, 1081, 343, 29),\n",
       " (50, 1081, 341, 30),\n",
       " (39, 1081, 334, 31),\n",
       " (40, 1081, 333, 32),\n",
       " (57, 1081, 331, 33),\n",
       " (44, 1081, 330, 34),\n",
       " (55, 1081, 327, 35),\n",
       " (59, 1081, 322, 36),\n",
       " (41, 1081, 322, 36),\n",
       " (4352, 1091, 399, 1)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT id, account_id, total,\n",
    "RANK() OVER(PARTITION BY account_id ORDER BY total desc) total_rank\n",
    "FROM orders\n",
    "LIMIT 100; "
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}