QueryingSalesDataIbis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7f15bb58-3f22-4dfc-a837-f459771b1cb5",
   "metadata": {},
   "source": [
    "# Querying Sales Data Ibis\n",
    "## Introduction\n",
    "In this notebook, we will use Ibis expressions to preview a mock dataset and then build a query that will help analysts get all of the information they need for exploratory analysis, standardized measurement studies, and data transfer.\n",
    "\n",
    "This is a companion notebook to QueryingSalesDatafStringSQL.ipynb and will outline the advantages of using Ibis over formatted SQL Strings.  Most of the steps are exactly the same, but there is some discussion about the difference between the two methods.\n",
    "\n",
    "## Setup\n",
    "\n",
    "We have a basic star schema containing (mock) sales data for several retailers.  The tables are as follows:\n",
    "\n",
    "* `fact_sale`: `fact_id` | `date_id` | `store_id` | `product_id` | `unit_id` | `value` | `created_at`\n",
    "    * Sales data in aggregate by some time period (in this case, weeks.  See `dim_date`)\n",
    "* `dim_date`: `date_id` | `date_value`\n",
    "    * date dimensions (ID to value).  Just contains week values.\n",
    "    * (join fact by `date_id`)\n",
    "* `dim_store`: `store_id` | `store_name` | `retailer_id`\n",
    "    * store dimensions (ID to names)\n",
    "    * (join fact by `store_id`)\n",
    "* `dim_retailer`: `retailer_id` | `retailer_name`\n",
    "    * retailer dimensions (ID to names)\n",
    "    * (join stores by `retailer_id`)\n",
    "* `dim_product`: `product_id` | `product_name`\n",
    "    * product dimensions (ID to names)\n",
    "    * (join fact by `product_id`)\n",
    "* `dim_unit`: `unit_id` | `unit_name`\n",
    "    * unit dimensions (ID to names).  For now, just contains Units and Sales USD\n",
    "    * (join fact by `unit_id`)\n",
    "\n",
    "Our analysts will use this data to run some rudimentary analyses for some product set at some retailer.  A typical analysis will have the following parameters:\n",
    "\n",
    "* dates:\n",
    "    * A test period that we want to measure.  Typically 4 weeks with a 2 period lag\n",
    "    * A control period that we will use to establish a baseline wrt time\n",
    "* products:\n",
    "    * An arbitrary binning of products\n",
    "* store segments:\n",
    "    * Test: treatment applied\n",
    "    * Control: treatment not applied, to help establish a baseline wrt stores\n",
    "    * Not Selected: n/a\n",
    "* units\n",
    "    * Typically Sales in Currency (USD in this example)\n",
    "\n",
    "Store segmentations created in our measurement studies are held in another star schema by an analysis ID, which connects to our sales schema using `store_id`:\n",
    "\n",
    "* `fact_segmentation`: `fact_id` | `analysis_id` | `segment_id` | `store_id`\n",
    "    * (join `dim_store` by `store_id`)\n",
    "* `dim_segment`: `segment_id` | `segment_name`\n",
    "    * (join fact by `segment_id`)\n",
    "\n",
    "In the event a store segmentation doesn't exist, we can simply create one segment for all interesting stores (some subset of the universe) and then upload it to `fact_segmentation`.\n",
    "\n",
    "## Things to Keep in Mind\n",
    "\n",
    "At some point, an analyst or downstream vendor might want to actually look at the data and map values to real world objects, so it will help to have as much of the data human-readable as possible.  Therefore, we want to include an acceptable amount of detail so internal users can limit our `SELECT * FROM TABLE WHERE TABLE_ID = VALUE` calls and external users can limit their prying emails.\n",
    "\n",
    "Also, it might be tempting to filter only on those products that are called for, but for exploratory reasons we should at least aggregate them into additional buckets.  We will create three additional groups for all products:\n",
    "\n",
    "1. `included_products`\n",
    "2. `excluded_products`\n",
    "3. `all_products`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "497072af-0f39-41d1-a003-2b09f86a6ba5",
   "metadata": {},
   "source": [
    "# Sales Data Query\n",
    "\n",
    "In this section, we will build a Sales Data Query using the mock sales data referenced above.  To generate a mock dataset and follow along, you can use the MockSalesData notebook provided in this repo.\n",
    "\n",
    "The columns we need are chosen to help an analyst comb through the data quickly in the event of an anomaly or for exploratory analyses:\n",
    "\n",
    "1. `analysis_id`: to keep track of what analysis ID we're using\n",
    "1. `[ date_id | store_id | unit_id ]`: a set of ID references that act as a unique label for our product groups at a given store, during a given week, for a given unit\n",
    "1. `[ retailer_name | store_name ]`: human-readable store label\n",
    "1. `week_value`: human-readable date label\n",
    "1. `unit_name`: human-readable unit label\n",
    "1. product group names: we will group our products by product group label and then pivot our data so it takes up fewer rows (also so that a human can keep better track of the labels in (2))\n",
    "\n",
    "We will begin by exploring the tables in our database and then construct our query to accept arbitrary analysis parameters:\n",
    "\n",
    "* `ANALYSIS_ID`: filtering store segments and to label the analysis\n",
    "* `PRODUCT GROUPS`: a mapping of product group names (group labels) to product names (`product_name`)\n",
    "    * These are assumed to be mutually exclusive for the purposes of addition\n",
    "* dates:\n",
    "    * `TEST_PERIOD_START`: week of treatment start\n",
    "    * `TEST_PERIOD_END`: week of treatment end\n",
    "    * `LAG_PERIOD_END`: week of the lag period end\n",
    "    * `CONTROL_PERIOD_START`: week of control start\n",
    "    * `CONTROL_PERIOD_END`: week of control end"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7088106-470a-46b3-a1c4-edc85025759b",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b198459-d6ae-4199-a352-f3bdcbd48c8d",
   "metadata": {},
   "source": [
    "Analysis vars for filtering and transforming.  Normally these would be kept in a set of tables on a database somewhere, but for now let's just make them variables we can play around with in-notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "21d10d78-1dc1-4a15-a803-0da667a5dcec",
   "metadata": {},
   "outputs": [],
   "source": [
    "ANALYSIS_ID = 3\n",
    "\n",
    "# In a more sophisticated data warehouse, we would store analysis paramters as a configuration\n",
    "# inside of our database and connect this configuration to our data using the configuration id\n",
    "PRODUCT_GROUPS = {\n",
    "    'group_1': ['PRODUCT_0', 'PRODUCT_4']\n",
    "    ,'group_2': ['PRODUCT_1']\n",
    "    ,'group_3': ['PRODUCT_3', 'PRODUCT_5', 'PRODUCT_9']\n",
    "}\n",
    "\n",
    "TEST_PERIOD_START = 'WEEK_52'\n",
    "TEST_PERIOD_END = 'WEEK_55'\n",
    "\n",
    "LAG_PERIOD_END = 'WEEK_57'\n",
    "\n",
    "CONTROL_PERIOD_START = 'WEEK_00'\n",
    "CONTROL_PERIOD_END = 'WEEK_51'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44b7544f-9210-431b-8de7-68e962ed49fb",
   "metadata": {},
   "source": [
    "Misc vars for pointing to stuff:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "435d4767-38bc-4ae2-8469-d625375bba70",
   "metadata": {},
   "outputs": [],
   "source": [
    "SALES_DB = 'sales_data.db'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4767c06-3ac0-4ebd-ab71-dcd8b52d3d01",
   "metadata": {},
   "source": [
    "To get our data using ibis, we will use the sqlite backend connector.\n",
    "\n",
    "The default limit on `.execute()` is 10,000 rows.  We will instead set this to 20,000,000 (please change this value depending on how hard you are willing to make your kernel work)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c315e18f-ef43-4928-9ab8-bd84d2893cab",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "\n",
    "iconn = ibis.sqlite.connect(SALES_DB)\n",
    "\n",
    "ibis.options.sql.default_limit = 2e7"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab249a33-b1f1-4537-b966-c5b6b78eb296",
   "metadata": {},
   "source": [
    "### Table Expressions\n",
    "\n",
    "Table expressions allow us to pull in table metadata.  By having table metadata on hand, Ibis can typecheck our operations and referencecheck our calls.\n",
    "\n",
    "Let's establish some table expressions before we get started:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9ffebf29-3310-434a-8bea-83ca079a502a",
   "metadata": {},
   "outputs": [],
   "source": [
    "table_list = [\n",
    "    'fact_sale'\n",
    "    ,'dim_date'\n",
    "    ,'dim_store'\n",
    "    ,'dim_retailer'\n",
    "    ,'dim_product'\n",
    "    ,'dim_unit'\n",
    "    ,'fact_segmentation'\n",
    "    ,'dim_segment'\n",
    "]\n",
    "\n",
    "TABLES = {\n",
    "    key: iconn.table(key)\n",
    "    for key in table_list\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fbd88c4-cb4f-49c2-80da-8e9772e65e43",
   "metadata": {},
   "source": [
    "### Exploratory Functions\n",
    "\n",
    "Here we are just counting the rows to each table of interest.  By using `count()` on a table expression we can get the count:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "74073ec2-ce4a-4183-9b3b-6f049ce52efc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fact_sale row count: 3798040\n",
      "dim_date row count: 58\n",
      "dim_store row count: 1721\n",
      "dim_retailer row count: 3\n",
      "dim_product row count: 10\n",
      "dim_unit row count: 2\n",
      "fact_segmentation row count: 1721\n",
      "dim_segment row count: 3\n"
     ]
    }
   ],
   "source": [
    "for tbl in TABLES:\n",
    "    print(tbl, \"row count:\", TABLES[tbl].count().execute())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b43be5be-f272-4ecc-93aa-82dec1486e43",
   "metadata": {},
   "source": [
    "`fact_sale` is a bit big, so let's filter it down to preview it before moving on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7de79358-1b71-4a8d-b120-e6f6b33ee00f",
   "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>fact_id</th>\n",
       "      <th>date_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>unit_id</th>\n",
       "      <th>value</th>\n",
       "      <th>created_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.00</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4.64</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16.00</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>9.28</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16.00</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fact_id  date_id  store_id  product_id  unit_id  value          created_at\n",
       "0        1        1         1           1        1   8.00 2022-06-08 18:20:32\n",
       "1        2        1         1           1        2   4.64 2022-06-08 18:20:32\n",
       "2        3        2         1           1        1  16.00 2022-06-08 18:20:32\n",
       "3        4        2         1           1        2   9.28 2022-06-08 18:20:32\n",
       "4        5        3         1           1        1  16.00 2022-06-08 18:20:32"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TABLES['fact_sale'].limit(5).execute()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d73cab46-c706-446f-ad37-981cc9b6e596",
   "metadata": {},
   "source": [
    "Since we expect there to be only one row per set over the partition of `date_id` | `store_id` | `product_id` | `unit_id` (since, for this dataset, there _should_ only be one value for a given product at a given store for a given unit at a given time--it's total sales data for a week), let's check this condition real quick before moving forward."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5b44b4e1-6881-4897-9b5d-5644488793a7",
   "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_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>unit_id</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   date_id  store_id  product_id  unit_id  count\n",
       "0        1         1           1        1      2\n",
       "1        1         1           1        2      2\n",
       "2        1         1           2        1      2\n",
       "3        1         1           2        2      2\n",
       "4        1         1           3        1      2"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gbcols = [\n",
    "    'date_id'\n",
    "    ,'store_id'\n",
    "    ,'product_id'\n",
    "    ,'unit_id'\n",
    "]\n",
    "\n",
    "(\n",
    "    # fact sale in TABLES\n",
    "    TABLES['fact_sale']\n",
    "    # select only the id columns\n",
    "    .select(gbcols)\n",
    "    # group by the id columns\n",
    "    .group_by(gbcols)\n",
    "    # count occurance of set\n",
    "    .size()\n",
    "    # sort by count, descending (largest at top)\n",
    "    .sort_by(('count', False))\n",
    "    # pick top 5\n",
    "    .limit(5)\n",
    "    # execute\n",
    "    .execute()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0328f848-1f5d-4187-b72a-8c323cb9cd3e",
   "metadata": {},
   "source": [
    "Looks like there are duplicates, so let's grab one and see what a duplicate looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "65e6bc4f-ee89-41ff-9385-d79b93c6b037",
   "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>fact_id</th>\n",
       "      <th>date_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>unit_id</th>\n",
       "      <th>value</th>\n",
       "      <th>created_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1899021</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2022-06-08 18:22:12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fact_id  date_id  store_id  product_id  unit_id  value          created_at\n",
       "0        1        1         1           1        1    8.0 2022-06-08 18:20:32\n",
       "1  1899021        1         1           1        1    4.0 2022-06-08 18:22:12"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def filter_fact_sale(fact_sale=TABLES['fact_sale'], **kwargs):\n",
    "    bix = None\n",
    "    for k, v in kwargs.items():\n",
    "        bix = bix & (fact_sale[k] == v) if bix is not None else (fact_sale[k] == v)\n",
    "\n",
    "    return fact_sale.filter(bix).execute()\n",
    "\n",
    "filter_fact_sale(date_id=1, store_id=1, product_id=1, unit_id=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e05f07b0-2240-40b3-aa82-be7361c94d5a",
   "metadata": {},
   "source": [
    "In this dataset, it looks like `value` is double the other entry.  It is likely that data was doubled for upload and this error was quickly corrected.\n",
    "\n",
    "Some core principles of this table are:\n",
    "\n",
    "1. never delete data, including data uploaded in error\n",
    "2. if something is uploaded in error, fix it somewhere else and then re-upload it\n",
    "3. append the current timestamp to the data upon upload\n",
    "\n",
    "So in this table, we use `created_at` to give us the latest value of truth for each `date_id`, `store_id`, `product_id`, `unit_id` set.  So we need to pick those rows where `created_at` is equal to the `MAX(created_at OVER (PARTITION BY date_id, store_id, product_id, unit_id)`.\n",
    "\n",
    "Running this window function every time we want data can be expensive, so we should filter our data as much as possible before running it.\n",
    "\n",
    "To help filter down our data, we will join `fact_sale` on to filtered dimension tables for the values we care about.  Let's craft those filter queries now."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bd6d0eb-02ca-4a8c-b4b2-dda57392e68c",
   "metadata": {},
   "source": [
    "### Filtering `dim_date`\n",
    "\n",
    "We are given 5 date points: test start and end, lag end, and control start and end.  We can smartly lump the test period and lag period together to create two ranges: the intervention+lag period, and the control period.\n",
    "\n",
    "To filter dates, we'll find all weeks between both of those ranges:\n",
    "\n",
    "* `BETWEEN TEST_PERIOD_START AND LAG_PERIOD_END`\n",
    "* `BETWEEN CONTROL_PERIOD_START AND CONTROL_PERIOD_END`\n",
    "\n",
    "We create two distinct ranges because it's possible that we do a period over period analysis instead of a 52-week lead control and following 6 week test back-to-back."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c485d401-5e10-4217-9af3-645707472c27",
   "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_id</th>\n",
       "      <th>date_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>WEEK_00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>WEEK_01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>WEEK_02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>WEEK_03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>WEEK_04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   date_id date_value\n",
       "0        1    WEEK_00\n",
       "1        2    WEEK_01\n",
       "2        3    WEEK_02\n",
       "3        4    WEEK_03\n",
       "4        5    WEEK_04"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def filter_dim_date(control_period: iter, test_period: iter, dim_date=TABLES['dim_date']):\n",
    "    return dim_date.filter(\n",
    "        dim_date['date_value'].between(*control_period)\n",
    "        | dim_date['date_value'].between(*test_period)\n",
    "    )[dim_date]\n",
    "\n",
    "filter_dim_date([CONTROL_PERIOD_START, CONTROL_PERIOD_END], [TEST_PERIOD_START, LAG_PERIOD_END]).limit(5).execute()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dca6bd7-62bd-4471-8c3d-311a5b7cdf94",
   "metadata": {},
   "source": [
    "Note that weeks are stored as strings in this database for generality.  This query _could_ actually work with date values."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0dfa42c1-4da9-43d6-897f-48c11ac100a6",
   "metadata": {},
   "source": [
    "### Filtering `dim_store`\n",
    "\n",
    "We have an `analysis_id` (`ANALYSIS_ID`), and we have store segments for that config saved in `fact_segmentation`.  Let's filter `dim_stores` and pull in store segments, retailer names, and store names at the same time:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "21e1c9c0-3454-4f22-8ce1-dfe0b4f656d7",
   "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>store_id</th>\n",
       "      <th>store_name</th>\n",
       "      <th>segment_name</th>\n",
       "      <th>retailer_name</th>\n",
       "      <th>analysis_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>000</td>\n",
       "      <td>T</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>001</td>\n",
       "      <td>C</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>002</td>\n",
       "      <td>C</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>003</td>\n",
       "      <td>C</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>004</td>\n",
       "      <td>T</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_id store_name segment_name retailer_name  analysis_id\n",
       "0         1        000            T    RETAILER_0            1\n",
       "1         2        001            C    RETAILER_0            1\n",
       "2         3        002            C    RETAILER_0            1\n",
       "3         4        003            C    RETAILER_0            1\n",
       "4         5        004            T    RETAILER_0            1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def filter_dim_store(\n",
    "    analysis_ids: iter=[1]\n",
    "    ,dim_store=TABLES['dim_store']\n",
    "    ,dim_segment=TABLES['dim_segment']\n",
    "    ,fact_segmentation=TABLES['fact_segmentation']\n",
    "    ,dim_retailer=TABLES['dim_retailer']\n",
    "):\n",
    "    fseg = fact_segmentation.filter(\n",
    "        fact_segmentation['analysis_id'].isin(analysis_ids)\n",
    "    )['store_id', 'segment_id', 'analysis_id']\n",
    "\n",
    "    seg = fseg.inner_join(\n",
    "        dim_segment\n",
    "        ,predicates=dim_segment['segment_id'] == fseg['segment_id']\n",
    "    # relabel to avoid suffixes in the next line\n",
    "    )['store_id', 'segment_name', 'analysis_id'].relabel(\n",
    "        {'store_id': 'seg_store_id'}\n",
    "    )\n",
    "\n",
    "    store_filt = seg.inner_join(\n",
    "        dim_store\n",
    "        ,predicates=dim_store['store_id'] == seg['seg_store_id']\n",
    "    )['store_id', 'store_name', 'segment_name', 'retailer_id', 'analysis_id'].relabel(\n",
    "        {'retailer_id': 'store_retailer_id'}\n",
    "    )\n",
    "\n",
    "    result = store_filt.inner_join(\n",
    "        dim_retailer\n",
    "        ,predicates=dim_retailer['retailer_id'] == store_filt['store_retailer_id']\n",
    "    )\n",
    "    return result['store_id', 'store_name', 'segment_name', 'retailer_name', 'analysis_id']\n",
    "\n",
    "filter_dim_store().limit(5).execute()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2c16632-720e-431a-806a-46b7c97d623c",
   "metadata": {},
   "source": [
    "### Filtering `dim_product` and `dim_unit`\n",
    "\n",
    "As mentioned before, it may be tempting to filter `dim_product`.  Our example analysis uses only 6 products, so if we cut out the rest that's 11 products cross however many stores cross however many dates cross however many units we care about.  We're instead going to bucket them and include them in our analysis.  Same deal with units.\n",
    "\n",
    "For larger datasets, it may be a good idea to filter further--for example, `dim_unit` might contain multiple currencies or on hand units, and `dim_product` might contain products for multiple irrelevant companies that we would want to exclude from our analysis.\n",
    "\n",
    "For Ibis, in this case, we don't need to do anything since we'll just pull in those table expressions."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afc9f3e4-56f6-4556-ad74-3359503beb77",
   "metadata": {},
   "source": [
    "### Putting it All Together to Filter Fact Sale and Calculate `MAX(created_at)` (maxfact)\n",
    "\n",
    "Now we'll combine all of our queries to filter `fact_sale` in preparation for our maxfact query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "00bad0d0-f757-4d65-9243-8aa191178c20",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_table_reference(table_name, dict_tables=None, con=None):\n",
    "    return (\n",
    "        con.table(table_name)\n",
    "        if not dict_tables and table_name not in dict_tables\n",
    "        else dict_tables[table_name]\n",
    "    )\n",
    "\n",
    "\n",
    "def filter_fact_sale(analysis_ids: list, control_period:iter, test_period: iter, con=None, dict_tables=dict()):\n",
    "    fact_sale = get_table_reference(\"fact_sale\", dict_tables=dict_tables, con=con)\n",
    "\n",
    "    dim_store = filter_dim_store(\n",
    "        analysis_ids=analysis_ids\n",
    "        ,dim_store=get_table_reference(\"dim_store\", dict_tables=dict_tables, con=con)\n",
    "        ,dim_segment=get_table_reference(\"dim_segment\", dict_tables=dict_tables, con=con)\n",
    "        ,dim_retailer=get_table_reference(\"dim_retailer\", dict_tables=dict_tables, con=con)\n",
    "        ,fact_segmentation=get_table_reference(\"fact_segmentation\", dict_tables=dict_tables, con=con)\n",
    "    ).relabel(\n",
    "        {'store_id': 'ds_store_id'}\n",
    "    )\n",
    "\n",
    "    dim_date = filter_dim_date(\n",
    "        control_period\n",
    "        ,test_period\n",
    "        ,dim_date=get_table_reference('dim_date', dict_tables=dict_tables, con=con)\n",
    "    ).relabel(\n",
    "        {'date_id': 'dd_date_id'}\n",
    "    )\n",
    "\n",
    "    dim_product = get_table_reference(\"dim_product\", dict_tables=dict_tables, con=con).relabel(\n",
    "        {'product_id': 'dp_product_id'}\n",
    "    )\n",
    "\n",
    "    dim_unit = get_table_reference(\"dim_unit\", dict_tables=dict_tables, con=con).relabel(\n",
    "        {'unit_id': 'du_unit_id'}\n",
    "    )\n",
    "\n",
    "    cols = [\n",
    "        'analysis_id'\n",
    "        ,'date_id'\n",
    "        ,'store_id'\n",
    "        ,'product_id'\n",
    "        ,'unit_id'\n",
    "        ,'created_at'\n",
    "        ,'value'\n",
    "        ,'date_value'\n",
    "        ,'store_name'\n",
    "        ,'retailer_name'\n",
    "        ,'product_name'\n",
    "        ,'unit_name'\n",
    "        ,'segment_name'\n",
    "    ]\n",
    "\n",
    "    join = fact_sale.inner_join(\n",
    "        dim_date\n",
    "        ,predicates=dim_date['dd_date_id'] == fact_sale['date_id']\n",
    "    ).inner_join(\n",
    "        dim_store\n",
    "        ,predicates=dim_store['ds_store_id'] == fact_sale['store_id']\n",
    "    ).inner_join(\n",
    "        dim_product\n",
    "        ,predicates=dim_product['dp_product_id'] == fact_sale['product_id']\n",
    "    ).inner_join(\n",
    "        dim_unit\n",
    "        ,predicates=dim_unit['du_unit_id'] == fact_sale['unit_id']\n",
    "    ).select(cols)\n",
    "\n",
    "    maxfactw = ibis.window(group_by=['date_id', 'store_id', 'product_id', 'unit_id'])\n",
    "    maxfact = fact_sale['created_at'].max().over(maxfactw).name('maxfact')\n",
    "\n",
    "    return join[join, maxfact]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2bd96a17-ec9d-496c-88c1-6349be0bc824",
   "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>analysis_id</th>\n",
       "      <th>date_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>unit_id</th>\n",
       "      <th>created_at</th>\n",
       "      <th>value</th>\n",
       "      <th>date_value</th>\n",
       "      <th>store_name</th>\n",
       "      <th>retailer_name</th>\n",
       "      <th>product_name</th>\n",
       "      <th>unit_name</th>\n",
       "      <th>segment_name</th>\n",
       "      <th>maxfact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "      <td>8.00</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>000</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>PRODUCT_0</td>\n",
       "      <td>SALES_UNITS</td>\n",
       "      <td>T</td>\n",
       "      <td>2022-06-08 18:22:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-06-08 18:22:12</td>\n",
       "      <td>4.00</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>000</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>PRODUCT_0</td>\n",
       "      <td>SALES_UNITS</td>\n",
       "      <td>T</td>\n",
       "      <td>2022-06-08 18:22:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "      <td>4.64</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>000</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>PRODUCT_0</td>\n",
       "      <td>SALES_USD</td>\n",
       "      <td>T</td>\n",
       "      <td>2022-06-08 18:22:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2022-06-08 18:22:12</td>\n",
       "      <td>2.32</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>000</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>PRODUCT_0</td>\n",
       "      <td>SALES_USD</td>\n",
       "      <td>T</td>\n",
       "      <td>2022-06-08 18:22:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-06-08 18:20:32</td>\n",
       "      <td>8.00</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>000</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>PRODUCT_1</td>\n",
       "      <td>SALES_UNITS</td>\n",
       "      <td>T</td>\n",
       "      <td>2022-06-08 18:22:12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   analysis_id  date_id  store_id  product_id  unit_id          created_at  \\\n",
       "0            1        1         1           1        1 2022-06-08 18:20:32   \n",
       "1            1        1         1           1        1 2022-06-08 18:22:12   \n",
       "2            1        1         1           1        2 2022-06-08 18:20:32   \n",
       "3            1        1         1           1        2 2022-06-08 18:22:12   \n",
       "4            1        1         1           2        1 2022-06-08 18:20:32   \n",
       "\n",
       "   value date_value store_name retailer_name product_name    unit_name  \\\n",
       "0   8.00    WEEK_00        000    RETAILER_0    PRODUCT_0  SALES_UNITS   \n",
       "1   4.00    WEEK_00        000    RETAILER_0    PRODUCT_0  SALES_UNITS   \n",
       "2   4.64    WEEK_00        000    RETAILER_0    PRODUCT_0    SALES_USD   \n",
       "3   2.32    WEEK_00        000    RETAILER_0    PRODUCT_0    SALES_USD   \n",
       "4   8.00    WEEK_00        000    RETAILER_0    PRODUCT_1  SALES_UNITS   \n",
       "\n",
       "  segment_name             maxfact  \n",
       "0            T 2022-06-08 18:22:12  \n",
       "1            T 2022-06-08 18:22:12  \n",
       "2            T 2022-06-08 18:22:12  \n",
       "3            T 2022-06-08 18:22:12  \n",
       "4            T 2022-06-08 18:22:12  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_fact_sale([1], [CONTROL_PERIOD_START, CONTROL_PERIOD_END], [TEST_PERIOD_START, LAG_PERIOD_END], con=iconn).limit(5).execute()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f7b75a9-47e1-422d-80f2-b642fc7f540b",
   "metadata": {},
   "source": [
    "### \"Pivoting\" using `SUM(CASE`/`WHEN IN set THEN value ELSE 0 END) AS name` on Arbitrary Sets\n",
    "\n",
    "We now have one last thing to do: flatten our data by bucketing our products into their respective groups.  We can pivot our data by aggregating `fact_sale.value` if a product name is in the set.\n",
    "\n",
    "Let's create some functions to help us do that:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "398502dc-0903-4be6-bf3a-1f625cf385af",
   "metadata": {},
   "source": [
    "First, let's deal with our defined groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "28d7a260-7f19-4dc7-8312-c194f0f72f42",
   "metadata": {},
   "outputs": [],
   "source": [
    "# return a list here so we can add up all of our case/when statement lists\n",
    "# and then format a final case/when statement\n",
    "def cw_def_groups(product_groups: dict, maxfact_expr) -> list:\n",
    "    cws = [\n",
    "        maxfact_expr['product_name'].isin(v).ifelse(maxfact_expr['value'], 0).sum().name(k)\n",
    "        for k, v in product_groups.items()\n",
    "    ]\n",
    "    return cws"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43e76296-7546-4387-95c5-35fa1e433bf0",
   "metadata": {},
   "source": [
    "Next, let's deal included, excluded, and all:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4f1fa195-3674-45dc-b7cf-ae69842692be",
   "metadata": {},
   "outputs": [],
   "source": [
    "def cw_undef_groups(product_groups: dict, maxfact_expr) -> list:\n",
    "    pset = set()\n",
    "    for pg in product_groups.values():\n",
    "        pset = pset.union(set(pg))\n",
    "\n",
    "    cws = [\n",
    "        maxfact_expr['product_name'].isin(pset).ifelse(maxfact_expr['value'], 0).sum().name('included_products')\n",
    "        ,maxfact_expr['product_name'].notin(pset).ifelse(maxfact_expr['value'], 0).sum().name('excluded_products')\n",
    "        ,maxfact_expr['value'].sum().name('all_products')\n",
    "    ]\n",
    "    return cws"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81e9b317-6f28-4aaf-85ca-4fb034d2a093",
   "metadata": {},
   "source": [
    "Finally, we will compile the aggregate expressions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8d5dae11-4c41-4620-8b5c-cd6e06274367",
   "metadata": {},
   "outputs": [],
   "source": [
    "def cw_stms(product_groups: dict, maxfact_expr):\n",
    "    grps = cw_def_groups(product_groups, maxfact_expr)\n",
    "    misc = cw_undef_groups(product_groups, maxfact_expr)\n",
    "    return grps + misc"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7dce82f-4286-43f9-a915-537528e97b3f",
   "metadata": {},
   "source": [
    "### The Final Query\n",
    "\n",
    "By putting this all together, we get our Sales Data Query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "960f779b-fcaf-4d27-8ec7-3a3963b3b671",
   "metadata": {},
   "outputs": [],
   "source": [
    "def sales_data_query(\n",
    "    analysis_ids: list\n",
    "    ,product_groups: dict\n",
    "    ,control_period: iter\n",
    "    ,test_period: iter\n",
    "    ,con=iconn\n",
    "    ,dict_tables: dict=None\n",
    ") -> str:\n",
    "    maxfact = filter_fact_sale(\n",
    "        analysis_ids\n",
    "        ,control_period\n",
    "        ,test_period\n",
    "        ,con=con\n",
    "        ,dict_tables=dict_tables\n",
    "    )\n",
    "\n",
    "    gbcols = [\n",
    "        'analysis_id'\n",
    "        ,'date_id'\n",
    "        ,'store_id'\n",
    "        ,'unit_id'\n",
    "        ,'date_value'\n",
    "        ,'store_name'\n",
    "        ,'retailer_name'\n",
    "        ,'segment_name'\n",
    "        ,'unit_name'\n",
    "    ]\n",
    "\n",
    "    cws = cw_stms(product_groups, maxfact)\n",
    "\n",
    "    result = (\n",
    "        maxfact\n",
    "        # Filter to latest rows\n",
    "        .filter(maxfact['created_at'] == maxfact['created_at'])\n",
    "        # select necessary columns\n",
    "        .select(gbcols + ['product_name', 'value'])\n",
    "        # Group by store, date, unit\n",
    "        .groupby(gbcols)\n",
    "        # aggregate over our groups\n",
    "        .aggregate(cws)\n",
    "    )\n",
    "    return result\n",
    "\n",
    "sdq = sales_data_query(\n",
    "    [ANALYSIS_ID]\n",
    "    ,PRODUCT_GROUPS\n",
    "    ,[CONTROL_PERIOD_START, CONTROL_PERIOD_END]\n",
    "    ,[TEST_PERIOD_START, LAG_PERIOD_END]\n",
    "    ,con=iconn\n",
    "    ,dict_tables=TABLES\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4dbfd31-bd9e-4e7d-8c19-0b3c1d156c68",
   "metadata": {},
   "source": [
    "And running it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f0caa165-3c6a-4740-bbdc-1d9150e32811",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(66336, 15)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales = sdq.execute()\n",
    "\n",
    "sales.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e7d08b1f-0688-4dae-b65c-da38e4b4e52a",
   "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>analysis_id</th>\n",
       "      <th>date_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>unit_id</th>\n",
       "      <th>date_value</th>\n",
       "      <th>store_name</th>\n",
       "      <th>retailer_name</th>\n",
       "      <th>segment_name</th>\n",
       "      <th>unit_name</th>\n",
       "      <th>group_1</th>\n",
       "      <th>group_2</th>\n",
       "      <th>group_3</th>\n",
       "      <th>included_products</th>\n",
       "      <th>excluded_products</th>\n",
       "      <th>all_products</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1123</td>\n",
       "      <td>1</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>000</td>\n",
       "      <td>RETAILER_2</td>\n",
       "      <td>T</td>\n",
       "      <td>SALES_UNITS</td>\n",
       "      <td>27.00</td>\n",
       "      <td>18.00</td>\n",
       "      <td>36.00</td>\n",
       "      <td>81.00</td>\n",
       "      <td>63.00</td>\n",
       "      <td>144.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1123</td>\n",
       "      <td>2</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>000</td>\n",
       "      <td>RETAILER_2</td>\n",
       "      <td>T</td>\n",
       "      <td>SALES_USD</td>\n",
       "      <td>8.94</td>\n",
       "      <td>27.18</td>\n",
       "      <td>12.24</td>\n",
       "      <td>48.36</td>\n",
       "      <td>119.52</td>\n",
       "      <td>167.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1124</td>\n",
       "      <td>1</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>001</td>\n",
       "      <td>RETAILER_2</td>\n",
       "      <td>C</td>\n",
       "      <td>SALES_UNITS</td>\n",
       "      <td>18.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>42.00</td>\n",
       "      <td>39.00</td>\n",
       "      <td>81.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1124</td>\n",
       "      <td>2</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>001</td>\n",
       "      <td>RETAILER_2</td>\n",
       "      <td>C</td>\n",
       "      <td>SALES_USD</td>\n",
       "      <td>7.56</td>\n",
       "      <td>18.12</td>\n",
       "      <td>10.17</td>\n",
       "      <td>35.85</td>\n",
       "      <td>73.56</td>\n",
       "      <td>109.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1125</td>\n",
       "      <td>1</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>002</td>\n",
       "      <td>RETAILER_2</td>\n",
       "      <td>C</td>\n",
       "      <td>SALES_UNITS</td>\n",
       "      <td>3.00</td>\n",
       "      <td>21.00</td>\n",
       "      <td>39.00</td>\n",
       "      <td>63.00</td>\n",
       "      <td>63.00</td>\n",
       "      <td>126.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   analysis_id  date_id  store_id  unit_id date_value store_name  \\\n",
       "0            3        1      1123        1    WEEK_00        000   \n",
       "1            3        1      1123        2    WEEK_00        000   \n",
       "2            3        1      1124        1    WEEK_00        001   \n",
       "3            3        1      1124        2    WEEK_00        001   \n",
       "4            3        1      1125        1    WEEK_00        002   \n",
       "\n",
       "  retailer_name segment_name    unit_name  group_1  group_2  group_3  \\\n",
       "0    RETAILER_2            T  SALES_UNITS    27.00    18.00    36.00   \n",
       "1    RETAILER_2            T    SALES_USD     8.94    27.18    12.24   \n",
       "2    RETAILER_2            C  SALES_UNITS    18.00    12.00    12.00   \n",
       "3    RETAILER_2            C    SALES_USD     7.56    18.12    10.17   \n",
       "4    RETAILER_2            C  SALES_UNITS     3.00    21.00    39.00   \n",
       "\n",
       "   included_products  excluded_products  all_products  \n",
       "0              81.00              63.00        144.00  \n",
       "1              48.36             119.52        167.88  \n",
       "2              42.00              39.00         81.00  \n",
       "3              35.85              73.56        109.41  \n",
       "4              63.00              63.00        126.00  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5da66914-f39c-477f-8bdc-a433314dd3ed",
   "metadata": {},
   "source": [
    "Now that we have our data in a pandas DataFrame, we can export, transform, and filter as we (or our analysts) see fit."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76c6de60-5de9-4a95-8a8a-8c3787fd1ebd",
   "metadata": {},
   "source": [
    "### All Functions, Together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91c368a5-c51f-41d2-9f4b-aa219ff4cc2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_dim_date(control_period: iter, test_period: iter, dim_date=TABLES['dim_date']):\n",
    "    return dim_date.filter(\n",
    "        dim_date['date_value'].between(*control_period)\n",
    "        | dim_date['date_value'].between(*test_period)\n",
    "    )[dim_date]\n",
    "\n",
    "\n",
    "def filter_dim_store(\n",
    "    analysis_ids: iter=[1]\n",
    "    ,dim_store=TABLES['dim_store']\n",
    "    ,dim_segment=TABLES['dim_segment']\n",
    "    ,fact_segmentation=TABLES['fact_segmentation']\n",
    "    ,dim_retailer=TABLES['dim_retailer']\n",
    "):\n",
    "    fseg = fact_segmentation.filter(\n",
    "        fact_segmentation['analysis_id'].isin(analysis_ids)\n",
    "    )['store_id', 'segment_id', 'analysis_id']\n",
    "\n",
    "    seg = fseg.inner_join(\n",
    "        dim_segment\n",
    "        ,predicates=dim_segment['segment_id'] == fseg['segment_id']\n",
    "    # relabel to avoid suffixes in the next line\n",
    "    )['store_id', 'segment_name', 'analysis_id'].relabel(\n",
    "        {'store_id': 'seg_store_id'}\n",
    "    )\n",
    "\n",
    "    store_filt = seg.inner_join(\n",
    "        dim_store\n",
    "        ,predicates=dim_store['store_id'] == seg['seg_store_id']\n",
    "    )['store_id', 'store_name', 'segment_name', 'retailer_id', 'analysis_id'].relabel(\n",
    "        {'retailer_id': 'store_retailer_id'}\n",
    "    )\n",
    "\n",
    "    result = store_filt.inner_join(\n",
    "        dim_retailer\n",
    "        ,predicates=dim_retailer['retailer_id'] == store_filt['store_retailer_id']\n",
    "    )\n",
    "    return result['store_id', 'store_name', 'segment_name', 'retailer_name', 'analysis_id']\n",
    "\n",
    "\n",
    "def get_table_reference(table_name, dict_tables=None, con=None):\n",
    "    return (\n",
    "        con.table(table_name)\n",
    "        if not dict_tables and table_name not in dict_tables\n",
    "        else dict_tables[table_name]\n",
    "    )\n",
    "\n",
    "\n",
    "def filter_fact_sale(analysis_ids: list, control_period:iter, test_period: iter, con=None, **tables):\n",
    "    fact_sale = get_table_reference(\"fact_sale\", dict_tables=tables, con=con)\n",
    "\n",
    "    dim_store = filter_dim_store(\n",
    "        analysis_ids=analysis_ids\n",
    "        ,dim_store=get_table_reference(\"dim_store\", dict_tables=tables, con=con)\n",
    "        ,dim_segment=get_table_reference(\"dim_segment\", dict_tables=tables, con=con)\n",
    "        ,dim_retailer=get_table_reference(\"dim_retailer\", dict_tables=tables, con=con)\n",
    "        ,fact_segmentation=get_table_reference(\"fact_segmentation\", dict_tables=tables, con=con)\n",
    "    ).relabel(\n",
    "        {'store_id': 'ds_store_id'}\n",
    "    )\n",
    "\n",
    "    dim_date = filter_dim_date(\n",
    "        control_period\n",
    "        ,test_period\n",
    "        ,dim_date=get_table_reference('dim_date', dict_tables=tables, con=con)\n",
    "    ).relabel(\n",
    "        {'date_id': 'dd_date_id'}\n",
    "    )\n",
    "\n",
    "    dim_product = get_table_reference(\"dim_product\", dict_tables=tables, con=con).relabel(\n",
    "        {'product_id': 'dp_product_id'}\n",
    "    )\n",
    "\n",
    "    dim_unit = get_table_reference(\"dim_unit\", dict_tables=tables, con=con).relabel(\n",
    "        {'unit_id': 'du_unit_id'}\n",
    "    )\n",
    "\n",
    "    cols = [\n",
    "        'analysis_id'\n",
    "        ,'date_id'\n",
    "        ,'store_id'\n",
    "        ,'product_id'\n",
    "        ,'unit_id'\n",
    "        ,'created_at'\n",
    "        ,'value'\n",
    "        ,'date_value'\n",
    "        ,'store_name'\n",
    "        ,'retailer_name'\n",
    "        ,'product_name'\n",
    "        ,'unit_name'\n",
    "        ,'segment_name'\n",
    "    ]\n",
    "\n",
    "    join = fact_sale.inner_join(\n",
    "        dim_date\n",
    "        ,predicates=dim_date['dd_date_id'] == fact_sale['date_id']\n",
    "    ).inner_join(\n",
    "        dim_store\n",
    "        ,predicates=dim_store['ds_store_id'] == fact_sale['store_id']\n",
    "    ).inner_join(\n",
    "        dim_product\n",
    "        ,predicates=dim_product['dp_product_id'] == fact_sale['product_id']\n",
    "    ).inner_join(\n",
    "        dim_unit\n",
    "        ,predicates=dim_unit['du_unit_id'] == fact_sale['unit_id']\n",
    "    ).select(cols)\n",
    "\n",
    "    maxfactw = ibis.window(group_by=['date_id', 'store_id', 'product_id', 'unit_id'])\n",
    "    maxfact = fact_sale['created_at'].max().over(maxfactw).name('maxfact')\n",
    "\n",
    "    return join[join, maxfact]\n",
    "\n",
    "\n",
    "# return a list here so we can add up all of our case/when statement lists\n",
    "# and then format a final case/when statement\n",
    "def cw_def_groups(product_groups: dict, maxfact_expr) -> list:\n",
    "    cws = [\n",
    "        maxfact_expr['product_name'].isin(v).ifelse(maxfact_expr['value'], 0).sum().name(k)\n",
    "        for k, v in product_groups.items()\n",
    "    ]\n",
    "    return cws\n",
    "\n",
    "\n",
    "def cw_undef_groups(product_groups: dict, maxfact_expr) -> list:\n",
    "    pset = set()\n",
    "    for pg in product_groups.values():\n",
    "        pset = pset.union(set(pg))\n",
    "\n",
    "    cws = [\n",
    "        maxfact_expr['product_name'].isin(pset).ifelse(maxfact_expr['value'], 0).sum().name('included_products')\n",
    "        ,maxfact_expr['product_name'].notin(pset).ifelse(maxfact_expr['value'], 0).sum().name('excluded_products')\n",
    "        ,maxfact_expr['value'].sum().name('all_products')\n",
    "    ]\n",
    "    return cws\n",
    "\n",
    "\n",
    "def cw_stms(product_groups: dict, maxfact_expr):\n",
    "    grps = cw_def_groups(product_groups, maxfact_expr)\n",
    "    misc = cw_undef_groups(product_groups, maxfact_expr)\n",
    "    return grps + misc\n",
    "\n",
    "\n",
    "def sales_data_query(\n",
    "    analysis_ids: list\n",
    "    ,product_groups: dict\n",
    "    ,control_period: iter\n",
    "    ,test_period: iter\n",
    "    ,con=iconn\n",
    "    ,dict_tables: dict=None\n",
    ") -> str:\n",
    "    maxfact = filter_fact_sale(\n",
    "        analysis_ids\n",
    "        ,control_period\n",
    "        ,test_period\n",
    "        ,con=con\n",
    "        ,**dict_tables\n",
    "    )\n",
    "\n",
    "    gbcols = [\n",
    "        'analysis_id'\n",
    "        ,'date_id'\n",
    "        ,'store_id'\n",
    "        ,'unit_id'\n",
    "        ,'date_value'\n",
    "        ,'store_name'\n",
    "        ,'retailer_name'\n",
    "        ,'segment_name'\n",
    "        ,'unit_name'\n",
    "    ]\n",
    "\n",
    "    cws = cw_stms(product_groups, maxfact)\n",
    "\n",
    "    result = (\n",
    "        maxfact\n",
    "        # Filter to latest rows\n",
    "        .filter(maxfact['created_at'] == maxfact['created_at'])\n",
    "        # select necessary columns\n",
    "        .select(gbcols + ['product_name', 'value'])\n",
    "        # Group by store, date, unit\n",
    "        .groupby(gbcols)\n",
    "        # aggregate over our groups\n",
    "        .aggregate(cws)\n",
    "    )\n",
    "    return result\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6205a579-fa1a-4c46-b9eb-31d6564a7cfe",
   "metadata": {},
   "source": [
    "# https://github.com/p-a-a-a-trick/ibis-sales-query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0487c771-d89c-4e6e-809f-8b62f9fff431",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}