Analytic-training.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9129cdcb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import configparser\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "10b493f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['clustertab.config']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('clustertab.config')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a471cc22",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = config['POSTGRES']['PG_DB']\n",
    "user = config['POSTGRES']['PG_UNAME']\n",
    "passwd = config['POSTGRES']['PG_PASS']\n",
    "port = config['POSTGRES']['PG_PORT']\n",
    "host = config['POSTGRES']['PG_HOST']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4216af6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'retail_db'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "623308f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "credentials = \"postgresql://{}:{}@{}:{}/{}\".format(user,passwd,host,port,db)\n",
    "\n",
    "#using psycopg2 to test connection since there are no tables\n",
    "import psycopg2\n",
    "try:\n",
    "    conn = psycopg2.connect(host=host,dbname=db,user=user,password=passwd,port=port)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "conn.set_session(autocommit=True)\n",
    "\n",
    "try:\n",
    "    cur = conn.cursor()\n",
    "    \n",
    "except:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6846fc00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'postgresql://postgres:1234@172.17.0.2:5432/retail_db'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "77f47076",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Helper functions to work with the database\n",
    "def schemaGen(dataframe, schemaName):\n",
    "    localSchema = pd.io.sql.get_schema(dataframe,schemaName)\n",
    "    localSchema = localSchema.replace('TEXT','VARCHAR(255)').replace('INTEGER','NUMERIC').replace('\\n','').replace('\"',\"\")\n",
    "    return \"\".join(localSchema)\n",
    "\n",
    "#Using pandas read_sql for getting schema\n",
    "def getSchema(tableName, credentials):\n",
    "    schema = pd.read_sql(\"\"\"SELECT * FROM information_schema.columns where table_name='{}'\"\"\".format(tableName),con=credentials)\n",
    "    return schema\n",
    "\n",
    "#Issue is in using pd.read_sql to write data to the database. so using psycopg2\n",
    "def queryTable(query):\n",
    "    try:\n",
    "        schema = cur.execute(query)\n",
    "        return \n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        \n",
    "#This doesn't return anything\n",
    "\n",
    "#Using the pd.read_sql for getting data from db\n",
    "def queryBase(query):\n",
    "    requiredTable = pd.read_sql(query,con=credentials)\n",
    "    return requiredTable\n",
    "\n",
    "#This returns the dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6544f6a2",
   "metadata": {},
   "source": [
    "## Pivoting Rows into Columns\n",
    "\n",
    "Let us understand how we can pivot rows into columns in Postgres.\n",
    "\n",
    "* We need to use `crosstab` as part of `FROM` clause to pivot the data. We need to pass the main query to `crosstab` function.\n",
    "* We need to install `tablefunc` as Postgres superuser to expose functions like crosstab - `CREATE EXTENSION tablefunc;`\n",
    "\n",
    "```{note}\n",
    "If you are using environment provided by us, you don't need to install `tablefunc`. If you are using your own environment run this command by logging in as superuser into postgres server to install `tablefunc`.\n",
    "\n",
    "`CREATE EXTENSION tablefunc;`\n",
    "\n",
    "However, in some cases you might have to run scripts in postgres. Follow official instructions by searching around.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "eea14b4a",
   "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>order_date</th>\n",
       "      <th>order_status</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>CANCELED</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>COMPLETE</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>ON_HOLD</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>PAYMENT_REVIEW</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>PROCESSING</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>SUSPECTED_FRAUD</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>CANCELED</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>COMPLETE</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>ON_HOLD</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>PAYMENT_REVIEW</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>PROCESSING</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>SUSPECTED_FRAUD</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date     order_status  count\n",
       "0  2013-07-25         CANCELED      1\n",
       "1  2013-07-25           CLOSED     20\n",
       "2  2013-07-25         COMPLETE     42\n",
       "3  2013-07-25          ON_HOLD      5\n",
       "4  2013-07-25   PAYMENT_REVIEW      3\n",
       "5  2013-07-25          PENDING     13\n",
       "6  2013-07-25  PENDING_PAYMENT     41\n",
       "7  2013-07-25       PROCESSING     16\n",
       "8  2013-07-25  SUSPECTED_FRAUD      2\n",
       "9  2013-07-26         CANCELED      3\n",
       "10 2013-07-26           CLOSED     29\n",
       "11 2013-07-26         COMPLETE     87\n",
       "12 2013-07-26          ON_HOLD     19\n",
       "13 2013-07-26   PAYMENT_REVIEW      6\n",
       "14 2013-07-26          PENDING     31\n",
       "15 2013-07-26  PENDING_PAYMENT     59\n",
       "16 2013-07-26       PROCESSING     30\n",
       "17 2013-07-26  SUSPECTED_FRAUD      5"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT order_date,\n",
    "    order_status,\n",
    "    count(1)\n",
    "FROM orders\n",
    "GROUP BY order_date,\n",
    "    order_status\n",
    "ORDER BY order_date,\n",
    "    order_status\n",
    "LIMIT 18\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "46224ae2",
   "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>order_date</th>\n",
       "      <th>CANCELED</th>\n",
       "      <th>CLOSED</th>\n",
       "      <th>COMPLETE</th>\n",
       "      <th>ON_HOLD</th>\n",
       "      <th>PAYMENT_REVIEW</th>\n",
       "      <th>PENDING</th>\n",
       "      <th>PENDING_PAYMENT</th>\n",
       "      <th>PROCESSING</th>\n",
       "      <th>SUSPECTED_FRAUD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-04-26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-04-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-08-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-01-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-06-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-09-14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2014-07-06</td>\n",
       "      <td>3.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2014-02-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2014-05-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date  CANCELED CLOSED  COMPLETE  ON_HOLD  PAYMENT_REVIEW PENDING  \\\n",
       "0  2014-04-26       NaN   None       NaN      NaN             NaN    None   \n",
       "1  2014-04-21       NaN   None       NaN      NaN             4.0    None   \n",
       "2  2013-08-15       NaN   None       NaN      NaN             NaN    None   \n",
       "3  2014-01-20       NaN   None       NaN     12.0             NaN    None   \n",
       "4  2014-06-03       NaN   None       NaN      NaN             NaN    None   \n",
       "5  2013-09-14       NaN   None       NaN      NaN             NaN    None   \n",
       "6  2014-07-06       3.0   None       NaN      NaN             NaN    None   \n",
       "7  2014-02-11       NaN   None       NaN      NaN             NaN    None   \n",
       "8  2014-05-07       NaN   None      64.0      NaN             NaN    None   \n",
       "9  2013-12-30       NaN   None       NaN      NaN             NaN    None   \n",
       "\n",
       "   PENDING_PAYMENT  PROCESSING  SUSPECTED_FRAUD  \n",
       "0              NaN        33.0              NaN  \n",
       "1              NaN         NaN              NaN  \n",
       "2             40.0         NaN              NaN  \n",
       "3              NaN         NaN              NaN  \n",
       "4              NaN         NaN              4.0  \n",
       "5              NaN        37.0              NaN  \n",
       "6              NaN         NaN              NaN  \n",
       "7              NaN        25.0              NaN  \n",
       "8              NaN         NaN              NaN  \n",
       "9              NaN         NaN              1.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM crosstab(\n",
    "    'SELECT order_date,\n",
    "        order_status,\n",
    "        count(1) AS order_count\n",
    "    FROM orders\n",
    "    GROUP BY order_date,\n",
    "        order_status',\n",
    "    'SELECT DISTINCT order_status FROM orders ORDER BY 1'\n",
    ") AS (\n",
    "    order_date DATE,\n",
    "    \"CANCELED\" INT,\n",
    "    \"CLOSED\" INT,\n",
    "    \"COMPLETE\" INT,\n",
    "    \"ON_HOLD\" INT,\n",
    "    \"PAYMENT_REVIEW\" INT,\n",
    "    \"PENDING\" INT,\n",
    "    \"PENDING_PAYMENT\" INT,\n",
    "    \"PROCESSING\" INT,\n",
    "    \"SUSPECTED_FRAUD\" INT\n",
    ")\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c10f2c66",
   "metadata": {},
   "source": [
    "## Overview of Analytic Functions\n",
    "\n",
    "Let us get an overview of Analytics or Windowing Functions as part of **SQL**.\n",
    "\n",
    "* Aggregate Functions (`sum`, `min`, `max`, `avg`)\n",
    "* Window Functions (`lead`, `lag`, `first_value`, `last_value`)\n",
    "* Rank Functions (`rank`, `dense_rank`, `row_number` etc)\n",
    "* For all the functions when used as part of Analytic or Windowing functions we use `OVER` clause.\n",
    "* For aggregate functions we typically use `PARTITION BY`\n",
    "* For global ranking and windowing functions we can use `ORDER BY sort_column` and for ranking and windowing with in a partition or group we can use `PARTITION BY partition_column ORDER BY sort_column`.\n",
    "* Here is how the syntax will look like.\n",
    "  * Aggregate - `func() OVER (PARTITION BY partition_column)`\n",
    "  * Global Rank - `func() OVER (ORDER BY sort_column DESC)`\n",
    "  * Rank in a partition - `func() OVER (PARTITION BY partition_column ORDER BY sort_column DESC)`\n",
    "* We can also get cumulative or moving metrics by adding `ROWS BETWEEN` clause. We will see details later.\n",
    "\n",
    "## Analytic Functions – Aggregations\n",
    "\n",
    "Let us see how we can perform aggregations with in a partition or group using Windowing/Analytics Functions.\n",
    "\n",
    "* For simple aggregations where we have to get grouping key and aggregated results we can use **GROUP BY**.\n",
    "* If we want to get the raw data along with aggregated results, then using **GROUP BY** is not possible or overly complicated.\n",
    "* Using aggregate functions with **OVER** Clause not only simplifies the process of writing query, but also better with respect to performance.\n",
    "* Let us take an example of getting employee salary percentage when compared to department salary expense.\n",
    "\n",
    "## Cumulative or Moving Aggregations\n",
    "\n",
    "Let us understand how we can take care of cumulative or moving aggregations using Analytic Functions.\n",
    "\n",
    "* When it comes to Windowing or Analytic Functions we can also specify window spec using `ROWS BETWEEN` clause.\n",
    "* Even when we do not specify window spec, the default window spec is used. For most of the functions the default window spec is `UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`. You also have special clauses such as `CURRENT ROW`.\n",
    "* Here are some of the examples with respect to `ROWS BETWEEN`.\n",
    "  * `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`\n",
    "  * `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`\n",
    "  * `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`\n",
    "  * `ROWS BETWEEN 3 PRECEDING AND CURRENT ROW` - moving aggregations using current record and previous 3 records.\n",
    "  * `ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING` - moving aggregations using current record and following 3 records.\n",
    "  * `ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING` - moving aggregations based up on 7 records (current record, 3 previous records and 3 following records)\n",
    "* We can leverage `ROWS BETWEEN` for cumulative aggregations or moving aggregations.\n",
    "* Here is an example of cumulative sum.\n",
    "\n",
    "## Analytic Functions – Windowing\n",
    "\n",
    "Let us go through the list of Windowing functions supported by Postgres.\n",
    "\n",
    "* `lead` and `lag`\n",
    "* `first_value` and `last_value`\n",
    "* We can either use `ORDER BY sort_column` or `PARTITION BY partition_column ORDER BY sort_column` while using Windowing Functions.\n",
    "\n",
    "The lead and lag also needs additional work.. Same like Merging\n",
    "\n",
    "## Analytic Functions – Ranking\n",
    "\n",
    "Let us see how we can assign ranks using different **rank** functions.\n",
    "\n",
    "* If we have to assign ranks globally, we just need to specify **ORDER BY**\n",
    "* If we have to assign ranks with in a key then we need to specify **PARTITION BY** and then **ORDER BY**.\n",
    "* By default **ORDER BY** will sort the data in ascending order. We can change the order by passing **DESC** after order by.\n",
    "* We have 3 main functions to assign ranks - `rank`, `dense_rank` and `row_number`. We will see the differences between the 3 in a moment.\n",
    "\n",
    " Let us understand the difference between **rank**, **dense_rank** and **row_number**.\n",
    "\n",
    "* We can use either of the functions to generate ranks when the rank field does not have duplicates.\n",
    "* When rank field have duplicates then row_number should not be used as it generate unique number for each record with in the partition.\n",
    "* **rank** will skip the ranks in between if multiple people get the same rank while **dense_rank** continue with the next number.\n",
    "\n",
    "## Analytic Functions - Filtering\n",
    "\n",
    "Let us go through the solution for getting top 5 daily products based up on the revenue. In that process we will understand how to apply filtering on top of the derived values using analytic functions.\n",
    "\n",
    "## Ranking and Filtering - Recap\n",
    "\n",
    "Let us recap the procedure to get top 5 products by revenue for each day.\n",
    "\n",
    "* We have our original data in **orders** and **order_items**\n",
    "* We can pre-compute the data or create a view with the logic to generate **daily product revenue**\n",
    "* Then, we have to use the view or table or even sub query to compute rank\n",
    "* Once the ranks are computed, we need to use sub query to filter based up on our requirement.\n",
    "\n",
    "Let us come up with the query to compute daily product revenue."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fe750787",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE daily_revenue\n",
    "AS\n",
    "SELECT o.order_date,\n",
    "    round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue\n",
    "FROM orders o JOIN order_items oi\n",
    "ON o.order_id = oi.order_item_order_id\n",
    "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n",
    "GROUP BY o.order_date\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c2fc6797",
   "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>order_date</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>31547.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>54713.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>48411.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>35672.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-29</td>\n",
       "      <td>54579.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-30</td>\n",
       "      <td>49329.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-31</td>\n",
       "      <td>59212.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-08-01</td>\n",
       "      <td>49160.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-08-02</td>\n",
       "      <td>50688.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-08-03</td>\n",
       "      <td>43416.74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date   revenue\n",
       "0 2013-07-25  31547.23\n",
       "1 2013-07-26  54713.23\n",
       "2 2013-07-27  48411.48\n",
       "3 2013-07-28  35672.03\n",
       "4 2013-07-29  54579.70\n",
       "5 2013-07-30  49329.29\n",
       "6 2013-07-31  59212.49\n",
       "7 2013-08-01  49160.08\n",
       "8 2013-08-02  50688.58\n",
       "9 2013-08-03  43416.74"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM daily_revenue\n",
    "ORDER BY order_date\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c619c599",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE daily_product_revenue\n",
    "AS\n",
    "SELECT o.order_date,\n",
    "    oi.order_item_product_id,\n",
    "    round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue\n",
    "FROM orders o JOIN order_items oi\n",
    "ON o.order_id = oi.order_item_order_id\n",
    "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n",
    "GROUP BY o.order_date, oi.order_item_product_id\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ad930129",
   "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>order_date</th>\n",
       "      <th>order_item_product_id</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1004</td>\n",
       "      <td>5599.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>191</td>\n",
       "      <td>5099.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>957</td>\n",
       "      <td>4499.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>365</td>\n",
       "      <td>3359.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1073</td>\n",
       "      <td>2999.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1014</td>\n",
       "      <td>2798.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>403</td>\n",
       "      <td>1949.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>502</td>\n",
       "      <td>1650.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>627</td>\n",
       "      <td>1079.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>226</td>\n",
       "      <td>599.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date  order_item_product_id  revenue\n",
       "0 2013-07-25                   1004  5599.72\n",
       "1 2013-07-25                    191  5099.49\n",
       "2 2013-07-25                    957  4499.70\n",
       "3 2013-07-25                    365  3359.44\n",
       "4 2013-07-25                   1073  2999.85\n",
       "5 2013-07-25                   1014  2798.88\n",
       "6 2013-07-25                    403  1949.85\n",
       "7 2013-07-25                    502  1650.00\n",
       "8 2013-07-25                    627  1079.73\n",
       "9 2013-07-25                    226   599.99"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM daily_product_revenue\n",
    "ORDER BY order_date, revenue DESC\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f62170e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = 'itversit_hrdb'\n",
    "user = config['POSTGRES']['PG_UNAME']\n",
    "passwd = config['POSTGRES']['PG_PASS']\n",
    "port = config['POSTGRES']['PG_PORT']\n",
    "host = config['POSTGRES']['PG_HOST']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "84cc3e97",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'itversit_hrdb'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a2bb6cc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "credentials = \"postgresql://{}:{}@{}:{}/{}\".format(user,passwd,host,port,db)\n",
    "\n",
    "#using psycopg2 to test connection since there are no tables\n",
    "import psycopg2\n",
    "try:\n",
    "    conn = psycopg2.connect(host=host,dbname=db,user=user,password=passwd,port=port)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "conn.set_session(autocommit=True)\n",
    "\n",
    "try:\n",
    "    cur = conn.cursor()\n",
    "    \n",
    "except:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a2e6192c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'postgresql://postgres:1234@172.17.0.2:5432/itversit_hrdb'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "1565f26f",
   "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>employee_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200</td>\n",
       "      <td>10</td>\n",
       "      <td>4400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>202</td>\n",
       "      <td>20</td>\n",
       "      <td>6000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201</td>\n",
       "      <td>20</td>\n",
       "      <td>13000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>119</td>\n",
       "      <td>30</td>\n",
       "      <td>2500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>118</td>\n",
       "      <td>30</td>\n",
       "      <td>2600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>117</td>\n",
       "      <td>30</td>\n",
       "      <td>2800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>116</td>\n",
       "      <td>30</td>\n",
       "      <td>2900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>115</td>\n",
       "      <td>30</td>\n",
       "      <td>3100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>114</td>\n",
       "      <td>30</td>\n",
       "      <td>11000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>203</td>\n",
       "      <td>40</td>\n",
       "      <td>6500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id  department_id   salary\n",
       "0          200             10   4400.0\n",
       "1          202             20   6000.0\n",
       "2          201             20  13000.0\n",
       "3          119             30   2500.0\n",
       "4          118             30   2600.0\n",
       "5          117             30   2800.0\n",
       "6          116             30   2900.0\n",
       "7          115             30   3100.0\n",
       "8          114             30  11000.0\n",
       "9          203             40   6500.0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT employee_id, department_id, salary \n",
    "FROM employees \n",
    "ORDER BY department_id, salary\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6dc8ba45",
   "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>employee_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>department_salary_expense</th>\n",
       "      <th>avg_salary_expense</th>\n",
       "      <th>pct_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200</td>\n",
       "      <td>10</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>202</td>\n",
       "      <td>20</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>19000.0</td>\n",
       "      <td>9500.0</td>\n",
       "      <td>31.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201</td>\n",
       "      <td>20</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>19000.0</td>\n",
       "      <td>9500.0</td>\n",
       "      <td>68.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>119</td>\n",
       "      <td>30</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>10.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>118</td>\n",
       "      <td>30</td>\n",
       "      <td>2600.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>10.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>117</td>\n",
       "      <td>30</td>\n",
       "      <td>2800.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>11.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>116</td>\n",
       "      <td>30</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>11.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>115</td>\n",
       "      <td>30</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>12.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>114</td>\n",
       "      <td>30</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>44.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>203</td>\n",
       "      <td>40</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id  department_id   salary  department_salary_expense  \\\n",
       "0          200             10   4400.0                     4400.0   \n",
       "1          202             20   6000.0                    19000.0   \n",
       "2          201             20  13000.0                    19000.0   \n",
       "3          119             30   2500.0                    24900.0   \n",
       "4          118             30   2600.0                    24900.0   \n",
       "5          117             30   2800.0                    24900.0   \n",
       "6          116             30   2900.0                    24900.0   \n",
       "7          115             30   3100.0                    24900.0   \n",
       "8          114             30  11000.0                    24900.0   \n",
       "9          203             40   6500.0                     6500.0   \n",
       "\n",
       "   avg_salary_expense  pct_salary  \n",
       "0              4400.0      100.00  \n",
       "1              9500.0       31.58  \n",
       "2              9500.0       68.42  \n",
       "3              4150.0       10.04  \n",
       "4              4150.0       10.44  \n",
       "5              4150.0       11.24  \n",
       "6              4150.0       11.65  \n",
       "7              4150.0       12.45  \n",
       "8              4150.0       44.18  \n",
       "9              6500.0      100.00  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT e.employee_id, e.department_id, e.salary,\n",
    "    ae.department_salary_expense,\n",
    "    ae.avg_salary_expense,\n",
    "    round(e.salary/ae.department_salary_expense * 100, 2) pct_salary\n",
    "FROM employees e JOIN (\n",
    "    SELECT department_id, \n",
    "        sum(salary) AS department_salary_expense,\n",
    "        round(avg(salary)::numeric, 2) AS avg_salary_expense\n",
    "    FROM employees\n",
    "    GROUP BY department_id\n",
    ") ae\n",
    "ON e.department_id = ae.department_id\n",
    "ORDER BY department_id, salary\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b2459259",
   "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>employee_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>department_salary_expense</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200</td>\n",
       "      <td>10</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201</td>\n",
       "      <td>20</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>19000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>202</td>\n",
       "      <td>20</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>19000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>114</td>\n",
       "      <td>30</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>24900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>115</td>\n",
       "      <td>30</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>24900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>116</td>\n",
       "      <td>30</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>24900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>117</td>\n",
       "      <td>30</td>\n",
       "      <td>2800.0</td>\n",
       "      <td>24900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>118</td>\n",
       "      <td>30</td>\n",
       "      <td>2600.0</td>\n",
       "      <td>24900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>119</td>\n",
       "      <td>30</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>24900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>203</td>\n",
       "      <td>40</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>6500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id  department_id   salary  department_salary_expense\n",
       "0          200             10   4400.0                     4400.0\n",
       "1          201             20  13000.0                    19000.0\n",
       "2          202             20   6000.0                    19000.0\n",
       "3          114             30  11000.0                    24900.0\n",
       "4          115             30   3100.0                    24900.0\n",
       "5          116             30   2900.0                    24900.0\n",
       "6          117             30   2800.0                    24900.0\n",
       "7          118             30   2600.0                    24900.0\n",
       "8          119             30   2500.0                    24900.0\n",
       "9          203             40   6500.0                     6500.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Using windowing function\n",
    "\n",
    "queryBase(\"\"\"SELECT e.employee_id, e.department_id, e.salary,\n",
    "    sum(e.salary) OVER (\n",
    "        PARTITION BY e.department_id\n",
    "    ) AS department_salary_expense\n",
    "FROM employees e\n",
    "ORDER BY e.department_id\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5965a662",
   "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>employee_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>department_salary_expense</th>\n",
       "      <th>pct_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200</td>\n",
       "      <td>10</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>202</td>\n",
       "      <td>20</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>19000.0</td>\n",
       "      <td>31.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201</td>\n",
       "      <td>20</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>19000.0</td>\n",
       "      <td>68.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>119</td>\n",
       "      <td>30</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>10.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>118</td>\n",
       "      <td>30</td>\n",
       "      <td>2600.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>10.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>117</td>\n",
       "      <td>30</td>\n",
       "      <td>2800.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>11.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>116</td>\n",
       "      <td>30</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>11.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>115</td>\n",
       "      <td>30</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>12.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>114</td>\n",
       "      <td>30</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>44.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>203</td>\n",
       "      <td>40</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id  department_id   salary  department_salary_expense  pct_salary\n",
       "0          200             10   4400.0                     4400.0      100.00\n",
       "1          202             20   6000.0                    19000.0       31.58\n",
       "2          201             20  13000.0                    19000.0       68.42\n",
       "3          119             30   2500.0                    24900.0       10.04\n",
       "4          118             30   2600.0                    24900.0       10.44\n",
       "5          117             30   2800.0                    24900.0       11.24\n",
       "6          116             30   2900.0                    24900.0       11.65\n",
       "7          115             30   3100.0                    24900.0       12.45\n",
       "8          114             30  11000.0                    24900.0       44.18\n",
       "9          203             40   6500.0                     6500.0      100.00"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Using windowing function\n",
    "\n",
    "queryBase(\"\"\"SELECT e.employee_id, e.department_id, e.salary,\n",
    "    sum(e.salary) OVER (\n",
    "        PARTITION BY e.department_id\n",
    "    ) AS department_salary_expense,\n",
    "    round(e.salary / sum(e.salary) OVER (\n",
    "        PARTITION BY e.department_id\n",
    "    ) * 100, 2) AS pct_salary\n",
    "FROM employees e\n",
    "ORDER BY e.department_id,\n",
    "    e.salary\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e5d6998e",
   "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>employee_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>sum_sal_expense</th>\n",
       "      <th>avg_sal_expense</th>\n",
       "      <th>min_sal_expense</th>\n",
       "      <th>max_sal_expense</th>\n",
       "      <th>cnt_sal_expense</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200</td>\n",
       "      <td>10</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>202</td>\n",
       "      <td>20</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>19000.0</td>\n",
       "      <td>9500.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201</td>\n",
       "      <td>20</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>19000.0</td>\n",
       "      <td>9500.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>119</td>\n",
       "      <td>30</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>118</td>\n",
       "      <td>30</td>\n",
       "      <td>2600.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>117</td>\n",
       "      <td>30</td>\n",
       "      <td>2800.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>116</td>\n",
       "      <td>30</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>115</td>\n",
       "      <td>30</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>114</td>\n",
       "      <td>30</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>24900.0</td>\n",
       "      <td>4150.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>203</td>\n",
       "      <td>40</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>6500.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id  department_id   salary  sum_sal_expense  avg_sal_expense  \\\n",
       "0          200             10   4400.0           4400.0           4400.0   \n",
       "1          202             20   6000.0          19000.0           9500.0   \n",
       "2          201             20  13000.0          19000.0           9500.0   \n",
       "3          119             30   2500.0          24900.0           4150.0   \n",
       "4          118             30   2600.0          24900.0           4150.0   \n",
       "5          117             30   2800.0          24900.0           4150.0   \n",
       "6          116             30   2900.0          24900.0           4150.0   \n",
       "7          115             30   3100.0          24900.0           4150.0   \n",
       "8          114             30  11000.0          24900.0           4150.0   \n",
       "9          203             40   6500.0           6500.0           6500.0   \n",
       "\n",
       "   min_sal_expense  max_sal_expense  cnt_sal_expense  \n",
       "0           4400.0           4400.0                1  \n",
       "1           6000.0          13000.0                2  \n",
       "2           6000.0          13000.0                2  \n",
       "3           2500.0          11000.0                6  \n",
       "4           2500.0          11000.0                6  \n",
       "5           2500.0          11000.0                6  \n",
       "6           2500.0          11000.0                6  \n",
       "7           2500.0          11000.0                6  \n",
       "8           2500.0          11000.0                6  \n",
       "9           6500.0           6500.0                1  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Using windowing function\n",
    "\n",
    "queryBase(\"\"\"SELECT e.employee_id, e.department_id, e.salary,\n",
    "    sum(e.salary) OVER (\n",
    "        PARTITION BY e.department_id\n",
    "    ) AS sum_sal_expense,\n",
    "    round(avg(e.salary) OVER (\n",
    "        PARTITION BY e.department_id\n",
    "    ), 2) AS avg_sal_expense,\n",
    "    min(e.salary) OVER (\n",
    "        PARTITION BY e.department_id\n",
    "    ) AS min_sal_expense,\n",
    "    max(e.salary) OVER (\n",
    "        PARTITION BY e.department_id\n",
    "    ) AS max_sal_expense,\n",
    "    count(e.salary) OVER (\n",
    "        PARTITION BY e.department_id\n",
    "    ) AS cnt_sal_expense\n",
    "FROM employees e\n",
    "ORDER BY e.department_id,\n",
    "    e.salary\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f6a4176a",
   "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>order_date</th>\n",
       "      <th>revenue</th>\n",
       "      <th>cumulative_daily_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>31547.23</td>\n",
       "      <td>31547.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>54713.23</td>\n",
       "      <td>86260.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>48411.48</td>\n",
       "      <td>134671.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>35672.03</td>\n",
       "      <td>170343.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-29</td>\n",
       "      <td>54579.70</td>\n",
       "      <td>224923.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-30</td>\n",
       "      <td>49329.29</td>\n",
       "      <td>274252.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-31</td>\n",
       "      <td>59212.49</td>\n",
       "      <td>333465.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-08-01</td>\n",
       "      <td>49160.08</td>\n",
       "      <td>49160.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-08-02</td>\n",
       "      <td>50688.58</td>\n",
       "      <td>99848.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-08-03</td>\n",
       "      <td>43416.74</td>\n",
       "      <td>143265.40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date   revenue  cumulative_daily_revenue\n",
       "0 2013-07-25  31547.23                  31547.23\n",
       "1 2013-07-26  54713.23                  86260.46\n",
       "2 2013-07-27  48411.48                 134671.94\n",
       "3 2013-07-28  35672.03                 170343.97\n",
       "4 2013-07-29  54579.70                 224923.67\n",
       "5 2013-07-30  49329.29                 274252.96\n",
       "6 2013-07-31  59212.49                 333465.45\n",
       "7 2013-08-01  49160.08                  49160.08\n",
       "8 2013-08-02  50688.58                  99848.66\n",
       "9 2013-08-03  43416.74                 143265.40"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT t.*,\n",
    "    round(sum(t.revenue) OVER (\n",
    "        PARTITION BY to_char(order_date, 'yyyy-MM')\n",
    "        ORDER BY order_date\n",
    "        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n",
    "    ), 2) AS cumulative_daily_revenue\n",
    "FROM daily_revenue t\n",
    "ORDER BY to_char(order_date, 'yyyy-MM'),\n",
    "    order_date\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "940c4d68",
   "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>order_date</th>\n",
       "      <th>revenue</th>\n",
       "      <th>moving_3day_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>31547.23</td>\n",
       "      <td>31547.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>54713.23</td>\n",
       "      <td>86260.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>48411.48</td>\n",
       "      <td>134671.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>35672.03</td>\n",
       "      <td>138796.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-29</td>\n",
       "      <td>54579.70</td>\n",
       "      <td>138663.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-30</td>\n",
       "      <td>49329.29</td>\n",
       "      <td>139581.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-31</td>\n",
       "      <td>59212.49</td>\n",
       "      <td>163121.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-08-01</td>\n",
       "      <td>49160.08</td>\n",
       "      <td>157701.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-08-02</td>\n",
       "      <td>50688.58</td>\n",
       "      <td>159061.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-08-03</td>\n",
       "      <td>43416.74</td>\n",
       "      <td>143265.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2013-08-04</td>\n",
       "      <td>35093.01</td>\n",
       "      <td>129198.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2013-08-05</td>\n",
       "      <td>34025.27</td>\n",
       "      <td>112535.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013-08-06</td>\n",
       "      <td>57843.89</td>\n",
       "      <td>126962.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013-08-07</td>\n",
       "      <td>45525.59</td>\n",
       "      <td>137394.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013-08-08</td>\n",
       "      <td>33549.47</td>\n",
       "      <td>136918.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2013-08-09</td>\n",
       "      <td>29225.16</td>\n",
       "      <td>108300.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2013-08-10</td>\n",
       "      <td>46435.04</td>\n",
       "      <td>109209.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013-08-11</td>\n",
       "      <td>31155.50</td>\n",
       "      <td>106815.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013-08-12</td>\n",
       "      <td>59014.74</td>\n",
       "      <td>136605.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013-08-13</td>\n",
       "      <td>17956.88</td>\n",
       "      <td>108127.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date   revenue  moving_3day_revenue\n",
       "0  2013-07-25  31547.23             31547.23\n",
       "1  2013-07-26  54713.23             86260.46\n",
       "2  2013-07-27  48411.48            134671.94\n",
       "3  2013-07-28  35672.03            138796.74\n",
       "4  2013-07-29  54579.70            138663.21\n",
       "5  2013-07-30  49329.29            139581.02\n",
       "6  2013-07-31  59212.49            163121.48\n",
       "7  2013-08-01  49160.08            157701.86\n",
       "8  2013-08-02  50688.58            159061.15\n",
       "9  2013-08-03  43416.74            143265.40\n",
       "10 2013-08-04  35093.01            129198.33\n",
       "11 2013-08-05  34025.27            112535.02\n",
       "12 2013-08-06  57843.89            126962.17\n",
       "13 2013-08-07  45525.59            137394.75\n",
       "14 2013-08-08  33549.47            136918.95\n",
       "15 2013-08-09  29225.16            108300.22\n",
       "16 2013-08-10  46435.04            109209.67\n",
       "17 2013-08-11  31155.50            106815.70\n",
       "18 2013-08-12  59014.74            136605.28\n",
       "19 2013-08-13  17956.88            108127.12"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT t.*,\n",
    "    round(sum(t.revenue) OVER (\n",
    "        ORDER BY order_date\n",
    "        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n",
    "    ), 2) AS moving_3day_revenue\n",
    "FROM daily_revenue t\n",
    "ORDER BY order_date\n",
    "LIMIT 20\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "64a24375",
   "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>order_date</th>\n",
       "      <th>revenue</th>\n",
       "      <th>moving_3day_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>31547.23</td>\n",
       "      <td>134671.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>54713.23</td>\n",
       "      <td>170343.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>48411.48</td>\n",
       "      <td>224923.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>35672.03</td>\n",
       "      <td>242705.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-29</td>\n",
       "      <td>54579.70</td>\n",
       "      <td>247204.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-30</td>\n",
       "      <td>49329.29</td>\n",
       "      <td>247953.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-31</td>\n",
       "      <td>59212.49</td>\n",
       "      <td>262970.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-08-01</td>\n",
       "      <td>49160.08</td>\n",
       "      <td>251807.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-08-02</td>\n",
       "      <td>50688.58</td>\n",
       "      <td>237570.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-08-03</td>\n",
       "      <td>43416.74</td>\n",
       "      <td>212383.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2013-08-04</td>\n",
       "      <td>35093.01</td>\n",
       "      <td>221067.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2013-08-05</td>\n",
       "      <td>34025.27</td>\n",
       "      <td>215904.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013-08-06</td>\n",
       "      <td>57843.89</td>\n",
       "      <td>206037.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013-08-07</td>\n",
       "      <td>45525.59</td>\n",
       "      <td>200169.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013-08-08</td>\n",
       "      <td>33549.47</td>\n",
       "      <td>212579.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2013-08-09</td>\n",
       "      <td>29225.16</td>\n",
       "      <td>185890.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2013-08-10</td>\n",
       "      <td>46435.04</td>\n",
       "      <td>199379.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013-08-11</td>\n",
       "      <td>31155.50</td>\n",
       "      <td>183787.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013-08-12</td>\n",
       "      <td>59014.74</td>\n",
       "      <td>196605.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013-08-13</td>\n",
       "      <td>17956.88</td>\n",
       "      <td>199737.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date   revenue  moving_3day_revenue\n",
       "0  2013-07-25  31547.23            134671.94\n",
       "1  2013-07-26  54713.23            170343.97\n",
       "2  2013-07-27  48411.48            224923.67\n",
       "3  2013-07-28  35672.03            242705.73\n",
       "4  2013-07-29  54579.70            247204.99\n",
       "5  2013-07-30  49329.29            247953.59\n",
       "6  2013-07-31  59212.49            262970.14\n",
       "7  2013-08-01  49160.08            251807.18\n",
       "8  2013-08-02  50688.58            237570.90\n",
       "9  2013-08-03  43416.74            212383.68\n",
       "10 2013-08-04  35093.01            221067.49\n",
       "11 2013-08-05  34025.27            215904.50\n",
       "12 2013-08-06  57843.89            206037.23\n",
       "13 2013-08-07  45525.59            200169.38\n",
       "14 2013-08-08  33549.47            212579.15\n",
       "15 2013-08-09  29225.16            185890.76\n",
       "16 2013-08-10  46435.04            199379.91\n",
       "17 2013-08-11  31155.50            183787.32\n",
       "18 2013-08-12  59014.74            196605.61\n",
       "19 2013-08-13  17956.88            199737.25"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT t.*,\n",
    "    round(sum(t.revenue) OVER (\n",
    "        ORDER BY order_date\n",
    "        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING\n",
    "    ), 2) AS moving_3day_revenue\n",
    "FROM daily_revenue t\n",
    "ORDER BY order_date\n",
    "LIMIT 20\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c8ac37ee",
   "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>order_date</th>\n",
       "      <th>order_item_product_id</th>\n",
       "      <th>revenue</th>\n",
       "      <th>rnk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1004</td>\n",
       "      <td>5599.72</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>191</td>\n",
       "      <td>5099.49</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>957</td>\n",
       "      <td>4499.70</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>365</td>\n",
       "      <td>3359.44</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1073</td>\n",
       "      <td>2999.85</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1014</td>\n",
       "      <td>2798.88</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>403</td>\n",
       "      <td>1949.85</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>502</td>\n",
       "      <td>1650.00</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>627</td>\n",
       "      <td>1079.73</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>226</td>\n",
       "      <td>599.99</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>24</td>\n",
       "      <td>319.96</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>821</td>\n",
       "      <td>207.96</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>625</td>\n",
       "      <td>199.99</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>705</td>\n",
       "      <td>119.99</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>572</td>\n",
       "      <td>119.97</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>666</td>\n",
       "      <td>109.99</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>725</td>\n",
       "      <td>108.00</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>134</td>\n",
       "      <td>100.00</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>906</td>\n",
       "      <td>99.96</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>828</td>\n",
       "      <td>95.97</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>810</td>\n",
       "      <td>79.96</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>924</td>\n",
       "      <td>79.95</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>926</td>\n",
       "      <td>79.95</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>93</td>\n",
       "      <td>74.97</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>835</td>\n",
       "      <td>63.98</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>897</td>\n",
       "      <td>49.98</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>1004</td>\n",
       "      <td>10799.46</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>365</td>\n",
       "      <td>7978.67</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>957</td>\n",
       "      <td>6899.54</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>191</td>\n",
       "      <td>6799.32</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date  order_item_product_id   revenue  rnk\n",
       "0  2013-07-25                   1004   5599.72    1\n",
       "1  2013-07-25                    191   5099.49    2\n",
       "2  2013-07-25                    957   4499.70    3\n",
       "3  2013-07-25                    365   3359.44    4\n",
       "4  2013-07-25                   1073   2999.85    5\n",
       "5  2013-07-25                   1014   2798.88    6\n",
       "6  2013-07-25                    403   1949.85    7\n",
       "7  2013-07-25                    502   1650.00    8\n",
       "8  2013-07-25                    627   1079.73    9\n",
       "9  2013-07-25                    226    599.99   10\n",
       "10 2013-07-25                     24    319.96   11\n",
       "11 2013-07-25                    821    207.96   12\n",
       "12 2013-07-25                    625    199.99   13\n",
       "13 2013-07-25                    705    119.99   14\n",
       "14 2013-07-25                    572    119.97   15\n",
       "15 2013-07-25                    666    109.99   16\n",
       "16 2013-07-25                    725    108.00   17\n",
       "17 2013-07-25                    134    100.00   18\n",
       "18 2013-07-25                    906     99.96   19\n",
       "19 2013-07-25                    828     95.97   20\n",
       "20 2013-07-25                    810     79.96   21\n",
       "21 2013-07-25                    924     79.95   22\n",
       "22 2013-07-25                    926     79.95   22\n",
       "23 2013-07-25                     93     74.97   24\n",
       "24 2013-07-25                    835     63.98   25\n",
       "25 2013-07-25                    897     49.98   26\n",
       "26 2013-07-26                   1004  10799.46    1\n",
       "27 2013-07-26                    365   7978.67    2\n",
       "28 2013-07-26                    957   6899.54    3\n",
       "29 2013-07-26                    191   6799.32    4"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT t.*,\n",
    "    rank() OVER (\n",
    "        PARTITION BY order_date\n",
    "        ORDER BY revenue DESC\n",
    "    ) AS rnk\n",
    "FROM daily_product_revenue t\n",
    "ORDER BY order_date, revenue DESC\n",
    "LIMIT 30\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "25eadb78",
   "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>order_date</th>\n",
       "      <th>order_item_product_id</th>\n",
       "      <th>revenue</th>\n",
       "      <th>rnk</th>\n",
       "      <th>drnk</th>\n",
       "      <th>rn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1004</td>\n",
       "      <td>5599.72</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>191</td>\n",
       "      <td>5099.49</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>957</td>\n",
       "      <td>4499.70</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>365</td>\n",
       "      <td>3359.44</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1073</td>\n",
       "      <td>2999.85</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1014</td>\n",
       "      <td>2798.88</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>403</td>\n",
       "      <td>1949.85</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>502</td>\n",
       "      <td>1650.00</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>627</td>\n",
       "      <td>1079.73</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>226</td>\n",
       "      <td>599.99</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>24</td>\n",
       "      <td>319.96</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>821</td>\n",
       "      <td>207.96</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>625</td>\n",
       "      <td>199.99</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>705</td>\n",
       "      <td>119.99</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>572</td>\n",
       "      <td>119.97</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>666</td>\n",
       "      <td>109.99</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>725</td>\n",
       "      <td>108.00</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>134</td>\n",
       "      <td>100.00</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>906</td>\n",
       "      <td>99.96</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>828</td>\n",
       "      <td>95.97</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>810</td>\n",
       "      <td>79.96</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>924</td>\n",
       "      <td>79.95</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>926</td>\n",
       "      <td>79.95</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>93</td>\n",
       "      <td>74.97</td>\n",
       "      <td>24</td>\n",
       "      <td>23</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>835</td>\n",
       "      <td>63.98</td>\n",
       "      <td>25</td>\n",
       "      <td>24</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>897</td>\n",
       "      <td>49.98</td>\n",
       "      <td>26</td>\n",
       "      <td>25</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>1004</td>\n",
       "      <td>10799.46</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>365</td>\n",
       "      <td>7978.67</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>957</td>\n",
       "      <td>6899.54</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>191</td>\n",
       "      <td>6799.32</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date  order_item_product_id   revenue  rnk  drnk  rn\n",
       "0  2013-07-25                   1004   5599.72    1     1   1\n",
       "1  2013-07-25                    191   5099.49    2     2   2\n",
       "2  2013-07-25                    957   4499.70    3     3   3\n",
       "3  2013-07-25                    365   3359.44    4     4   4\n",
       "4  2013-07-25                   1073   2999.85    5     5   5\n",
       "5  2013-07-25                   1014   2798.88    6     6   6\n",
       "6  2013-07-25                    403   1949.85    7     7   7\n",
       "7  2013-07-25                    502   1650.00    8     8   8\n",
       "8  2013-07-25                    627   1079.73    9     9   9\n",
       "9  2013-07-25                    226    599.99   10    10  10\n",
       "10 2013-07-25                     24    319.96   11    11  11\n",
       "11 2013-07-25                    821    207.96   12    12  12\n",
       "12 2013-07-25                    625    199.99   13    13  13\n",
       "13 2013-07-25                    705    119.99   14    14  14\n",
       "14 2013-07-25                    572    119.97   15    15  15\n",
       "15 2013-07-25                    666    109.99   16    16  16\n",
       "16 2013-07-25                    725    108.00   17    17  17\n",
       "17 2013-07-25                    134    100.00   18    18  18\n",
       "18 2013-07-25                    906     99.96   19    19  19\n",
       "19 2013-07-25                    828     95.97   20    20  20\n",
       "20 2013-07-25                    810     79.96   21    21  21\n",
       "21 2013-07-25                    924     79.95   22    22  22\n",
       "22 2013-07-25                    926     79.95   22    22  23\n",
       "23 2013-07-25                     93     74.97   24    23  24\n",
       "24 2013-07-25                    835     63.98   25    24  25\n",
       "25 2013-07-25                    897     49.98   26    25  26\n",
       "26 2013-07-26                   1004  10799.46    1     1   1\n",
       "27 2013-07-26                    365   7978.67    2     2   2\n",
       "28 2013-07-26                    957   6899.54    3     3   3\n",
       "29 2013-07-26                    191   6799.32    4     4   4"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT\n",
    "    t.*,\n",
    "    rank() OVER (\n",
    "        PARTITION BY order_date\n",
    "        ORDER BY revenue DESC\n",
    "    ) rnk,\n",
    "    dense_rank() OVER (\n",
    "        PARTITION BY order_date\n",
    "        ORDER BY revenue DESC\n",
    "    ) drnk,\n",
    "    row_number() OVER (\n",
    "        PARTITION BY order_date\n",
    "        ORDER BY revenue DESC\n",
    "    ) rn\n",
    "FROM daily_product_revenue AS t\n",
    "ORDER BY order_date, revenue DESC\n",
    "LIMIT 30\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "3ec11750",
   "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>order_date</th>\n",
       "      <th>order_item_product_id</th>\n",
       "      <th>revenue</th>\n",
       "      <th>drnk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1004</td>\n",
       "      <td>5599.72</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>191</td>\n",
       "      <td>5099.49</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>957</td>\n",
       "      <td>4499.70</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>365</td>\n",
       "      <td>3359.44</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1073</td>\n",
       "      <td>2999.85</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>1004</td>\n",
       "      <td>10799.46</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>365</td>\n",
       "      <td>7978.67</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>957</td>\n",
       "      <td>6899.54</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>191</td>\n",
       "      <td>6799.32</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>1014</td>\n",
       "      <td>4798.08</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>1004</td>\n",
       "      <td>9599.52</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>191</td>\n",
       "      <td>5999.40</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>957</td>\n",
       "      <td>5699.62</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>1073</td>\n",
       "      <td>5399.73</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>365</td>\n",
       "      <td>5099.15</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>1004</td>\n",
       "      <td>5599.72</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>957</td>\n",
       "      <td>5099.66</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>365</td>\n",
       "      <td>4799.20</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>403</td>\n",
       "      <td>4419.66</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>191</td>\n",
       "      <td>4299.57</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date  order_item_product_id   revenue  drnk\n",
       "0  2013-07-25                   1004   5599.72     1\n",
       "1  2013-07-25                    191   5099.49     2\n",
       "2  2013-07-25                    957   4499.70     3\n",
       "3  2013-07-25                    365   3359.44     4\n",
       "4  2013-07-25                   1073   2999.85     5\n",
       "5  2013-07-26                   1004  10799.46     1\n",
       "6  2013-07-26                    365   7978.67     2\n",
       "7  2013-07-26                    957   6899.54     3\n",
       "8  2013-07-26                    191   6799.32     4\n",
       "9  2013-07-26                   1014   4798.08     5\n",
       "10 2013-07-27                   1004   9599.52     1\n",
       "11 2013-07-27                    191   5999.40     2\n",
       "12 2013-07-27                    957   5699.62     3\n",
       "13 2013-07-27                   1073   5399.73     4\n",
       "14 2013-07-27                    365   5099.15     5\n",
       "15 2013-07-28                   1004   5599.72     1\n",
       "16 2013-07-28                    957   5099.66     2\n",
       "17 2013-07-28                    365   4799.20     3\n",
       "18 2013-07-28                    403   4419.66     4\n",
       "19 2013-07-28                    191   4299.57     5"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM (\n",
    "    SELECT nq.*,\n",
    "        dense_rank() OVER (\n",
    "            PARTITION BY order_date\n",
    "            ORDER BY revenue DESC\n",
    "        ) AS drnk\n",
    "    FROM (\n",
    "        SELECT o.order_date,\n",
    "            oi.order_item_product_id,\n",
    "            round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue\n",
    "        FROM orders o \n",
    "            JOIN order_items oi\n",
    "                ON o.order_id = oi.order_item_order_id\n",
    "        WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n",
    "        GROUP BY o.order_date, oi.order_item_product_id\n",
    "    ) nq\n",
    ") nq1\n",
    "WHERE drnk <= 5\n",
    "ORDER BY order_date, revenue DESC\n",
    "cv\n",
    "LIMIT 20\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "667be411",
   "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>order_date</th>\n",
       "      <th>order_item_product_id</th>\n",
       "      <th>revenue</th>\n",
       "      <th>drnk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1004</td>\n",
       "      <td>5599.72</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>191</td>\n",
       "      <td>5099.49</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>957</td>\n",
       "      <td>4499.70</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>365</td>\n",
       "      <td>3359.44</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-07-25</td>\n",
       "      <td>1073</td>\n",
       "      <td>2999.85</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>1004</td>\n",
       "      <td>10799.46</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>365</td>\n",
       "      <td>7978.67</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>957</td>\n",
       "      <td>6899.54</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>191</td>\n",
       "      <td>6799.32</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013-07-26</td>\n",
       "      <td>1014</td>\n",
       "      <td>4798.08</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>1004</td>\n",
       "      <td>9599.52</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>191</td>\n",
       "      <td>5999.40</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>957</td>\n",
       "      <td>5699.62</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>1073</td>\n",
       "      <td>5399.73</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013-07-27</td>\n",
       "      <td>365</td>\n",
       "      <td>5099.15</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>1004</td>\n",
       "      <td>5599.72</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>957</td>\n",
       "      <td>5099.66</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>365</td>\n",
       "      <td>4799.20</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>403</td>\n",
       "      <td>4419.66</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013-07-28</td>\n",
       "      <td>191</td>\n",
       "      <td>4299.57</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date  order_item_product_id   revenue  drnk\n",
       "0  2013-07-25                   1004   5599.72     1\n",
       "1  2013-07-25                    191   5099.49     2\n",
       "2  2013-07-25                    957   4499.70     3\n",
       "3  2013-07-25                    365   3359.44     4\n",
       "4  2013-07-25                   1073   2999.85     5\n",
       "5  2013-07-26                   1004  10799.46     1\n",
       "6  2013-07-26                    365   7978.67     2\n",
       "7  2013-07-26                    957   6899.54     3\n",
       "8  2013-07-26                    191   6799.32     4\n",
       "9  2013-07-26                   1014   4798.08     5\n",
       "10 2013-07-27                   1004   9599.52     1\n",
       "11 2013-07-27                    191   5999.40     2\n",
       "12 2013-07-27                    957   5699.62     3\n",
       "13 2013-07-27                   1073   5399.73     4\n",
       "14 2013-07-27                    365   5099.15     5\n",
       "15 2013-07-28                   1004   5599.72     1\n",
       "16 2013-07-28                    957   5099.66     2\n",
       "17 2013-07-28                    365   4799.20     3\n",
       "18 2013-07-28                    403   4419.66     4\n",
       "19 2013-07-28                    191   4299.57     5"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT * FROM (SELECT dpr.*,\n",
    "  dense_rank() OVER (\n",
    "    PARTITION BY order_date\n",
    "    ORDER BY revenue DESC\n",
    "  ) AS drnk\n",
    "FROM daily_product_revenue AS dpr) q\n",
    "WHERE drnk <= 5\n",
    "ORDER BY order_date, revenue DESC\n",
    "LIMIT 20\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8304b931",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}