database_views/views_overview.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "882e4916-5b28-47c1-ac69-029adde045f3",
   "metadata": {},
   "source": [
    "# Database Views\n",
    "\n",
    "Presented below are the views crafted for the spend_save database. These views serve as informative \"reports,\" providing convenient access and analysis of summarized data pertaining to my spending patterns and account balances. To showcase the creation process and the respective outputs of each view, I opted to utilize Jupyter Notebooks along with the sql magic command. If you are interested in examining or executing the SQL script employed to establish these views, please refer to the [views creation script](views_creation_script.sql).\n",
    "\n",
    "Please note that the results produced by the queries presented below do not reflect my personal financial information. Before executing these queries, fictitious transactions were deliberately introduced into the database to safeguard privacy."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a592cdf4-d40b-4483-90aa-09c174bfba40",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "In order to execute the SQL code within Jupyter Notebooks, a few setup steps are necessary. Firstly, we need to install and load the SQL IPython module, enabling the execution of SQL code. Additionally, we need to establish a connection to the database. It is worth noting that the \"%\" symbol is utilized to indicate a single line of a Jupyter magic command, while \"%%\" designates the entire cell as a magic command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "442f402c-7455-42f9-8fd0-096c51a458af",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: ipython-sql in c:\\users\\weism\\anaconda3\\lib\\site-packages (0.5.0)\n",
      "Requirement already satisfied: prettytable in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython-sql) (3.8.0)\n",
      "Requirement already satisfied: six in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython-sql) (1.16.0)\n",
      "Requirement already satisfied: sqlparse in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython-sql) (0.4.4)\n",
      "Requirement already satisfied: sqlalchemy>=2.0 in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython-sql) (2.0.16)\n",
      "Requirement already satisfied: ipython in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython-sql) (8.10.0)\n",
      "Requirement already satisfied: ipython-genutils in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\weism\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (2.0.1)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in c:\\users\\weism\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (4.4.0)\n",
      "Requirement already satisfied: traitlets>=5 in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.7.1)\n",
      "Requirement already satisfied: jedi>=0.16 in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.18.1)\n",
      "Requirement already satisfied: prompt-toolkit<3.1.0,>=3.0.30 in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (3.0.36)\n",
      "Requirement already satisfied: pygments>=2.4.0 in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (2.11.2)\n",
      "Requirement already satisfied: matplotlib-inline in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.1.6)\n",
      "Requirement already satisfied: decorator in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: stack-data in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: pickleshare in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: colorama in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.4.6)\n",
      "Requirement already satisfied: backcall in c:\\users\\weism\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: wcwidth in c:\\users\\weism\\anaconda3\\lib\\site-packages (from prettytable->ipython-sql) (0.2.5)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.0 in c:\\users\\weism\\anaconda3\\lib\\site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.3)\n",
      "Requirement already satisfied: executing in c:\\users\\weism\\anaconda3\\lib\\site-packages (from stack-data->ipython->ipython-sql) (0.8.3)\n",
      "Requirement already satisfied: asttokens in c:\\users\\weism\\anaconda3\\lib\\site-packages (from stack-data->ipython->ipython-sql) (2.0.5)\n",
      "Requirement already satisfied: pure-eval in c:\\users\\weism\\anaconda3\\lib\\site-packages (from stack-data->ipython->ipython-sql) (0.2.2)\n",
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "# Install and run the ipython sql module to run sql code\n",
    "!pip install ipython-sql \n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "15ff3878-a6e9-491c-a983-d2a21a6613f4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Connect to spend_save database using pymysql(Note: Password has been hidden after running code)\n",
    "%sql mysql+pymysql://root:****@localhost/spend_save"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84a3cf7e-7fe6-45e1-a126-2031b3770749",
   "metadata": {},
   "source": [
    "## Creation and Sample Output\n",
    "\n",
    "See how each view was created and the output produced below:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87bae898-4eed-46d6-a7b1-ecaff8fa83f5",
   "metadata": {},
   "source": [
    "### monthly_spend_summary\n",
    "\n",
    "Shows total spent per month, increase from prior month, and the running total for the year the month is in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f09feb16-5462-46f9-ba58-876e356a3208",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE VIEW monthly_spend_summary AS\n",
    "-- CTE to perform initial aggregation\n",
    "WITH cte AS (\n",
    "    SELECT\n",
    "        d.month_number,\n",
    "        d.month_name AS month,\n",
    "        d.year,\n",
    "        -- Take absolute value since transaction_amount is negative for purchases\n",
    "        SUM(ABS(tf.transaction_amount)) AS month_amount_spent\n",
    "    FROM transaction_facts AS tf\n",
    "    JOIN transaction_type AS t \n",
    "        USING (transaction_type_id)\n",
    "    -- Inner join with category filters out all transactions that are not purchases\n",
    "    JOIN category AS c\n",
    "        USING (category_id)\n",
    "    JOIN date AS d \n",
    "        USING (short_date)\n",
    "    -- Not interested in 2022\n",
    "    WHERE d.year != '2022'\n",
    "    GROUP BY month, d.month_number, d.year\n",
    "    ORDER BY d.year, d.month_number\n",
    ")\n",
    "SELECT\n",
    "    *,\n",
    "    -- Calculate dollar increase from prior month as decimal\n",
    "    ROUND((month_amount_spent - LAG(month_amount_spent)\n",
    "                                    OVER (ORDER BY year, month_number))\n",
    "        / LAG(month_amount_spent) OVER (ORDER BY year, month_number),\n",
    "          2) AS prior_month_change,\n",
    "    -- Calculate yearly running total spent\n",
    "    SUM(month_amount_spent)\n",
    "        OVER (\n",
    "            PARTITION BY year\n",
    "            ORDER BY year, month_number\n",
    "        ) AS yearly_running_total\n",
    "FROM cte;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "2d32252d-a413-4d39-ada4-0642db3be7bc",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>month_number</th>\n",
       "            <th>month</th>\n",
       "            <th>year</th>\n",
       "            <th>month_amount_spent</th>\n",
       "            <th>prior_month_change</th>\n",
       "            <th>yearly_running_total</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>2023</td>\n",
       "            <td>3554.27</td>\n",
       "            <td>None</td>\n",
       "            <td>3554.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>2023</td>\n",
       "            <td>3284.54</td>\n",
       "            <td>-0.08</td>\n",
       "            <td>6838.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>March</td>\n",
       "            <td>2023</td>\n",
       "            <td>4094.22</td>\n",
       "            <td>0.25</td>\n",
       "            <td>10933.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>April</td>\n",
       "            <td>2023</td>\n",
       "            <td>2603.66</td>\n",
       "            <td>-0.36</td>\n",
       "            <td>13536.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>May</td>\n",
       "            <td>2023</td>\n",
       "            <td>2366.55</td>\n",
       "            <td>-0.09</td>\n",
       "            <td>15903.24</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>June</td>\n",
       "            <td>2023</td>\n",
       "            <td>1522.43</td>\n",
       "            <td>-0.36</td>\n",
       "            <td>17425.67</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'January', 2023, Decimal('3554.27'), None, Decimal('3554.27')),\n",
       " (2, 'February', 2023, Decimal('3284.54'), Decimal('-0.08'), Decimal('6838.81')),\n",
       " (3, 'March', 2023, Decimal('4094.22'), Decimal('0.25'), Decimal('10933.03')),\n",
       " (4, 'April', 2023, Decimal('2603.66'), Decimal('-0.36'), Decimal('13536.69')),\n",
       " (5, 'May', 2023, Decimal('2366.55'), Decimal('-0.09'), Decimal('15903.24')),\n",
       " (6, 'June', 2023, Decimal('1522.43'), Decimal('-0.36'), Decimal('17425.67'))]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Output\n",
    "SELECT *\n",
    "FROM monthly_spend_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "088c7dc1-9e35-43e7-8344-4cf334c53d40",
   "metadata": {},
   "source": [
    "### monthly_spend_category\n",
    "\n",
    "Shows how much was spent per category per month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a7c0616d-06cb-4123-ad88-0924d08cf6ca",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE VIEW monthly_spend_category AS\n",
    "SELECT\n",
    "    d.year,\n",
    "    d.month_number,\n",
    "    d.month_name,\n",
    "    c.category_description,\n",
    "    -- Take absolute value since transaction_amount is negative for purchases\n",
    "    SUM(ABS(tf.transaction_amount)) AS monthly_spend,\n",
    "    -- Rank spending per category by month and year\n",
    "    RANK()\n",
    "        OVER(\n",
    "        PARTITION BY month_number, month_name, year \n",
    "            ORDER BY ABS(SUM(tf.transaction_amount)) DESC\n",
    "        ) AS month_ranking\n",
    "FROM transaction_facts AS tf\n",
    "-- Inner join with category filters out all non-purchase transactions\n",
    "JOIN category AS c\n",
    "    USING (category_id)\n",
    "JOIN account AS a \n",
    "    USING (account_id)\n",
    "JOIN transaction_type AS t \n",
    "    USING (transaction_type_id)\n",
    "JOIN date AS d \n",
    "    USING (short_date)\n",
    "-- Not interested in 2022\n",
    "WHERE d.year != '2022'\n",
    "GROUP BY month_number, month_name, year, category_description\n",
    "ORDER BY year, month_number, month_ranking;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "49ba59dd-fa00-40f5-b18b-22a3db27b64a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "30 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>year</th>\n",
       "            <th>month_number</th>\n",
       "            <th>month_name</th>\n",
       "            <th>category_description</th>\n",
       "            <th>monthly_spend</th>\n",
       "            <th>month_ranking</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>automotive</td>\n",
       "            <td>1092.83</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>food &amp; drink</td>\n",
       "            <td>870.74</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>travel</td>\n",
       "            <td>315.30</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>shopping</td>\n",
       "            <td>310.43</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>home</td>\n",
       "            <td>197.65</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>personal</td>\n",
       "            <td>153.76</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>gas</td>\n",
       "            <td>118.81</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>groceries</td>\n",
       "            <td>106.61</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>health &amp; wellness</td>\n",
       "            <td>105.79</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>bills &amp; utilities</td>\n",
       "            <td>74.23</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>miscellaneous</td>\n",
       "            <td>57.05</td>\n",
       "            <td>11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>gifts &amp; donations</td>\n",
       "            <td>51.89</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>professional services</td>\n",
       "            <td>49.58</td>\n",
       "            <td>13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>education</td>\n",
       "            <td>44.56</td>\n",
       "            <td>14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>1</td>\n",
       "            <td>January</td>\n",
       "            <td>entertainment</td>\n",
       "            <td>5.04</td>\n",
       "            <td>15</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>groceries</td>\n",
       "            <td>1689.66</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>food &amp; drink</td>\n",
       "            <td>611.69</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>travel</td>\n",
       "            <td>283.32</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>health &amp; wellness</td>\n",
       "            <td>252.63</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>entertainment</td>\n",
       "            <td>76.18</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>home</td>\n",
       "            <td>74.32</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>education</td>\n",
       "            <td>64.90</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>gifts &amp; donations</td>\n",
       "            <td>53.73</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>automotive</td>\n",
       "            <td>43.59</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>gas</td>\n",
       "            <td>38.27</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>professional services</td>\n",
       "            <td>34.77</td>\n",
       "            <td>11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>miscellaneous</td>\n",
       "            <td>23.02</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>personal</td>\n",
       "            <td>21.48</td>\n",
       "            <td>13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>shopping</td>\n",
       "            <td>10.12</td>\n",
       "            <td>14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023</td>\n",
       "            <td>2</td>\n",
       "            <td>February</td>\n",
       "            <td>bills &amp; utilities</td>\n",
       "            <td>6.86</td>\n",
       "            <td>15</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2023, 1, 'January', 'automotive', Decimal('1092.83'), 1),\n",
       " (2023, 1, 'January', 'food & drink', Decimal('870.74'), 2),\n",
       " (2023, 1, 'January', 'travel', Decimal('315.30'), 3),\n",
       " (2023, 1, 'January', 'shopping', Decimal('310.43'), 4),\n",
       " (2023, 1, 'January', 'home', Decimal('197.65'), 5),\n",
       " (2023, 1, 'January', 'personal', Decimal('153.76'), 6),\n",
       " (2023, 1, 'January', 'gas', Decimal('118.81'), 7),\n",
       " (2023, 1, 'January', 'groceries', Decimal('106.61'), 8),\n",
       " (2023, 1, 'January', 'health & wellness', Decimal('105.79'), 9),\n",
       " (2023, 1, 'January', 'bills & utilities', Decimal('74.23'), 10),\n",
       " (2023, 1, 'January', 'miscellaneous', Decimal('57.05'), 11),\n",
       " (2023, 1, 'January', 'gifts & donations', Decimal('51.89'), 12),\n",
       " (2023, 1, 'January', 'professional services', Decimal('49.58'), 13),\n",
       " (2023, 1, 'January', 'education', Decimal('44.56'), 14),\n",
       " (2023, 1, 'January', 'entertainment', Decimal('5.04'), 15),\n",
       " (2023, 2, 'February', 'groceries', Decimal('1689.66'), 1),\n",
       " (2023, 2, 'February', 'food & drink', Decimal('611.69'), 2),\n",
       " (2023, 2, 'February', 'travel', Decimal('283.32'), 3),\n",
       " (2023, 2, 'February', 'health & wellness', Decimal('252.63'), 4),\n",
       " (2023, 2, 'February', 'entertainment', Decimal('76.18'), 5),\n",
       " (2023, 2, 'February', 'home', Decimal('74.32'), 6),\n",
       " (2023, 2, 'February', 'education', Decimal('64.90'), 7),\n",
       " (2023, 2, 'February', 'gifts & donations', Decimal('53.73'), 8),\n",
       " (2023, 2, 'February', 'automotive', Decimal('43.59'), 9),\n",
       " (2023, 2, 'February', 'gas', Decimal('38.27'), 10),\n",
       " (2023, 2, 'February', 'professional services', Decimal('34.77'), 11),\n",
       " (2023, 2, 'February', 'miscellaneous', Decimal('23.02'), 12),\n",
       " (2023, 2, 'February', 'personal', Decimal('21.48'), 13),\n",
       " (2023, 2, 'February', 'shopping', Decimal('10.12'), 14),\n",
       " (2023, 2, 'February', 'bills & utilities', Decimal('6.86'), 15)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Output\n",
    "SELECT *\n",
    "FROM monthly_spend_category\n",
    "WHERE month_number < 3        -- Limit output to first 2 months due to large amount of data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78d71901-eab0-4fe1-8b26-4449e61fc7ec",
   "metadata": {},
   "source": [
    "### monthly_account_balances\n",
    "\n",
    "Shows the balance of checking account, savings account, and credit card at end of every month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3e6010f8-14ef-4560-ab6c-54e980573f5c",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE VIEW monthly_account_balances AS\n",
    "SELECT\n",
    "    -- Combine month and year to get date period\n",
    "    CONCAT(year, '-' , LPAD(month_number, 2, '0')) AS end_date_period,\n",
    "    tf.account_id,\n",
    "    account_type,\n",
    "    -- Running total represents account balances\n",
    "    SUM(SUM(transaction_amount))\n",
    "    OVER(\n",
    "    PARTITION BY tf.account_id \n",
    "            ORDER BY CONCAT(year, '-' , LPAD(month_number, 2, '0'))\n",
    "    ) AS balance\n",
    "FROM transaction_facts AS tf\n",
    "JOIN account \n",
    "    USING (account_id)\n",
    "JOIN date \n",
    "    USING (short_date)\n",
    "GROUP BY end_date_period, account_id, account_type\n",
    "ORDER BY end_date_period;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "6ccef906-b20a-4669-9a35-68222a21eb35",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "21 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>end_date_period</th>\n",
       "            <th>account_id</th>\n",
       "            <th>account_type</th>\n",
       "            <th>balance</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2022-12</td>\n",
       "            <td>1</td>\n",
       "            <td>credit card</td>\n",
       "            <td>-1538.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2022-12</td>\n",
       "            <td>2</td>\n",
       "            <td>checking</td>\n",
       "            <td>8262.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2022-12</td>\n",
       "            <td>3</td>\n",
       "            <td>saving</td>\n",
       "            <td>10821.14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01</td>\n",
       "            <td>1</td>\n",
       "            <td>credit card</td>\n",
       "            <td>-1455.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01</td>\n",
       "            <td>2</td>\n",
       "            <td>checking</td>\n",
       "            <td>24039.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01</td>\n",
       "            <td>3</td>\n",
       "            <td>saving</td>\n",
       "            <td>11075.15</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02</td>\n",
       "            <td>1</td>\n",
       "            <td>credit card</td>\n",
       "            <td>-1487.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02</td>\n",
       "            <td>2</td>\n",
       "            <td>checking</td>\n",
       "            <td>34304.80</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02</td>\n",
       "            <td>3</td>\n",
       "            <td>saving</td>\n",
       "            <td>13654.16</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03</td>\n",
       "            <td>1</td>\n",
       "            <td>credit card</td>\n",
       "            <td>-1252.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03</td>\n",
       "            <td>2</td>\n",
       "            <td>checking</td>\n",
       "            <td>28436.51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03</td>\n",
       "            <td>3</td>\n",
       "            <td>saving</td>\n",
       "            <td>19562.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04</td>\n",
       "            <td>1</td>\n",
       "            <td>credit card</td>\n",
       "            <td>-1532.46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04</td>\n",
       "            <td>2</td>\n",
       "            <td>checking</td>\n",
       "            <td>25963.57</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04</td>\n",
       "            <td>3</td>\n",
       "            <td>saving</td>\n",
       "            <td>26348.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05</td>\n",
       "            <td>1</td>\n",
       "            <td>credit card</td>\n",
       "            <td>-738.68</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05</td>\n",
       "            <td>2</td>\n",
       "            <td>checking</td>\n",
       "            <td>23030.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05</td>\n",
       "            <td>3</td>\n",
       "            <td>saving</td>\n",
       "            <td>32344.45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06</td>\n",
       "            <td>1</td>\n",
       "            <td>credit card</td>\n",
       "            <td>-1580.19</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06</td>\n",
       "            <td>2</td>\n",
       "            <td>checking</td>\n",
       "            <td>20880.90</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06</td>\n",
       "            <td>3</td>\n",
       "            <td>saving</td>\n",
       "            <td>36127.45</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2022-12', 1, 'credit card', Decimal('-1538.21')),\n",
       " ('2022-12', 2, 'checking', Decimal('8262.03')),\n",
       " ('2022-12', 3, 'saving', Decimal('10821.14')),\n",
       " ('2023-01', 1, 'credit card', Decimal('-1455.67')),\n",
       " ('2023-01', 2, 'checking', Decimal('24039.04')),\n",
       " ('2023-01', 3, 'saving', Decimal('11075.15')),\n",
       " ('2023-02', 1, 'credit card', Decimal('-1487.31')),\n",
       " ('2023-02', 2, 'checking', Decimal('34304.80')),\n",
       " ('2023-02', 3, 'saving', Decimal('13654.16')),\n",
       " ('2023-03', 1, 'credit card', Decimal('-1252.27')),\n",
       " ('2023-03', 2, 'checking', Decimal('28436.51')),\n",
       " ('2023-03', 3, 'saving', Decimal('19562.21')),\n",
       " ('2023-04', 1, 'credit card', Decimal('-1532.46')),\n",
       " ('2023-04', 2, 'checking', Decimal('25963.57')),\n",
       " ('2023-04', 3, 'saving', Decimal('26348.27')),\n",
       " ('2023-05', 1, 'credit card', Decimal('-738.68')),\n",
       " ('2023-05', 2, 'checking', Decimal('23030.25')),\n",
       " ('2023-05', 3, 'saving', Decimal('32344.45')),\n",
       " ('2023-06', 1, 'credit card', Decimal('-1580.19')),\n",
       " ('2023-06', 2, 'checking', Decimal('20880.90')),\n",
       " ('2023-06', 3, 'saving', Decimal('36127.45'))]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Output\n",
    "SELECT *\n",
    "FROM monthly_account_balances"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "102adb19-a9d6-4ddf-89d6-edb321d11da7",
   "metadata": {},
   "source": [
    "### daily_spend\n",
    "\n",
    "Shows all transaction on a given day, 7 day running average amount spent, and yearly running amount spent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "dc1a35e3-29e5-4ac1-94b5-5b5db97f98e8",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE VIEW daily_spend AS\n",
    "SELECT\n",
    "    d.short_date,\n",
    "    -- Get daily spend amount. Take abs since transaction_amount is negative for purchases \n",
    "    -- Assign 0 for days with no purchases.\n",
    "    COALESCE(ABS(SUM(tf.transaction_amount)), 0) AS date_spend,\n",
    "    -- Get 7 day running average spent. Assign 0 to days with no transactions.    \n",
    "    ROUND(AVG(ABS(SUM(COALESCE(tf.transaction_amount, 0))))\n",
    "        OVER (\n",
    "            ORDER BY short_date\n",
    "            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n",
    "        ), 2) AS 7_day_avg_spend,\n",
    "    -- Sum total spent by year up to the current date\n",
    "    SUM(ABS(SUM(tf.transaction_amount)))\n",
    "        OVER (\n",
    "            PARTITION BY d.year \n",
    "            ORDER BY short_date\n",
    "        ) AS running_yearly_spend\n",
    "-- Select from date column to keep all dates, not just ones with purchases\n",
    "FROM date AS d\n",
    "-- Use left join to keep dates mentioned above. Also need to filter out non-purchases since left join was used\n",
    "LEFT JOIN transaction_facts AS tf\n",
    "    ON d.short_date = tf.short_date\n",
    "    AND tf.transaction_type_id IN (1,2)\n",
    "WHERE d.short_date <= CURDATE()\n",
    "    -- Not interested in 2022\n",
    "    AND year != '2022'\n",
    "GROUP BY d.short_date\n",
    "ORDER BY short_date;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "53b75026-057e-4fe5-874f-9c2ea5f93390",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "177 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>short_date</th>\n",
       "            <th>date_spend</th>\n",
       "            <th>7_day_avg_spend</th>\n",
       "            <th>running_yearly_spend</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>0.00</td>\n",
       "            <td>0.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>26.85</td>\n",
       "            <td>13.43</td>\n",
       "            <td>26.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>54.73</td>\n",
       "            <td>27.19</td>\n",
       "            <td>81.58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>0.00</td>\n",
       "            <td>20.40</td>\n",
       "            <td>81.58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>99.70</td>\n",
       "            <td>36.26</td>\n",
       "            <td>181.28</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>19.41</td>\n",
       "            <td>33.45</td>\n",
       "            <td>200.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>0.00</td>\n",
       "            <td>28.67</td>\n",
       "            <td>200.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>0.00</td>\n",
       "            <td>28.67</td>\n",
       "            <td>200.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>18.95</td>\n",
       "            <td>27.54</td>\n",
       "            <td>219.64</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>48.31</td>\n",
       "            <td>26.62</td>\n",
       "            <td>267.95</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-11</td>\n",
       "            <td>54.74</td>\n",
       "            <td>34.44</td>\n",
       "            <td>322.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-12</td>\n",
       "            <td>9.28</td>\n",
       "            <td>21.53</td>\n",
       "            <td>331.97</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-13</td>\n",
       "            <td>0.00</td>\n",
       "            <td>18.75</td>\n",
       "            <td>331.97</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-14</td>\n",
       "            <td>0.00</td>\n",
       "            <td>18.75</td>\n",
       "            <td>331.97</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-15</td>\n",
       "            <td>106.91</td>\n",
       "            <td>34.03</td>\n",
       "            <td>438.88</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-16</td>\n",
       "            <td>83.06</td>\n",
       "            <td>43.19</td>\n",
       "            <td>521.94</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-17</td>\n",
       "            <td>275.62</td>\n",
       "            <td>75.66</td>\n",
       "            <td>797.56</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-18</td>\n",
       "            <td>1042.04</td>\n",
       "            <td>216.70</td>\n",
       "            <td>1839.60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-19</td>\n",
       "            <td>212.08</td>\n",
       "            <td>245.67</td>\n",
       "            <td>2051.68</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-20</td>\n",
       "            <td>253.46</td>\n",
       "            <td>281.88</td>\n",
       "            <td>2305.14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-21</td>\n",
       "            <td>79.43</td>\n",
       "            <td>293.23</td>\n",
       "            <td>2384.57</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-22</td>\n",
       "            <td>157.63</td>\n",
       "            <td>300.47</td>\n",
       "            <td>2542.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-23</td>\n",
       "            <td>289.85</td>\n",
       "            <td>330.02</td>\n",
       "            <td>2832.05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-24</td>\n",
       "            <td>197.65</td>\n",
       "            <td>318.88</td>\n",
       "            <td>3029.70</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-25</td>\n",
       "            <td>57.05</td>\n",
       "            <td>178.16</td>\n",
       "            <td>3086.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-26</td>\n",
       "            <td>125.20</td>\n",
       "            <td>165.75</td>\n",
       "            <td>3211.95</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-27</td>\n",
       "            <td>49.58</td>\n",
       "            <td>136.63</td>\n",
       "            <td>3261.53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-28</td>\n",
       "            <td>174.27</td>\n",
       "            <td>150.18</td>\n",
       "            <td>3435.80</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-29</td>\n",
       "            <td>118.47</td>\n",
       "            <td>144.58</td>\n",
       "            <td>3554.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-30</td>\n",
       "            <td>0.00</td>\n",
       "            <td>103.17</td>\n",
       "            <td>3554.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-31</td>\n",
       "            <td>0.00</td>\n",
       "            <td>74.94</td>\n",
       "            <td>3554.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-01</td>\n",
       "            <td>0.00</td>\n",
       "            <td>66.79</td>\n",
       "            <td>3554.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-02</td>\n",
       "            <td>0.00</td>\n",
       "            <td>48.90</td>\n",
       "            <td>3554.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-03</td>\n",
       "            <td>0.00</td>\n",
       "            <td>41.82</td>\n",
       "            <td>3554.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-04</td>\n",
       "            <td>24.58</td>\n",
       "            <td>20.44</td>\n",
       "            <td>3578.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-05</td>\n",
       "            <td>94.96</td>\n",
       "            <td>17.08</td>\n",
       "            <td>3673.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-06</td>\n",
       "            <td>45.00</td>\n",
       "            <td>23.51</td>\n",
       "            <td>3718.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-07</td>\n",
       "            <td>89.10</td>\n",
       "            <td>36.23</td>\n",
       "            <td>3807.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-08</td>\n",
       "            <td>0.00</td>\n",
       "            <td>36.23</td>\n",
       "            <td>3807.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-09</td>\n",
       "            <td>0.00</td>\n",
       "            <td>36.23</td>\n",
       "            <td>3807.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-10</td>\n",
       "            <td>27.67</td>\n",
       "            <td>40.19</td>\n",
       "            <td>3835.58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-11</td>\n",
       "            <td>49.96</td>\n",
       "            <td>43.81</td>\n",
       "            <td>3885.54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-12</td>\n",
       "            <td>152.10</td>\n",
       "            <td>51.98</td>\n",
       "            <td>4037.64</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-13</td>\n",
       "            <td>22.70</td>\n",
       "            <td>48.79</td>\n",
       "            <td>4060.34</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-14</td>\n",
       "            <td>109.94</td>\n",
       "            <td>51.77</td>\n",
       "            <td>4170.28</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-15</td>\n",
       "            <td>1534.65</td>\n",
       "            <td>271.00</td>\n",
       "            <td>5704.93</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-16</td>\n",
       "            <td>35.12</td>\n",
       "            <td>276.02</td>\n",
       "            <td>5740.05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-17</td>\n",
       "            <td>95.64</td>\n",
       "            <td>285.73</td>\n",
       "            <td>5835.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-18</td>\n",
       "            <td>91.22</td>\n",
       "            <td>291.62</td>\n",
       "            <td>5926.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-19</td>\n",
       "            <td>38.27</td>\n",
       "            <td>275.36</td>\n",
       "            <td>5965.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-20</td>\n",
       "            <td>53.73</td>\n",
       "            <td>279.80</td>\n",
       "            <td>6018.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-21</td>\n",
       "            <td>74.84</td>\n",
       "            <td>274.78</td>\n",
       "            <td>6093.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-22</td>\n",
       "            <td>182.96</td>\n",
       "            <td>81.68</td>\n",
       "            <td>6276.71</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-23</td>\n",
       "            <td>74.32</td>\n",
       "            <td>87.28</td>\n",
       "            <td>6351.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-24</td>\n",
       "            <td>62.04</td>\n",
       "            <td>82.48</td>\n",
       "            <td>6413.07</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-25</td>\n",
       "            <td>174.45</td>\n",
       "            <td>94.37</td>\n",
       "            <td>6587.52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-26</td>\n",
       "            <td>97.41</td>\n",
       "            <td>102.82</td>\n",
       "            <td>6684.93</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-27</td>\n",
       "            <td>118.88</td>\n",
       "            <td>112.13</td>\n",
       "            <td>6803.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-02-28</td>\n",
       "            <td>35.00</td>\n",
       "            <td>106.44</td>\n",
       "            <td>6838.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-01</td>\n",
       "            <td>0.00</td>\n",
       "            <td>80.30</td>\n",
       "            <td>6838.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-02</td>\n",
       "            <td>0.00</td>\n",
       "            <td>69.68</td>\n",
       "            <td>6838.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-03</td>\n",
       "            <td>302.57</td>\n",
       "            <td>104.04</td>\n",
       "            <td>7141.38</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-04</td>\n",
       "            <td>360.31</td>\n",
       "            <td>130.60</td>\n",
       "            <td>7501.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-05</td>\n",
       "            <td>126.67</td>\n",
       "            <td>134.78</td>\n",
       "            <td>7628.36</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-06</td>\n",
       "            <td>23.47</td>\n",
       "            <td>121.15</td>\n",
       "            <td>7651.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-07</td>\n",
       "            <td>39.02</td>\n",
       "            <td>121.72</td>\n",
       "            <td>7690.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-08</td>\n",
       "            <td>0.00</td>\n",
       "            <td>121.72</td>\n",
       "            <td>7690.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-09</td>\n",
       "            <td>11.35</td>\n",
       "            <td>123.34</td>\n",
       "            <td>7702.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-10</td>\n",
       "            <td>0.00</td>\n",
       "            <td>80.12</td>\n",
       "            <td>7702.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-11</td>\n",
       "            <td>60.93</td>\n",
       "            <td>37.35</td>\n",
       "            <td>7763.13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-12</td>\n",
       "            <td>201.33</td>\n",
       "            <td>48.01</td>\n",
       "            <td>7964.46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-13</td>\n",
       "            <td>151.75</td>\n",
       "            <td>66.34</td>\n",
       "            <td>8116.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-14</td>\n",
       "            <td>0.00</td>\n",
       "            <td>60.77</td>\n",
       "            <td>8116.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-15</td>\n",
       "            <td>64.06</td>\n",
       "            <td>69.92</td>\n",
       "            <td>8180.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-16</td>\n",
       "            <td>123.77</td>\n",
       "            <td>85.98</td>\n",
       "            <td>8304.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-17</td>\n",
       "            <td>587.97</td>\n",
       "            <td>169.97</td>\n",
       "            <td>8892.01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-18</td>\n",
       "            <td>601.76</td>\n",
       "            <td>247.23</td>\n",
       "            <td>9493.77</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-19</td>\n",
       "            <td>203.03</td>\n",
       "            <td>247.48</td>\n",
       "            <td>9696.80</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-20</td>\n",
       "            <td>158.92</td>\n",
       "            <td>248.50</td>\n",
       "            <td>9855.72</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-21</td>\n",
       "            <td>160.24</td>\n",
       "            <td>271.39</td>\n",
       "            <td>10015.96</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-22</td>\n",
       "            <td>83.01</td>\n",
       "            <td>274.10</td>\n",
       "            <td>10098.97</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-23</td>\n",
       "            <td>167.32</td>\n",
       "            <td>280.32</td>\n",
       "            <td>10266.29</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-24</td>\n",
       "            <td>52.38</td>\n",
       "            <td>203.81</td>\n",
       "            <td>10318.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-25</td>\n",
       "            <td>55.50</td>\n",
       "            <td>125.77</td>\n",
       "            <td>10374.17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-26</td>\n",
       "            <td>59.64</td>\n",
       "            <td>105.29</td>\n",
       "            <td>10433.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-27</td>\n",
       "            <td>177.01</td>\n",
       "            <td>107.87</td>\n",
       "            <td>10610.82</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-28</td>\n",
       "            <td>120.08</td>\n",
       "            <td>102.13</td>\n",
       "            <td>10730.90</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-29</td>\n",
       "            <td>167.85</td>\n",
       "            <td>114.25</td>\n",
       "            <td>10898.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-30</td>\n",
       "            <td>0.00</td>\n",
       "            <td>90.35</td>\n",
       "            <td>10898.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-03-31</td>\n",
       "            <td>34.28</td>\n",
       "            <td>87.77</td>\n",
       "            <td>10933.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-01</td>\n",
       "            <td>21.18</td>\n",
       "            <td>82.86</td>\n",
       "            <td>10954.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-02</td>\n",
       "            <td>120.01</td>\n",
       "            <td>91.49</td>\n",
       "            <td>11074.22</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-03</td>\n",
       "            <td>83.99</td>\n",
       "            <td>78.20</td>\n",
       "            <td>11158.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-04</td>\n",
       "            <td>4.97</td>\n",
       "            <td>61.75</td>\n",
       "            <td>11163.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-05</td>\n",
       "            <td>139.58</td>\n",
       "            <td>57.72</td>\n",
       "            <td>11302.76</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-06</td>\n",
       "            <td>85.40</td>\n",
       "            <td>69.92</td>\n",
       "            <td>11388.16</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-07</td>\n",
       "            <td>125.88</td>\n",
       "            <td>83.00</td>\n",
       "            <td>11514.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-08</td>\n",
       "            <td>45.49</td>\n",
       "            <td>86.47</td>\n",
       "            <td>11559.53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-09</td>\n",
       "            <td>67.37</td>\n",
       "            <td>78.95</td>\n",
       "            <td>11626.90</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-10</td>\n",
       "            <td>135.46</td>\n",
       "            <td>86.31</td>\n",
       "            <td>11762.36</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-11</td>\n",
       "            <td>58.04</td>\n",
       "            <td>93.89</td>\n",
       "            <td>11820.40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-12</td>\n",
       "            <td>42.61</td>\n",
       "            <td>80.04</td>\n",
       "            <td>11863.01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-13</td>\n",
       "            <td>172.25</td>\n",
       "            <td>92.44</td>\n",
       "            <td>12035.26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-14</td>\n",
       "            <td>226.82</td>\n",
       "            <td>106.86</td>\n",
       "            <td>12262.08</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-15</td>\n",
       "            <td>144.49</td>\n",
       "            <td>121.01</td>\n",
       "            <td>12406.57</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-16</td>\n",
       "            <td>23.03</td>\n",
       "            <td>114.67</td>\n",
       "            <td>12429.60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-17</td>\n",
       "            <td>0.00</td>\n",
       "            <td>95.32</td>\n",
       "            <td>12429.60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-18</td>\n",
       "            <td>2.25</td>\n",
       "            <td>87.35</td>\n",
       "            <td>12431.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-19</td>\n",
       "            <td>103.41</td>\n",
       "            <td>96.04</td>\n",
       "            <td>12535.26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-20</td>\n",
       "            <td>0.00</td>\n",
       "            <td>71.43</td>\n",
       "            <td>12535.26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-21</td>\n",
       "            <td>36.14</td>\n",
       "            <td>44.19</td>\n",
       "            <td>12571.40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-22</td>\n",
       "            <td>0.00</td>\n",
       "            <td>23.55</td>\n",
       "            <td>12571.40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-23</td>\n",
       "            <td>18.16</td>\n",
       "            <td>22.85</td>\n",
       "            <td>12589.56</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-24</td>\n",
       "            <td>204.30</td>\n",
       "            <td>52.04</td>\n",
       "            <td>12793.86</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>0.00</td>\n",
       "            <td>51.72</td>\n",
       "            <td>12793.86</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-26</td>\n",
       "            <td>702.34</td>\n",
       "            <td>137.28</td>\n",
       "            <td>13496.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-27</td>\n",
       "            <td>9.28</td>\n",
       "            <td>138.60</td>\n",
       "            <td>13505.48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-28</td>\n",
       "            <td>31.21</td>\n",
       "            <td>137.90</td>\n",
       "            <td>13536.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-29</td>\n",
       "            <td>0.00</td>\n",
       "            <td>137.90</td>\n",
       "            <td>13536.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-04-30</td>\n",
       "            <td>0.00</td>\n",
       "            <td>135.30</td>\n",
       "            <td>13536.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-01</td>\n",
       "            <td>33.98</td>\n",
       "            <td>110.97</td>\n",
       "            <td>13570.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-02</td>\n",
       "            <td>0.00</td>\n",
       "            <td>110.97</td>\n",
       "            <td>13570.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-03</td>\n",
       "            <td>0.00</td>\n",
       "            <td>10.64</td>\n",
       "            <td>13570.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-04</td>\n",
       "            <td>22.23</td>\n",
       "            <td>12.49</td>\n",
       "            <td>13592.90</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-05</td>\n",
       "            <td>75.17</td>\n",
       "            <td>18.77</td>\n",
       "            <td>13668.07</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-06</td>\n",
       "            <td>57.34</td>\n",
       "            <td>26.96</td>\n",
       "            <td>13725.41</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-07</td>\n",
       "            <td>26.47</td>\n",
       "            <td>30.74</td>\n",
       "            <td>13751.88</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-08</td>\n",
       "            <td>9.28</td>\n",
       "            <td>27.21</td>\n",
       "            <td>13761.16</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-09</td>\n",
       "            <td>0.00</td>\n",
       "            <td>27.21</td>\n",
       "            <td>13761.16</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-10</td>\n",
       "            <td>2.27</td>\n",
       "            <td>27.54</td>\n",
       "            <td>13763.43</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-11</td>\n",
       "            <td>8.60</td>\n",
       "            <td>25.59</td>\n",
       "            <td>13772.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-12</td>\n",
       "            <td>19.28</td>\n",
       "            <td>17.61</td>\n",
       "            <td>13791.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-13</td>\n",
       "            <td>0.00</td>\n",
       "            <td>9.41</td>\n",
       "            <td>13791.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-14</td>\n",
       "            <td>0.00</td>\n",
       "            <td>5.63</td>\n",
       "            <td>13791.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-15</td>\n",
       "            <td>108.44</td>\n",
       "            <td>19.80</td>\n",
       "            <td>13899.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-16</td>\n",
       "            <td>160.32</td>\n",
       "            <td>42.70</td>\n",
       "            <td>14060.07</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-17</td>\n",
       "            <td>10.90</td>\n",
       "            <td>43.93</td>\n",
       "            <td>14070.97</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-18</td>\n",
       "            <td>132.45</td>\n",
       "            <td>61.63</td>\n",
       "            <td>14203.42</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-19</td>\n",
       "            <td>77.66</td>\n",
       "            <td>69.97</td>\n",
       "            <td>14281.08</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-20</td>\n",
       "            <td>101.04</td>\n",
       "            <td>84.40</td>\n",
       "            <td>14382.12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-21</td>\n",
       "            <td>73.25</td>\n",
       "            <td>94.87</td>\n",
       "            <td>14455.37</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-22</td>\n",
       "            <td>217.38</td>\n",
       "            <td>110.43</td>\n",
       "            <td>14672.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-23</td>\n",
       "            <td>68.30</td>\n",
       "            <td>97.28</td>\n",
       "            <td>14741.05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-24</td>\n",
       "            <td>59.59</td>\n",
       "            <td>104.24</td>\n",
       "            <td>14800.64</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-25</td>\n",
       "            <td>354.75</td>\n",
       "            <td>136.00</td>\n",
       "            <td>15155.39</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-26</td>\n",
       "            <td>168.73</td>\n",
       "            <td>149.01</td>\n",
       "            <td>15324.12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-27</td>\n",
       "            <td>302.09</td>\n",
       "            <td>177.73</td>\n",
       "            <td>15626.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-28</td>\n",
       "            <td>37.26</td>\n",
       "            <td>172.59</td>\n",
       "            <td>15663.47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-29</td>\n",
       "            <td>239.77</td>\n",
       "            <td>175.78</td>\n",
       "            <td>15903.24</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-30</td>\n",
       "            <td>0.00</td>\n",
       "            <td>166.03</td>\n",
       "            <td>15903.24</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-05-31</td>\n",
       "            <td>0.00</td>\n",
       "            <td>157.51</td>\n",
       "            <td>15903.24</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-01</td>\n",
       "            <td>0.00</td>\n",
       "            <td>106.84</td>\n",
       "            <td>15903.24</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-02</td>\n",
       "            <td>0.00</td>\n",
       "            <td>82.73</td>\n",
       "            <td>15903.24</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-03</td>\n",
       "            <td>37.59</td>\n",
       "            <td>44.95</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-04</td>\n",
       "            <td>0.00</td>\n",
       "            <td>39.62</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-05</td>\n",
       "            <td>0.00</td>\n",
       "            <td>5.37</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-06</td>\n",
       "            <td>0.00</td>\n",
       "            <td>5.37</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-07</td>\n",
       "            <td>0.00</td>\n",
       "            <td>5.37</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-08</td>\n",
       "            <td>0.00</td>\n",
       "            <td>5.37</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-09</td>\n",
       "            <td>0.00</td>\n",
       "            <td>5.37</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-10</td>\n",
       "            <td>0.00</td>\n",
       "            <td>0.00</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-11</td>\n",
       "            <td>0.00</td>\n",
       "            <td>0.00</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-12</td>\n",
       "            <td>0.00</td>\n",
       "            <td>0.00</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-13</td>\n",
       "            <td>0.00</td>\n",
       "            <td>0.00</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-14</td>\n",
       "            <td>0.00</td>\n",
       "            <td>0.00</td>\n",
       "            <td>15940.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-15</td>\n",
       "            <td>199.60</td>\n",
       "            <td>28.51</td>\n",
       "            <td>16140.43</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-16</td>\n",
       "            <td>201.03</td>\n",
       "            <td>57.23</td>\n",
       "            <td>16341.46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-17</td>\n",
       "            <td>147.94</td>\n",
       "            <td>78.37</td>\n",
       "            <td>16489.40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-18</td>\n",
       "            <td>171.39</td>\n",
       "            <td>102.85</td>\n",
       "            <td>16660.79</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-19</td>\n",
       "            <td>191.63</td>\n",
       "            <td>130.23</td>\n",
       "            <td>16852.42</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-20</td>\n",
       "            <td>139.90</td>\n",
       "            <td>150.21</td>\n",
       "            <td>16992.32</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-21</td>\n",
       "            <td>4.99</td>\n",
       "            <td>150.93</td>\n",
       "            <td>16997.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-22</td>\n",
       "            <td>116.63</td>\n",
       "            <td>139.07</td>\n",
       "            <td>17113.94</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-23</td>\n",
       "            <td>311.73</td>\n",
       "            <td>154.89</td>\n",
       "            <td>17425.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-24</td>\n",
       "            <td>0.00</td>\n",
       "            <td>133.75</td>\n",
       "            <td>17425.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-25</td>\n",
       "            <td>0.00</td>\n",
       "            <td>109.27</td>\n",
       "            <td>17425.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-06-26</td>\n",
       "            <td>0.00</td>\n",
       "            <td>81.89</td>\n",
       "            <td>17425.67</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2023, 1, 1), Decimal('0.00'), Decimal('0.00'), None),\n",
       " (datetime.date(2023, 1, 2), Decimal('26.85'), Decimal('13.43'), Decimal('26.85')),\n",
       " (datetime.date(2023, 1, 3), Decimal('54.73'), Decimal('27.19'), Decimal('81.58')),\n",
       " (datetime.date(2023, 1, 4), Decimal('0.00'), Decimal('20.40'), Decimal('81.58')),\n",
       " (datetime.date(2023, 1, 5), Decimal('99.70'), Decimal('36.26'), Decimal('181.28')),\n",
       " (datetime.date(2023, 1, 6), Decimal('19.41'), Decimal('33.45'), Decimal('200.69')),\n",
       " (datetime.date(2023, 1, 7), Decimal('0.00'), Decimal('28.67'), Decimal('200.69')),\n",
       " (datetime.date(2023, 1, 8), Decimal('0.00'), Decimal('28.67'), Decimal('200.69')),\n",
       " (datetime.date(2023, 1, 9), Decimal('18.95'), Decimal('27.54'), Decimal('219.64')),\n",
       " (datetime.date(2023, 1, 10), Decimal('48.31'), Decimal('26.62'), Decimal('267.95')),\n",
       " (datetime.date(2023, 1, 11), Decimal('54.74'), Decimal('34.44'), Decimal('322.69')),\n",
       " (datetime.date(2023, 1, 12), Decimal('9.28'), Decimal('21.53'), Decimal('331.97')),\n",
       " (datetime.date(2023, 1, 13), Decimal('0.00'), Decimal('18.75'), Decimal('331.97')),\n",
       " (datetime.date(2023, 1, 14), Decimal('0.00'), Decimal('18.75'), Decimal('331.97')),\n",
       " (datetime.date(2023, 1, 15), Decimal('106.91'), Decimal('34.03'), Decimal('438.88')),\n",
       " (datetime.date(2023, 1, 16), Decimal('83.06'), Decimal('43.19'), Decimal('521.94')),\n",
       " (datetime.date(2023, 1, 17), Decimal('275.62'), Decimal('75.66'), Decimal('797.56')),\n",
       " (datetime.date(2023, 1, 18), Decimal('1042.04'), Decimal('216.70'), Decimal('1839.60')),\n",
       " (datetime.date(2023, 1, 19), Decimal('212.08'), Decimal('245.67'), Decimal('2051.68')),\n",
       " (datetime.date(2023, 1, 20), Decimal('253.46'), Decimal('281.88'), Decimal('2305.14')),\n",
       " (datetime.date(2023, 1, 21), Decimal('79.43'), Decimal('293.23'), Decimal('2384.57')),\n",
       " (datetime.date(2023, 1, 22), Decimal('157.63'), Decimal('300.47'), Decimal('2542.20')),\n",
       " (datetime.date(2023, 1, 23), Decimal('289.85'), Decimal('330.02'), Decimal('2832.05')),\n",
       " (datetime.date(2023, 1, 24), Decimal('197.65'), Decimal('318.88'), Decimal('3029.70')),\n",
       " (datetime.date(2023, 1, 25), Decimal('57.05'), Decimal('178.16'), Decimal('3086.75')),\n",
       " (datetime.date(2023, 1, 26), Decimal('125.20'), Decimal('165.75'), Decimal('3211.95')),\n",
       " (datetime.date(2023, 1, 27), Decimal('49.58'), Decimal('136.63'), Decimal('3261.53')),\n",
       " (datetime.date(2023, 1, 28), Decimal('174.27'), Decimal('150.18'), Decimal('3435.80')),\n",
       " (datetime.date(2023, 1, 29), Decimal('118.47'), Decimal('144.58'), Decimal('3554.27')),\n",
       " (datetime.date(2023, 1, 30), Decimal('0.00'), Decimal('103.17'), Decimal('3554.27')),\n",
       " (datetime.date(2023, 1, 31), Decimal('0.00'), Decimal('74.94'), Decimal('3554.27')),\n",
       " (datetime.date(2023, 2, 1), Decimal('0.00'), Decimal('66.79'), Decimal('3554.27')),\n",
       " (datetime.date(2023, 2, 2), Decimal('0.00'), Decimal('48.90'), Decimal('3554.27')),\n",
       " (datetime.date(2023, 2, 3), Decimal('0.00'), Decimal('41.82'), Decimal('3554.27')),\n",
       " (datetime.date(2023, 2, 4), Decimal('24.58'), Decimal('20.44'), Decimal('3578.85')),\n",
       " (datetime.date(2023, 2, 5), Decimal('94.96'), Decimal('17.08'), Decimal('3673.81')),\n",
       " (datetime.date(2023, 2, 6), Decimal('45.00'), Decimal('23.51'), Decimal('3718.81')),\n",
       " (datetime.date(2023, 2, 7), Decimal('89.10'), Decimal('36.23'), Decimal('3807.91')),\n",
       " (datetime.date(2023, 2, 8), Decimal('0.00'), Decimal('36.23'), Decimal('3807.91')),\n",
       " (datetime.date(2023, 2, 9), Decimal('0.00'), Decimal('36.23'), Decimal('3807.91')),\n",
       " (datetime.date(2023, 2, 10), Decimal('27.67'), Decimal('40.19'), Decimal('3835.58')),\n",
       " (datetime.date(2023, 2, 11), Decimal('49.96'), Decimal('43.81'), Decimal('3885.54')),\n",
       " (datetime.date(2023, 2, 12), Decimal('152.10'), Decimal('51.98'), Decimal('4037.64')),\n",
       " (datetime.date(2023, 2, 13), Decimal('22.70'), Decimal('48.79'), Decimal('4060.34')),\n",
       " (datetime.date(2023, 2, 14), Decimal('109.94'), Decimal('51.77'), Decimal('4170.28')),\n",
       " (datetime.date(2023, 2, 15), Decimal('1534.65'), Decimal('271.00'), Decimal('5704.93')),\n",
       " (datetime.date(2023, 2, 16), Decimal('35.12'), Decimal('276.02'), Decimal('5740.05')),\n",
       " (datetime.date(2023, 2, 17), Decimal('95.64'), Decimal('285.73'), Decimal('5835.69')),\n",
       " (datetime.date(2023, 2, 18), Decimal('91.22'), Decimal('291.62'), Decimal('5926.91')),\n",
       " (datetime.date(2023, 2, 19), Decimal('38.27'), Decimal('275.36'), Decimal('5965.18')),\n",
       " (datetime.date(2023, 2, 20), Decimal('53.73'), Decimal('279.80'), Decimal('6018.91')),\n",
       " (datetime.date(2023, 2, 21), Decimal('74.84'), Decimal('274.78'), Decimal('6093.75')),\n",
       " (datetime.date(2023, 2, 22), Decimal('182.96'), Decimal('81.68'), Decimal('6276.71')),\n",
       " (datetime.date(2023, 2, 23), Decimal('74.32'), Decimal('87.28'), Decimal('6351.03')),\n",
       " (datetime.date(2023, 2, 24), Decimal('62.04'), Decimal('82.48'), Decimal('6413.07')),\n",
       " (datetime.date(2023, 2, 25), Decimal('174.45'), Decimal('94.37'), Decimal('6587.52')),\n",
       " (datetime.date(2023, 2, 26), Decimal('97.41'), Decimal('102.82'), Decimal('6684.93')),\n",
       " (datetime.date(2023, 2, 27), Decimal('118.88'), Decimal('112.13'), Decimal('6803.81')),\n",
       " (datetime.date(2023, 2, 28), Decimal('35.00'), Decimal('106.44'), Decimal('6838.81')),\n",
       " (datetime.date(2023, 3, 1), Decimal('0.00'), Decimal('80.30'), Decimal('6838.81')),\n",
       " (datetime.date(2023, 3, 2), Decimal('0.00'), Decimal('69.68'), Decimal('6838.81')),\n",
       " (datetime.date(2023, 3, 3), Decimal('302.57'), Decimal('104.04'), Decimal('7141.38')),\n",
       " (datetime.date(2023, 3, 4), Decimal('360.31'), Decimal('130.60'), Decimal('7501.69')),\n",
       " (datetime.date(2023, 3, 5), Decimal('126.67'), Decimal('134.78'), Decimal('7628.36')),\n",
       " (datetime.date(2023, 3, 6), Decimal('23.47'), Decimal('121.15'), Decimal('7651.83')),\n",
       " (datetime.date(2023, 3, 7), Decimal('39.02'), Decimal('121.72'), Decimal('7690.85')),\n",
       " (datetime.date(2023, 3, 8), Decimal('0.00'), Decimal('121.72'), Decimal('7690.85')),\n",
       " (datetime.date(2023, 3, 9), Decimal('11.35'), Decimal('123.34'), Decimal('7702.20')),\n",
       " (datetime.date(2023, 3, 10), Decimal('0.00'), Decimal('80.12'), Decimal('7702.20')),\n",
       " (datetime.date(2023, 3, 11), Decimal('60.93'), Decimal('37.35'), Decimal('7763.13')),\n",
       " (datetime.date(2023, 3, 12), Decimal('201.33'), Decimal('48.01'), Decimal('7964.46')),\n",
       " (datetime.date(2023, 3, 13), Decimal('151.75'), Decimal('66.34'), Decimal('8116.21')),\n",
       " (datetime.date(2023, 3, 14), Decimal('0.00'), Decimal('60.77'), Decimal('8116.21')),\n",
       " (datetime.date(2023, 3, 15), Decimal('64.06'), Decimal('69.92'), Decimal('8180.27')),\n",
       " (datetime.date(2023, 3, 16), Decimal('123.77'), Decimal('85.98'), Decimal('8304.04')),\n",
       " (datetime.date(2023, 3, 17), Decimal('587.97'), Decimal('169.97'), Decimal('8892.01')),\n",
       " (datetime.date(2023, 3, 18), Decimal('601.76'), Decimal('247.23'), Decimal('9493.77')),\n",
       " (datetime.date(2023, 3, 19), Decimal('203.03'), Decimal('247.48'), Decimal('9696.80')),\n",
       " (datetime.date(2023, 3, 20), Decimal('158.92'), Decimal('248.50'), Decimal('9855.72')),\n",
       " (datetime.date(2023, 3, 21), Decimal('160.24'), Decimal('271.39'), Decimal('10015.96')),\n",
       " (datetime.date(2023, 3, 22), Decimal('83.01'), Decimal('274.10'), Decimal('10098.97')),\n",
       " (datetime.date(2023, 3, 23), Decimal('167.32'), Decimal('280.32'), Decimal('10266.29')),\n",
       " (datetime.date(2023, 3, 24), Decimal('52.38'), Decimal('203.81'), Decimal('10318.67')),\n",
       " (datetime.date(2023, 3, 25), Decimal('55.50'), Decimal('125.77'), Decimal('10374.17')),\n",
       " (datetime.date(2023, 3, 26), Decimal('59.64'), Decimal('105.29'), Decimal('10433.81')),\n",
       " (datetime.date(2023, 3, 27), Decimal('177.01'), Decimal('107.87'), Decimal('10610.82')),\n",
       " (datetime.date(2023, 3, 28), Decimal('120.08'), Decimal('102.13'), Decimal('10730.90')),\n",
       " (datetime.date(2023, 3, 29), Decimal('167.85'), Decimal('114.25'), Decimal('10898.75')),\n",
       " (datetime.date(2023, 3, 30), Decimal('0.00'), Decimal('90.35'), Decimal('10898.75')),\n",
       " (datetime.date(2023, 3, 31), Decimal('34.28'), Decimal('87.77'), Decimal('10933.03')),\n",
       " (datetime.date(2023, 4, 1), Decimal('21.18'), Decimal('82.86'), Decimal('10954.21')),\n",
       " (datetime.date(2023, 4, 2), Decimal('120.01'), Decimal('91.49'), Decimal('11074.22')),\n",
       " (datetime.date(2023, 4, 3), Decimal('83.99'), Decimal('78.20'), Decimal('11158.21')),\n",
       " (datetime.date(2023, 4, 4), Decimal('4.97'), Decimal('61.75'), Decimal('11163.18')),\n",
       " (datetime.date(2023, 4, 5), Decimal('139.58'), Decimal('57.72'), Decimal('11302.76')),\n",
       " (datetime.date(2023, 4, 6), Decimal('85.40'), Decimal('69.92'), Decimal('11388.16')),\n",
       " (datetime.date(2023, 4, 7), Decimal('125.88'), Decimal('83.00'), Decimal('11514.04')),\n",
       " (datetime.date(2023, 4, 8), Decimal('45.49'), Decimal('86.47'), Decimal('11559.53')),\n",
       " (datetime.date(2023, 4, 9), Decimal('67.37'), Decimal('78.95'), Decimal('11626.90')),\n",
       " (datetime.date(2023, 4, 10), Decimal('135.46'), Decimal('86.31'), Decimal('11762.36')),\n",
       " (datetime.date(2023, 4, 11), Decimal('58.04'), Decimal('93.89'), Decimal('11820.40')),\n",
       " (datetime.date(2023, 4, 12), Decimal('42.61'), Decimal('80.04'), Decimal('11863.01')),\n",
       " (datetime.date(2023, 4, 13), Decimal('172.25'), Decimal('92.44'), Decimal('12035.26')),\n",
       " (datetime.date(2023, 4, 14), Decimal('226.82'), Decimal('106.86'), Decimal('12262.08')),\n",
       " (datetime.date(2023, 4, 15), Decimal('144.49'), Decimal('121.01'), Decimal('12406.57')),\n",
       " (datetime.date(2023, 4, 16), Decimal('23.03'), Decimal('114.67'), Decimal('12429.60')),\n",
       " (datetime.date(2023, 4, 17), Decimal('0.00'), Decimal('95.32'), Decimal('12429.60')),\n",
       " (datetime.date(2023, 4, 18), Decimal('2.25'), Decimal('87.35'), Decimal('12431.85')),\n",
       " (datetime.date(2023, 4, 19), Decimal('103.41'), Decimal('96.04'), Decimal('12535.26')),\n",
       " (datetime.date(2023, 4, 20), Decimal('0.00'), Decimal('71.43'), Decimal('12535.26')),\n",
       " (datetime.date(2023, 4, 21), Decimal('36.14'), Decimal('44.19'), Decimal('12571.40')),\n",
       " (datetime.date(2023, 4, 22), Decimal('0.00'), Decimal('23.55'), Decimal('12571.40')),\n",
       " (datetime.date(2023, 4, 23), Decimal('18.16'), Decimal('22.85'), Decimal('12589.56')),\n",
       " (datetime.date(2023, 4, 24), Decimal('204.30'), Decimal('52.04'), Decimal('12793.86')),\n",
       " (datetime.date(2023, 4, 25), Decimal('0.00'), Decimal('51.72'), Decimal('12793.86')),\n",
       " (datetime.date(2023, 4, 26), Decimal('702.34'), Decimal('137.28'), Decimal('13496.20')),\n",
       " (datetime.date(2023, 4, 27), Decimal('9.28'), Decimal('138.60'), Decimal('13505.48')),\n",
       " (datetime.date(2023, 4, 28), Decimal('31.21'), Decimal('137.90'), Decimal('13536.69')),\n",
       " (datetime.date(2023, 4, 29), Decimal('0.00'), Decimal('137.90'), Decimal('13536.69')),\n",
       " (datetime.date(2023, 4, 30), Decimal('0.00'), Decimal('135.30'), Decimal('13536.69')),\n",
       " (datetime.date(2023, 5, 1), Decimal('33.98'), Decimal('110.97'), Decimal('13570.67')),\n",
       " (datetime.date(2023, 5, 2), Decimal('0.00'), Decimal('110.97'), Decimal('13570.67')),\n",
       " (datetime.date(2023, 5, 3), Decimal('0.00'), Decimal('10.64'), Decimal('13570.67')),\n",
       " (datetime.date(2023, 5, 4), Decimal('22.23'), Decimal('12.49'), Decimal('13592.90')),\n",
       " (datetime.date(2023, 5, 5), Decimal('75.17'), Decimal('18.77'), Decimal('13668.07')),\n",
       " (datetime.date(2023, 5, 6), Decimal('57.34'), Decimal('26.96'), Decimal('13725.41')),\n",
       " (datetime.date(2023, 5, 7), Decimal('26.47'), Decimal('30.74'), Decimal('13751.88')),\n",
       " (datetime.date(2023, 5, 8), Decimal('9.28'), Decimal('27.21'), Decimal('13761.16')),\n",
       " (datetime.date(2023, 5, 9), Decimal('0.00'), Decimal('27.21'), Decimal('13761.16')),\n",
       " (datetime.date(2023, 5, 10), Decimal('2.27'), Decimal('27.54'), Decimal('13763.43')),\n",
       " (datetime.date(2023, 5, 11), Decimal('8.60'), Decimal('25.59'), Decimal('13772.03')),\n",
       " (datetime.date(2023, 5, 12), Decimal('19.28'), Decimal('17.61'), Decimal('13791.31')),\n",
       " (datetime.date(2023, 5, 13), Decimal('0.00'), Decimal('9.41'), Decimal('13791.31')),\n",
       " (datetime.date(2023, 5, 14), Decimal('0.00'), Decimal('5.63'), Decimal('13791.31')),\n",
       " (datetime.date(2023, 5, 15), Decimal('108.44'), Decimal('19.80'), Decimal('13899.75')),\n",
       " (datetime.date(2023, 5, 16), Decimal('160.32'), Decimal('42.70'), Decimal('14060.07')),\n",
       " (datetime.date(2023, 5, 17), Decimal('10.90'), Decimal('43.93'), Decimal('14070.97')),\n",
       " (datetime.date(2023, 5, 18), Decimal('132.45'), Decimal('61.63'), Decimal('14203.42')),\n",
       " (datetime.date(2023, 5, 19), Decimal('77.66'), Decimal('69.97'), Decimal('14281.08')),\n",
       " (datetime.date(2023, 5, 20), Decimal('101.04'), Decimal('84.40'), Decimal('14382.12')),\n",
       " (datetime.date(2023, 5, 21), Decimal('73.25'), Decimal('94.87'), Decimal('14455.37')),\n",
       " (datetime.date(2023, 5, 22), Decimal('217.38'), Decimal('110.43'), Decimal('14672.75')),\n",
       " (datetime.date(2023, 5, 23), Decimal('68.30'), Decimal('97.28'), Decimal('14741.05')),\n",
       " (datetime.date(2023, 5, 24), Decimal('59.59'), Decimal('104.24'), Decimal('14800.64')),\n",
       " (datetime.date(2023, 5, 25), Decimal('354.75'), Decimal('136.00'), Decimal('15155.39')),\n",
       " (datetime.date(2023, 5, 26), Decimal('168.73'), Decimal('149.01'), Decimal('15324.12')),\n",
       " (datetime.date(2023, 5, 27), Decimal('302.09'), Decimal('177.73'), Decimal('15626.21')),\n",
       " (datetime.date(2023, 5, 28), Decimal('37.26'), Decimal('172.59'), Decimal('15663.47')),\n",
       " (datetime.date(2023, 5, 29), Decimal('239.77'), Decimal('175.78'), Decimal('15903.24')),\n",
       " (datetime.date(2023, 5, 30), Decimal('0.00'), Decimal('166.03'), Decimal('15903.24')),\n",
       " (datetime.date(2023, 5, 31), Decimal('0.00'), Decimal('157.51'), Decimal('15903.24')),\n",
       " (datetime.date(2023, 6, 1), Decimal('0.00'), Decimal('106.84'), Decimal('15903.24')),\n",
       " (datetime.date(2023, 6, 2), Decimal('0.00'), Decimal('82.73'), Decimal('15903.24')),\n",
       " (datetime.date(2023, 6, 3), Decimal('37.59'), Decimal('44.95'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 4), Decimal('0.00'), Decimal('39.62'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 5), Decimal('0.00'), Decimal('5.37'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 6), Decimal('0.00'), Decimal('5.37'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 7), Decimal('0.00'), Decimal('5.37'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 8), Decimal('0.00'), Decimal('5.37'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 9), Decimal('0.00'), Decimal('5.37'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 10), Decimal('0.00'), Decimal('0.00'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 11), Decimal('0.00'), Decimal('0.00'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 12), Decimal('0.00'), Decimal('0.00'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 13), Decimal('0.00'), Decimal('0.00'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 14), Decimal('0.00'), Decimal('0.00'), Decimal('15940.83')),\n",
       " (datetime.date(2023, 6, 15), Decimal('199.60'), Decimal('28.51'), Decimal('16140.43')),\n",
       " (datetime.date(2023, 6, 16), Decimal('201.03'), Decimal('57.23'), Decimal('16341.46')),\n",
       " (datetime.date(2023, 6, 17), Decimal('147.94'), Decimal('78.37'), Decimal('16489.40')),\n",
       " (datetime.date(2023, 6, 18), Decimal('171.39'), Decimal('102.85'), Decimal('16660.79')),\n",
       " (datetime.date(2023, 6, 19), Decimal('191.63'), Decimal('130.23'), Decimal('16852.42')),\n",
       " (datetime.date(2023, 6, 20), Decimal('139.90'), Decimal('150.21'), Decimal('16992.32')),\n",
       " (datetime.date(2023, 6, 21), Decimal('4.99'), Decimal('150.93'), Decimal('16997.31')),\n",
       " (datetime.date(2023, 6, 22), Decimal('116.63'), Decimal('139.07'), Decimal('17113.94')),\n",
       " (datetime.date(2023, 6, 23), Decimal('311.73'), Decimal('154.89'), Decimal('17425.67')),\n",
       " (datetime.date(2023, 6, 24), Decimal('0.00'), Decimal('133.75'), Decimal('17425.67')),\n",
       " (datetime.date(2023, 6, 25), Decimal('0.00'), Decimal('109.27'), Decimal('17425.67')),\n",
       " (datetime.date(2023, 6, 26), Decimal('0.00'), Decimal('81.89'), Decimal('17425.67'))]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Output\n",
    "SELECT *\n",
    "FROM daily_spend"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b9a8941-99db-44da-8fb4-32e24f808fc4",
   "metadata": {},
   "source": [
    "### daily_category_balance\n",
    "\n",
    "Calculates total spent for each category per day. This view was created and imported into Power BI to run a specific visual. It is not intended for any other analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "7fcb9daf-8f96-45af-8202-5051efd21ddd",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE VIEW daily_category_balance AS\n",
    "-- CTE to generate all possible days and categories\n",
    "WITH date_category AS (\n",
    "    SELECT \n",
    "        d.short_date,\n",
    "        c.category_id\n",
    "    FROM date AS d\n",
    "    CROSS JOIN (\n",
    "        SELECT category_id\n",
    "        FROM category\n",
    "    ) AS c\n",
    "    WHERE d.year != '2022'\n",
    "        AND d.short_date <= CURDATE()\n",
    "    ORDER BY short_date, category_id\n",
    ")\n",
    "SELECT \n",
    "\tdc.short_date,\n",
    "    dc.category_id,\n",
    "    -- Categorical spending each day\n",
    "    COALESCE(SUM(ABS(tf.transaction_amount)), 0) AS day_sum\n",
    "-- Select from cte and left join other tables to keep all dates and categories from cte\n",
    "FROM date_category AS dc\n",
    "LEFT JOIN transaction_facts AS tf\n",
    "    ON dc.short_date = tf.short_date\n",
    "    AND dc.category_id = tf.category_id\n",
    "LEFT JOIN transaction_type AS t\n",
    "    ON tf.transaction_type_id = t.transaction_type_id\n",
    "WHERE EXTRACT(year FROM dc.short_date) != '2022'\n",
    "    AND dc.short_date <= CURDATE()\n",
    "    AND (t.transaction_type_id IN (1, 2) OR t.transaction_type_id IS NULL)\n",
    "GROUP BY dc.short_date, dc.category_id\n",
    "ORDER BY dc.short_date, dc.category_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "2ff9d56c-6396-471a-8f22-8d5bdf2980a6",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/spend_save\n",
      "150 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>short_date</th>\n",
       "            <th>category_id</th>\n",
       "            <th>day_sum</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>6</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>10</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-01</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>6</td>\n",
       "            <td>10.78</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>10</td>\n",
       "            <td>16.07</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-02</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>6</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>7</td>\n",
       "            <td>54.73</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>10</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-03</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>6</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>10</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-04</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>6</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>10</td>\n",
       "            <td>42.97</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>16</td>\n",
       "            <td>56.73</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-05</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>6</td>\n",
       "            <td>19.41</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>10</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-06</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>6</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>10</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-07</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>6</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>10</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-08</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>6</td>\n",
       "            <td>18.95</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>9</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>10</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-09</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>1</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>2</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>3</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>4</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>6</td>\n",
       "            <td>11.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>7</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>8</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>9</td>\n",
       "            <td>36.32</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>10</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>11</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>12</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>14</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>15</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>16</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2023-01-10</td>\n",
       "            <td>17</td>\n",
       "            <td>0.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2023, 1, 1), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 6, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 10, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 1), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 6, Decimal('10.78')),\n",
       " (datetime.date(2023, 1, 2), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 10, Decimal('16.07')),\n",
       " (datetime.date(2023, 1, 2), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 2), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 6, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 7, Decimal('54.73')),\n",
       " (datetime.date(2023, 1, 3), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 10, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 3), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 6, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 10, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 4), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 6, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 10, Decimal('42.97')),\n",
       " (datetime.date(2023, 1, 5), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 5), 16, Decimal('56.73')),\n",
       " (datetime.date(2023, 1, 5), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 6, Decimal('19.41')),\n",
       " (datetime.date(2023, 1, 6), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 10, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 6), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 6, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 10, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 7), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 6, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 10, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 8), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 6, Decimal('18.95')),\n",
       " (datetime.date(2023, 1, 9), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 9, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 10, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 9), 17, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 1, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 2, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 3, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 4, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 6, Decimal('11.99')),\n",
       " (datetime.date(2023, 1, 10), 7, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 8, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 9, Decimal('36.32')),\n",
       " (datetime.date(2023, 1, 10), 10, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 11, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 12, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 14, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 15, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 16, Decimal('0.00')),\n",
       " (datetime.date(2023, 1, 10), 17, Decimal('0.00'))]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Output\n",
    "SELECT *\n",
    "FROM daily_category_balance\n",
    "LIMIT 150        -- Limit output due to size of view table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14fb133d-8d80-447b-9304-4de85ae0b23a",
   "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.9"
  },
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}