bqnt_iql_magics.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The iql.jupyter_magics.iql_magic extension is already loaded. To reload it, use:\n",
      "  %reload_ext iql.jupyter_magics.iql_magic\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "%load_ext iql.jupyter_magics.iql_magic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Cell and Line Magics\n",
    "\n",
    "You can use %iql for a one-line command, or %%iql at the beginning of a cell for a long query.\n",
    "\n",
    "This is the same as calling: `iql.execute(...)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   range\n",
       "0      0\n",
       "1      1\n",
       "2      2\n",
       "3      3\n",
       "4      4\n",
       "5      5\n",
       "6      6\n",
       "7      7\n",
       "8      8\n",
       "9      9"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# %iql is called a \"line magic\" in Jupyter. %%iql is a cell magic\n",
    "\n",
    "df = %iql select * from range(10)\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Multiple Statements Separated by Semicolons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   range\n",
       "0      6\n",
       "1      7\n",
       "2      8\n",
       "3      9"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%iql\n",
    "\n",
    "create table abc as select * from range(10);\n",
    "\n",
    "select * from abc where range > 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### BQL can be executed on the fly\n",
    "\n",
    "Use -o to store the output in a variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>DATE</th>\n",
       "      <th>name</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-29</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.080166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-30</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.047133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-31</td>\n",
       "      <td>px_last</td>\n",
       "      <td>2.618075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.299787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-02</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.463090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-03</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.664822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-04</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.589516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-05</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.096633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-06</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.778978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-07</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.911424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-08</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.871942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-09</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.101196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-10</td>\n",
       "      <td>px_last</td>\n",
       "      <td>1.012025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-11</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.740720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-12</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.761841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-13</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.922619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-14</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.843994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-15</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.553850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-16</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.121285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-17</td>\n",
       "      <td>px_last</td>\n",
       "      <td>0.345041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-18</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.515255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-19</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.618648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-20</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.579350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-21</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.196254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.375983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-23</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.924309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-24</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.641288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-25</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.784876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-26</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.505375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-27</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.998839</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ID       DATE     name     value\n",
       "0   IBM US Equity 2023-05-29  px_last  6.080166\n",
       "1   IBM US Equity 2023-05-30  px_last  7.047133\n",
       "2   IBM US Equity 2023-05-31  px_last  2.618075\n",
       "3   IBM US Equity 2023-06-01  px_last  3.299787\n",
       "4   IBM US Equity 2023-06-02  px_last  9.463090\n",
       "5   IBM US Equity 2023-06-03  px_last  3.664822\n",
       "6   IBM US Equity 2023-06-04  px_last  6.589516\n",
       "7   IBM US Equity 2023-06-05  px_last  7.096633\n",
       "8   IBM US Equity 2023-06-06  px_last  9.778978\n",
       "9   IBM US Equity 2023-06-07  px_last  9.911424\n",
       "10  IBM US Equity 2023-06-08  px_last  7.871942\n",
       "11  IBM US Equity 2023-06-09  px_last  4.101196\n",
       "12  IBM US Equity 2023-06-10  px_last  1.012025\n",
       "13  IBM US Equity 2023-06-11  px_last  9.740720\n",
       "14  IBM US Equity 2023-06-12  px_last  8.761841\n",
       "15  IBM US Equity 2023-06-13  px_last  6.922619\n",
       "16  IBM US Equity 2023-06-14  px_last  8.843994\n",
       "17  IBM US Equity 2023-06-15  px_last  3.553850\n",
       "18  IBM US Equity 2023-06-16  px_last  6.121285\n",
       "19  IBM US Equity 2023-06-17  px_last  0.345041\n",
       "20  IBM US Equity 2023-06-18  px_last  4.515255\n",
       "21  IBM US Equity 2023-06-19  px_last  4.618648\n",
       "22  IBM US Equity 2023-06-20  px_last  4.579350\n",
       "23  IBM US Equity 2023-06-21  px_last  6.196254\n",
       "24  IBM US Equity 2023-06-22  px_last  6.375983\n",
       "25  IBM US Equity 2023-06-23  px_last  8.924309\n",
       "26  IBM US Equity 2023-06-24  px_last  8.641288\n",
       "27  IBM US Equity 2023-06-25  px_last  3.784876\n",
       "28  IBM US Equity 2023-06-26  px_last  9.505375\n",
       "29  IBM US Equity 2023-06-27  px_last  9.998839"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%iql -o my_bql_df\n",
    "\n",
    "get(\n",
    "      px_last\n",
    "    ) for(\n",
    "      ['IBM US Equity']\n",
    "    ) with(\n",
    "      dates=range(-29d, 0d),\n",
    "      fill=prev,\n",
    "      currency=USD\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### %%capture to Suppress Display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture \n",
    "%%iql -o my_bql_df\n",
    "\n",
    "get(\n",
    "      px_last\n",
    "    ) for(\n",
    "      ['IBM US Equity']\n",
    "    ) with(\n",
    "      dates=range(-29d, 0d),\n",
    "      fill=prev,\n",
    "      currency=USD\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Now for interesting stuff: SQL + BQL\n",
    "\n",
    "Nothing too fancy, just ordering the results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>DATE</th>\n",
       "      <th>name</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-29</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.080166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-30</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.047133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-31</td>\n",
       "      <td>px_last</td>\n",
       "      <td>2.618075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.299787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-02</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.463090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-03</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.664822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-04</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.589516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-05</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.096633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-06</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.778978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-07</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.911424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-08</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.871942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-09</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.101196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-10</td>\n",
       "      <td>px_last</td>\n",
       "      <td>1.012025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-11</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.740720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-12</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.761841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-13</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.922619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-14</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.843994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-15</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.553850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-16</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.121285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-17</td>\n",
       "      <td>px_last</td>\n",
       "      <td>0.345041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-18</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.515255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-19</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.618648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-20</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.579350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-21</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.196254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.375983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-23</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.924309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-24</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.641288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-25</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.784876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-26</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.505375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-27</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.998839</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ID       DATE     name     value\n",
       "0   IBM US Equity 2023-05-29  px_last  6.080166\n",
       "1   IBM US Equity 2023-05-30  px_last  7.047133\n",
       "2   IBM US Equity 2023-05-31  px_last  2.618075\n",
       "3   IBM US Equity 2023-06-01  px_last  3.299787\n",
       "4   IBM US Equity 2023-06-02  px_last  9.463090\n",
       "5   IBM US Equity 2023-06-03  px_last  3.664822\n",
       "6   IBM US Equity 2023-06-04  px_last  6.589516\n",
       "7   IBM US Equity 2023-06-05  px_last  7.096633\n",
       "8   IBM US Equity 2023-06-06  px_last  9.778978\n",
       "9   IBM US Equity 2023-06-07  px_last  9.911424\n",
       "10  IBM US Equity 2023-06-08  px_last  7.871942\n",
       "11  IBM US Equity 2023-06-09  px_last  4.101196\n",
       "12  IBM US Equity 2023-06-10  px_last  1.012025\n",
       "13  IBM US Equity 2023-06-11  px_last  9.740720\n",
       "14  IBM US Equity 2023-06-12  px_last  8.761841\n",
       "15  IBM US Equity 2023-06-13  px_last  6.922619\n",
       "16  IBM US Equity 2023-06-14  px_last  8.843994\n",
       "17  IBM US Equity 2023-06-15  px_last  3.553850\n",
       "18  IBM US Equity 2023-06-16  px_last  6.121285\n",
       "19  IBM US Equity 2023-06-17  px_last  0.345041\n",
       "20  IBM US Equity 2023-06-18  px_last  4.515255\n",
       "21  IBM US Equity 2023-06-19  px_last  4.618648\n",
       "22  IBM US Equity 2023-06-20  px_last  4.579350\n",
       "23  IBM US Equity 2023-06-21  px_last  6.196254\n",
       "24  IBM US Equity 2023-06-22  px_last  6.375983\n",
       "25  IBM US Equity 2023-06-23  px_last  8.924309\n",
       "26  IBM US Equity 2023-06-24  px_last  8.641288\n",
       "27  IBM US Equity 2023-06-25  px_last  3.784876\n",
       "28  IBM US Equity 2023-06-26  px_last  9.505375\n",
       "29  IBM US Equity 2023-06-27  px_last  9.998839"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%iql -o my_bql_df\n",
    "\n",
    "get(\n",
    "      px_last\n",
    "    ) for(\n",
    "      ['IBM US Equity']\n",
    "    ) with(\n",
    "      dates=range(-29d, 0d),\n",
    "      fill=prev,\n",
    "      currency=USD\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivoting multiple fields for a time series result\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The iql.jupyter_magics.iql_magic extension is already loaded. To reload it, use:\n",
      "  %reload_ext iql.jupyter_magics.iql_magic\n"
     ]
    }
   ],
   "source": [
    "%load_ext iql.jupyter_magics.iql_magic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Cell and Line Magics\n",
    "\n",
    "You can use %iql for a one-line command, or %%iql at the beginning of a cell for a long query.\n",
    "\n",
    "This is the same as calling: `iql.execute(...)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   range\n",
       "0      0\n",
       "1      1\n",
       "2      2\n",
       "3      3\n",
       "4      4\n",
       "5      5\n",
       "6      6\n",
       "7      7\n",
       "8      8\n",
       "9      9"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# %iql is called a \"line magic\" in Jupyter. %%iql is a cell magic\n",
    "\n",
    "df = %iql select * from range(10)\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Multiple Statements Separated by Semicolons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   range\n",
       "0      6\n",
       "1      7\n",
       "2      8\n",
       "3      9"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%iql\n",
    "\n",
    "create table abc as select * from range(10);\n",
    "\n",
    "select * from abc where range > 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### BQL can be executed on the fly\n",
    "\n",
    "Use -o to store the output in a variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>DATE</th>\n",
       "      <th>name</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-29</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.080166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-30</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.047133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-05-31</td>\n",
       "      <td>px_last</td>\n",
       "      <td>2.618075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.299787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-02</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.463090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-03</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.664822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-04</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.589516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-05</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.096633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-06</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.778978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-07</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.911424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-08</td>\n",
       "      <td>px_last</td>\n",
       "      <td>7.871942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-09</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.101196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-10</td>\n",
       "      <td>px_last</td>\n",
       "      <td>1.012025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-11</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.740720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-12</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.761841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-13</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.922619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-14</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.843994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-15</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.553850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-16</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.121285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-17</td>\n",
       "      <td>px_last</td>\n",
       "      <td>0.345041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-18</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.515255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-19</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.618648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-20</td>\n",
       "      <td>px_last</td>\n",
       "      <td>4.579350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-21</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.196254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>px_last</td>\n",
       "      <td>6.375983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-23</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.924309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-24</td>\n",
       "      <td>px_last</td>\n",
       "      <td>8.641288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-25</td>\n",
       "      <td>px_last</td>\n",
       "      <td>3.784876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-26</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.505375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>2023-06-27</td>\n",
       "      <td>px_last</td>\n",
       "      <td>9.998839</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ID       DATE     name     value\n",
       "0   IBM US Equity 2023-05-29  px_last  6.080166\n",
       "1   IBM US Equity 2023-05-30  px_last  7.047133\n",
       "2   IBM US Equity 2023-05-31  px_last  2.618075\n",
       "3   IBM US Equity 2023-06-01  px_last  3.299787\n",
       "4   IBM US Equity 2023-06-02  px_last  9.463090\n",
       "5   IBM US Equity 2023-06-03  px_last  3.664822\n",
       "6   IBM US Equity 2023-06-04  px_last  6.589516\n",
       "7   IBM US Equity 2023-06-05  px_last  7.096633\n",
       "8   IBM US Equity 2023-06-06  px_last  9.778978\n",
       "9   IBM US Equity 2023-06-07  px_last  9.911424\n",
       "10  IBM US Equity 2023-06-08  px_last  7.871942\n",
       "11  IBM US Equity 2023-06-09  px_last  4.101196\n",
       "12  IBM US Equity 2023-06-10  px_last  1.012025\n",
       "13  IBM US Equity 2023-06-11  px_last  9.740720\n",
       "14  IBM US Equity 2023-06-12  px_last  8.761841\n",
       "15  IBM US Equity 2023-06-13  px_last  6.922619\n",
       "16  IBM US Equity 2023-06-14  px_last  8.843994\n",
       "17  IBM US Equity 2023-06-15  px_last  3.553850\n",
       "18  IBM US Equity 2023-06-16  px_last  6.121285\n",
       "19  IBM US Equity 2023-06-17  px_last  0.345041\n",
       "20  IBM US Equity 2023-06-18  px_last  4.515255\n",
       "21  IBM US Equity 2023-06-19  px_last  4.618648\n",
       "22  IBM US Equity 2023-06-20  px_last  4.579350\n",
       "23  IBM US Equity 2023-06-21  px_last  6.196254\n",
       "24  IBM US Equity 2023-06-22  px_last  6.375983\n",
       "25  IBM US Equity 2023-06-23  px_last  8.924309\n",
       "26  IBM US Equity 2023-06-24  px_last  8.641288\n",
       "27  IBM US Equity 2023-06-25  px_last  3.784876\n",
       "28  IBM US Equity 2023-06-26  px_last  9.505375\n",
       "29  IBM US Equity 2023-06-27  px_last  9.998839"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%iql -o my_bql_df\n",
    "\n",
    "get(\n",
    "      px_last\n",
    "    ) for(\n",
    "      ['IBM US Equity']\n",
    "    ) with(\n",
    "      dates=range(-29d, 0d),\n",
    "      fill=prev,\n",
    "      currency=USD\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivot Multi-Field Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>ID</th>\n",
       "      <th>px_last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-27</td>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>9.998839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-06-26</td>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>9.505375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-06-25</td>\n",
       "      <td>IBM US Equity</td>\n",
       "      <td>3.784876</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        DATE             ID   px_last\n",
       "0 2023-06-27  IBM US Equity  9.998839\n",
       "1 2023-06-26  IBM US Equity  9.505375\n",
       "2 2023-06-25  IBM US Equity  3.784876"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%iql -o my_bql_df\n",
    "\n",
    "select * from bql(\"\n",
    "get(\n",
    "      px_last\n",
    "    ) for(\n",
    "      ['IBM US Equity']\n",
    "    ) with(\n",
    "      dates=range(-29d, 0d),\n",
    "      fill=prev,\n",
    "      currency=USD\n",
    "    )\n",
    "\", pivot=((date, id), name, value))\n",
    "order by date desc\n",
    "limit 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Or, pivot to one column per security"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>IBM_US_Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-05-29</td>\n",
       "      <td>6.080166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-05-30</td>\n",
       "      <td>7.047133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-05-31</td>\n",
       "      <td>2.618075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        DATE  IBM_US_Equity\n",
       "0 2023-05-29       6.080166\n",
       "1 2023-05-30       7.047133\n",
       "2 2023-05-31       2.618075"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%iql -o my_bql_df\n",
    "\n",
    "--- pivot=(rows, columns, value field)\n",
    "select * from bql(\"\n",
    "get(\n",
    "      px_last\n",
    "    ) for(\n",
    "      ['IBM US Equity']\n",
    "    ) with(\n",
    "      dates=range(-29d, 0d),\n",
    "      fill=prev,\n",
    "      currency=USD\n",
    "    )\n",
    "\", pivot=(date, id, value))\n",
    "limit 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The remaining cells must be run in Bquant\n"
     ]
    },
    {
     "ename": "AssertionError",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAssertionError\u001b[0m                            Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[39], line 3\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mBQUANT_USERNAME\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;129;01min\u001b[39;00m os\u001b[38;5;241m.\u001b[39menviron:\n\u001b[0;32m      2\u001b[0m     \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mThe remaining cells must be run in Bquant\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m----> 3\u001b[0m     \u001b[38;5;28;01massert\u001b[39;00m \u001b[38;5;28;01mFalse\u001b[39;00m\n",
      "\u001b[1;31mAssertionError\u001b[0m: "
     ]
    }
   ],
   "source": [
    "if \"BQUANT_USERNAME\" not in os.environ:\n",
    "    print(\"The remaining cells must be run in Bquant\")\n",
    "    assert False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Other Examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%iql\n",
    "\n",
    "---- Ranking\n",
    "\n",
    "SELECT *, rank() over (partition by month(date) order by px_high) ranking\n",
    "    FROM bql(\"\n",
    "                get (px_open, px_high, px_low, px_last, px_volume/1M as #px_volume)\n",
    "                for ('IBM US Equity')\n",
    "                with (dates=range(-1Y, 0D), fill=prev)\n",
    "            \", pivot=(auto)) as data\n",
    "    ORDER BY date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%iql\n",
    "\n",
    "---- Skew and Kurtosis\n",
    "\n",
    "SELECT id,\n",
    "    avg(px_last) avg_px_last,\n",
    "    kurtosis(px_last) kurt_px_last,\n",
    "    count(*) num_observations\n",
    "    FROM bql(\"\n",
    "            get(px_last(dates=range(-10D, 0D), fill=prev) as #px_last)\n",
    "            for (['TSLA US Equity','IBM US Equity'])\n",
    "            \", pivot=auto)\n",
    "    GROUP BY id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%iql \n",
    "\n",
    "---- Price change vs peers\n",
    "\n",
    "SELECT c.id, c.country_iso, c.industry_subgroup, c.cur_mkt_cap, c.px_change, p.id, p.country_iso, p.industry_subgroup, p.cur_mkt_cap, p.px_change, c.px_change - p.px_change px_change_delta\n",
    "    FROM\n",
    "    (SELECT *, (px_last - px_last_1y) / px_last_1y as px_change\n",
    "        -- px_change could also be calculated in BQL via pct_diff()\n",
    "        FROM\n",
    "            bql(\"\n",
    "                get(name, COUNTRY_ISO, industry_subgroup, cur_mkt_cap/1M as #cur_mkt_cap, px_last, px_last(dates=-1Y) as #px_last_1y) for (peers('IBM US Equity')) with (currency=USD)\n",
    "            \", pivot=(id,name), dropna=True)\n",
    "        ) as p\n",
    "    JOIN\n",
    "        (SELECT *, (px_last - px_last_1y) / px_last_1y as px_change\n",
    "        FROM\n",
    "            bql(\"\n",
    "                get(name, COUNTRY_ISO, industry_subgroup, cur_mkt_cap/1M as #cur_mkt_cap, px_last, px_last(dates=-1Y) as #px_last_1y) for ('IBM US Equity') with (currency=USD)\n",
    "            \", pivot=(id,name))\n",
    "    ) as c\n",
    "    -- c always returns one row\n",
    "    ON true ORDER BY px_change_delta desc "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%iql\n",
    "\n",
    "---- With CTES\n",
    "\n",
    "WITH c AS (select * from bql(\"get(px_last) for('IBM US Equity') with(fill=prev, currency=USD)\")),\n",
    "    idx AS (select * from bql(\"get(px_last) for ('SPX Index') with(fill=prev, currency=USD)\"))\n",
    "    \n",
    "    SELECT c.id c, c.value as c_px, idx.id idx, idx.value as idx_px FROM c, idx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%iql\n",
    "\n",
    "--- You can create tables in the in-memory database\n",
    "--- These are deleted. You can also initialize the database with on-disk storage\n",
    "\n",
    "CREATE TABLE temp_table_1 as \n",
    "(SELECT id from bql(\"get(px_volume) for(translatesymbols(members('SPX Index'), targetidtype=FUNDAMENTALTICKER)) with(currency=USD)\", pivot=(id, name)) \n",
    "        ORDER BY px_volume desc limit 10) \n",
    "    ;\n",
    "SELECT id, name, px_last, px_volume/1000000 as px_volume FROM bql('get(name, px_last, px_volume) for(\"$SECURITY\")', paramquery=('$SECURITY', 'select * from temp_table_1 limit 2'), pivot=(id, name))\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}