QueryingSalesDatafStringSQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7f15bb58-3f22-4dfc-a837-f459771b1cb5",
   "metadata": {},
   "source": [
    "# Querying Sales Data fString SQL\n",
    "## Introduction\n",
    "In this notebook, we will use fString queries 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 QueryingSalesDataIbis.ipynb\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"
   ]
  },
  {
   "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 SQL fString, we will use a sqlite3 connector and pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c315e18f-ef43-4928-9ab8-bd84d2893cab",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "sconn = sqlite3.connect(SALES_DB)\n",
    "\n",
    "\n",
    "# an ipynb classic\n",
    "def read_sql(sql, con=sconn):\n",
    "    return pd.read_sql(sql, con)"
   ]
  },
  {
   "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": [
    "def table_ct_fstr(table_name: str):\n",
    "    sql = f\"select count(*) as ct from {table_name}\"\n",
    "    return read_sql(sql)['ct'][0]\n",
    "\n",
    "for tbl in ['fact_sale', 'dim_date', 'dim_store', 'dim_retailer', 'dim_product', 'dim_unit', 'fact_segmentation', 'dim_segment']:\n",
    "    print(tbl, \"row count:\", table_ct_fstr(tbl))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b43be5be-f272-4ecc-93aa-82dec1486e43",
   "metadata": {},
   "source": [
    "`fact_sale` is a bit big, so let's filter it down preview it before moving on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"SELECT * FROM fact_sale LIMIT 5\"\n",
    "read_sql(sql)"
   ]
  },
  {
   "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`, let's check this condition real quick before moving forward."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "52797531-4c08-4bf9-b2e9-7c6b694f411d",
   "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(fact_id)</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(fact_id)\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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "SELECT\n",
    "    date_id\n",
    "    ,store_id\n",
    "    ,product_id\n",
    "    ,unit_id\n",
    "    ,count(fact_id)\n",
    "FROM\n",
    "    fact_sale\n",
    "GROUP BY\n",
    "    1\n",
    "    ,2\n",
    "    ,3\n",
    "    ,4\n",
    "HAVING\n",
    "    count(fact_id) > 1\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "read_sql(sql)"
   ]
  },
  {
   "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": 7,
   "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",
       "      <th>date_id</th>\n",
       "      <th>date_value</th>\n",
       "      <th>store_id</th>\n",
       "      <th>store_name</th>\n",
       "      <th>retailer_id</th>\n",
       "      <th>retailer_id</th>\n",
       "      <th>retailer_name</th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>unit_id</th>\n",
       "      <th>unit_name</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",
       "      <td>1</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>1</td>\n",
       "      <td>000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>1</td>\n",
       "      <td>PRODUCT_0</td>\n",
       "      <td>1</td>\n",
       "      <td>SALES_UNITS</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",
       "      <td>1</td>\n",
       "      <td>WEEK_00</td>\n",
       "      <td>1</td>\n",
       "      <td>000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>RETAILER_0</td>\n",
       "      <td>1</td>\n",
       "      <td>PRODUCT_0</td>\n",
       "      <td>1</td>\n",
       "      <td>SALES_UNITS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fact_id  date_id  store_id  product_id  unit_id  value  \\\n",
       "0        1        1         1           1        1    8.0   \n",
       "1  1899021        1         1           1        1    4.0   \n",
       "\n",
       "            created_at  date_id date_value  store_id store_name  retailer_id  \\\n",
       "0  2022-06-08 18:20:32        1    WEEK_00         1        000            1   \n",
       "1  2022-06-08 18:22:12        1    WEEK_00         1        000            1   \n",
       "\n",
       "   retailer_id retailer_name  product_id product_name  unit_id    unit_name  \n",
       "0            1    RETAILER_0           1    PRODUCT_0        1  SALES_UNITS  \n",
       "1            1    RETAILER_0           1    PRODUCT_0        1  SALES_UNITS  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def fact_sale_filter_fstr(**kwargs):\n",
    "    sql = f\"\"\"\n",
    "    SELECT\n",
    "        *\n",
    "    FROM\n",
    "        fact_sale fs\n",
    "    JOIN\n",
    "        dim_date dd\n",
    "        ON dd.date_id = fs.date_id\n",
    "    JOIN\n",
    "        dim_store ds\n",
    "        ON ds.store_id = fs.store_id\n",
    "    JOIN\n",
    "        dim_retailer dr\n",
    "        ON dr.retailer_id = ds.retailer_id\n",
    "    JOIN\n",
    "        dim_product dp\n",
    "        ON dp.product_id = fs.product_id\n",
    "    JOIN\n",
    "        dim_unit du\n",
    "        ON du.unit_id = fs.unit_id\n",
    "    \"\"\"\n",
    "    wstm = \"fs.{} = {}\"\n",
    "    wstms = [wstm.format(k, v) for k, v in kwargs.items()]\n",
    "\n",
    "    if not wstms:\n",
    "        raise RuntimeError(\"you should filter on something there bud\")\n",
    "\n",
    "    sql = sql + 'WHERE\\n        ' + '\\n    AND\\n        '.join(wstms)\n",
    "    return read_sql(sql)\n",
    "\n",
    "fact_sale_filter_fstr(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": 8,
   "id": "c485d401-5e10-4217-9af3-645707472c27",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT\n",
      "        date_id\n",
      "        ,date_value\n",
      "    FROM\n",
      "        dim_date\n",
      "    WHERE\n",
      "        date_value BETWEEN 'WEEK_00' AND 'WEEK_51'\n",
      "    OR\n",
      "        date_value BETWEEN 'WEEK_52' AND 'WEEK_57'\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "def fstr_dim_date(control_period, test_period):\n",
    "    sql = \"\"\"\n",
    "    SELECT\n",
    "        date_id\n",
    "        ,date_value\n",
    "    FROM\n",
    "        dim_date\n",
    "    WHERE\n",
    "        date_value BETWEEN '{}' AND '{}'\n",
    "    OR\n",
    "        date_value BETWEEN '{}' AND '{}'\n",
    "    \"\"\".format(*control_period, *test_period)\n",
    "    return sql\n",
    "\n",
    "q_dim_date = fstr_dim_date([CONTROL_PERIOD_START, CONTROL_PERIOD_END], [TEST_PERIOD_START, LAG_PERIOD_END])\n",
    "print(q_dim_date)"
   ]
  },
  {
   "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": 9,
   "id": "21e1c9c0-3454-4f22-8ce1-dfe0b4f656d7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT\n",
      "        ds.store_id\n",
      "        ,ds.store_name\n",
      "        ,dseg.segment_name\n",
      "        ,dr.retailer_id\n",
      "        ,dr.retailer_name\n",
      "    FROM\n",
      "        fact_segmentation fseg\n",
      "    JOIN\n",
      "        dim_segment dseg\n",
      "        ON dseg.segment_id = fseg.segment_id\n",
      "    JOIN\n",
      "        dim_store ds\n",
      "        ON ds.store_id = fseg.store_id\n",
      "    JOIN\n",
      "        dim_retailer dr\n",
      "        ON dr.retailer_id = ds.retailer_id\n",
      "    WHERE\n",
      "        fseg.analysis_id IN (3)\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "def fstr_dim_store(analysis_ids: list):\n",
    "    r\"filter dim_stores using an analysis store segmentation\"\n",
    "    id_lst = ', '.join(str(e) for e in analysis_ids)\n",
    "\n",
    "    sql = f\"\"\"\n",
    "    SELECT\n",
    "        ds.store_id\n",
    "        ,ds.store_name\n",
    "        ,dseg.segment_name\n",
    "        ,dr.retailer_id\n",
    "        ,dr.retailer_name\n",
    "    FROM\n",
    "        fact_segmentation fseg\n",
    "    JOIN\n",
    "        dim_segment dseg\n",
    "        ON dseg.segment_id = fseg.segment_id\n",
    "    JOIN\n",
    "        dim_store ds\n",
    "        ON ds.store_id = fseg.store_id\n",
    "    JOIN\n",
    "        dim_retailer dr\n",
    "        ON dr.retailer_id = ds.retailer_id\n",
    "    WHERE\n",
    "        fseg.analysis_id IN ({id_lst})\n",
    "    \"\"\"\n",
    "    return sql\n",
    "\n",
    "q_dim_store = fstr_dim_store([ANALYSIS_ID])\n",
    "print(q_dim_store)"
   ]
  },
  {
   "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "08a90bca-d4a3-4599-8fb8-523c9be2da95",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT\n",
      "        product_id\n",
      "        ,product_name\n",
      "    FROM\n",
      "        dim_product\n",
      "    \n",
      "\n",
      "    SELECT\n",
      "        unit_id\n",
      "        ,unit_name\n",
      "    FROM\n",
      "        dim_unit\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "def fstr_dim_product():\n",
    "    sql = \"\"\"\n",
    "    SELECT\n",
    "        product_id\n",
    "        ,product_name\n",
    "    FROM\n",
    "        dim_product\n",
    "    \"\"\"\n",
    "    return sql\n",
    "\n",
    "\n",
    "def fstr_dim_unit():\n",
    "    sql = \"\"\"\n",
    "    SELECT\n",
    "        unit_id\n",
    "        ,unit_name\n",
    "    FROM\n",
    "        dim_unit\n",
    "    \"\"\"\n",
    "    return sql\n",
    "\n",
    "q_dim_product = fstr_dim_product()\n",
    "print(q_dim_product)\n",
    "q_dim_unit = fstr_dim_unit()\n",
    "print(q_dim_unit)"
   ]
  },
  {
   "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": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT\n",
      "        fs.fact_id\n",
      "        ,fs.created_at\n",
      "        ,MAX(fs.created_at) OVER (PARTITION BY fs.date_id, fs.store_id, fs.product_id, fs.unit_id) AS maxfact\n",
      "        ,fs.date_id\n",
      "        ,fs.store_id\n",
      "        ,fs.product_id\n",
      "        ,fs.unit_id\n",
      "        ,fs.value\n",
      "        ,dd.date_value\n",
      "        ,ds.store_name\n",
      "        ,ds.retailer_name\n",
      "        ,ds.segment_name\n",
      "        ,dp.product_name\n",
      "        ,du.unit_name\n",
      "    FROM\n",
      "        fact_sale fs\n",
      "    JOIN (\n",
      "    \n",
      "    SELECT\n",
      "        date_id\n",
      "        ,date_value\n",
      "    FROM\n",
      "        dim_date\n",
      "    WHERE\n",
      "        date_value BETWEEN 'WEEK_00' AND 'WEEK_51'\n",
      "    OR\n",
      "        date_value BETWEEN 'WEEK_52' AND 'WEEK_57'\n",
      "    \n",
      "        ) dd\n",
      "        ON dd.date_id = fs.date_id\n",
      "    JOIN (\n",
      "    \n",
      "    SELECT\n",
      "        ds.store_id\n",
      "        ,ds.store_name\n",
      "        ,dseg.segment_name\n",
      "        ,dr.retailer_id\n",
      "        ,dr.retailer_name\n",
      "    FROM\n",
      "        fact_segmentation fseg\n",
      "    JOIN\n",
      "        dim_segment dseg\n",
      "        ON dseg.segment_id = fseg.segment_id\n",
      "    JOIN\n",
      "        dim_store ds\n",
      "        ON ds.store_id = fseg.store_id\n",
      "    JOIN\n",
      "        dim_retailer dr\n",
      "        ON dr.retailer_id = ds.retailer_id\n",
      "    WHERE\n",
      "        fseg.analysis_id IN (3)\n",
      "    \n",
      "        ) ds\n",
      "        ON ds.store_id = fs.store_id\n",
      "    JOIN (\n",
      "    \n",
      "    SELECT\n",
      "        product_id\n",
      "        ,product_name\n",
      "    FROM\n",
      "        dim_product\n",
      "    \n",
      "        ) dp\n",
      "        ON dp.product_id = fs.product_id\n",
      "    JOIN (\n",
      "    \n",
      "    SELECT\n",
      "        unit_id\n",
      "        ,unit_name\n",
      "    FROM\n",
      "        dim_unit\n",
      "    \n",
      "        ) du\n",
      "        ON du.unit_id = fs.unit_id\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "def fstr_fact_sale(analysis_ids: list, control_period:iter, test_period: iter) -> str:\n",
    "    dim_date = fstr_dim_date(control_period, test_period)\n",
    "    dim_store = fstr_dim_store(analysis_ids)\n",
    "    dim_product = fstr_dim_product()\n",
    "    dim_unit = fstr_dim_unit()\n",
    "\n",
    "    sql = f\"\"\"\n",
    "    SELECT\n",
    "        fs.fact_id\n",
    "        ,fs.created_at\n",
    "        ,MAX(fs.created_at) OVER (PARTITION BY fs.date_id, fs.store_id, fs.product_id, fs.unit_id) AS maxfact\n",
    "        ,fs.date_id\n",
    "        ,fs.store_id\n",
    "        ,fs.product_id\n",
    "        ,fs.unit_id\n",
    "        ,fs.value\n",
    "        ,dd.date_value\n",
    "        ,ds.store_name\n",
    "        ,ds.retailer_name\n",
    "        ,ds.segment_name\n",
    "        ,dp.product_name\n",
    "        ,du.unit_name\n",
    "    FROM\n",
    "        fact_sale fs\n",
    "    JOIN (\n",
    "    {dim_date}\n",
    "        ) dd\n",
    "        ON dd.date_id = fs.date_id\n",
    "    JOIN (\n",
    "    {dim_store}\n",
    "        ) ds\n",
    "        ON ds.store_id = fs.store_id\n",
    "    JOIN (\n",
    "    {dim_product}\n",
    "        ) dp\n",
    "        ON dp.product_id = fs.product_id\n",
    "    JOIN (\n",
    "    {dim_unit}\n",
    "        ) du\n",
    "        ON du.unit_id = fs.unit_id\n",
    "    \"\"\"\n",
    "    return sql\n",
    "\n",
    "q_fact_sale = fstr_fact_sale([ANALYSIS_ID], [CONTROL_PERIOD_START, CONTROL_PERIOD_END], [TEST_PERIOD_START, LAG_PERIOD_END])\n",
    "print(q_fact_sale)"
   ]
  },
  {
   "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": 12,
   "id": "28d7a260-7f19-4dc7-8312-c194f0f72f42",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SUM(CASE WHEN product_name IN ('PRODUCT_0', 'PRODUCT_4') THEN fs.value ELSE 0 END) AS \"group_1\"\n",
      "SUM(CASE WHEN product_name IN ('PRODUCT_1') THEN fs.value ELSE 0 END) AS \"group_2\"\n",
      "SUM(CASE WHEN product_name IN ('PRODUCT_3', 'PRODUCT_5', 'PRODUCT_9') THEN fs.value ELSE 0 END) AS \"group_3\"\n"
     ]
    }
   ],
   "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) -> list:\n",
    "    def fmt_set(s) -> str:\n",
    "        return \"'\" + \"', '\".join(str(e) for e in s) + \"'\"\n",
    "\n",
    "    cw_skel = 'SUM(CASE WHEN product_name IN ({}) THEN fs.value ELSE 0 END) AS \"{}\"'\n",
    "    return [cw_skel.format(fmt_set(v), k) for k, v in product_groups.items()]\n",
    "\n",
    "q_agg_cw_def_groups = cw_def_groups(PRODUCT_GROUPS)\n",
    "print('\\n'.join(q_agg_cw_def_groups))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43e76296-7546-4387-95c5-35fa1e433bf0",
   "metadata": {},
   "source": [
    "Next, let's deal included, excluded, and all:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "4f1fa195-3674-45dc-b7cf-ae69842692be",
   "metadata": {},
   "outputs": [],
   "source": [
    "def cw_undef_groups(product_groups: dict) -> list:\n",
    "    def fmt_set(s) -> str:\n",
    "        return \"'\" + \"', '\".join(sorted(str(e) for e in s)) + \"'\"\n",
    "\n",
    "    cw_skel = 'SUM(CASE WHEN product_name {} IN ({}) THEN fs.value ELSE 0 END) AS \"{}\"'\n",
    "\n",
    "    pset = set()\n",
    "    for pg in product_groups.values():\n",
    "        pset = pset.union(set(pg))\n",
    "\n",
    "    cws = [\n",
    "        cw_skel.format('', fmt_set(pset), 'included_products')\n",
    "        ,cw_skel.format('NOT', fmt_set(pset), 'excluded_products')\n",
    "        ,'SUM(value) AS 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": 14,
   "id": "8d5dae11-4c41-4620-8b5c-cd6e06274367",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SUM(CASE WHEN product_name IN ('PRODUCT_0', 'PRODUCT_4') THEN fs.value ELSE 0 END) AS \"group_1\"\n",
      "        ,SUM(CASE WHEN product_name IN ('PRODUCT_1') THEN fs.value ELSE 0 END) AS \"group_2\"\n",
      "        ,SUM(CASE WHEN product_name IN ('PRODUCT_3', 'PRODUCT_5', 'PRODUCT_9') THEN fs.value ELSE 0 END) AS \"group_3\"\n",
      "        ,SUM(CASE WHEN product_name  IN ('PRODUCT_0', 'PRODUCT_1', 'PRODUCT_3', 'PRODUCT_4', 'PRODUCT_5', 'PRODUCT_9') THEN fs.value ELSE 0 END) AS \"included_products\"\n",
      "        ,SUM(CASE WHEN product_name NOT IN ('PRODUCT_0', 'PRODUCT_1', 'PRODUCT_3', 'PRODUCT_4', 'PRODUCT_5', 'PRODUCT_9') THEN fs.value ELSE 0 END) AS \"excluded_products\"\n",
      "        ,SUM(value) AS all_products\n"
     ]
    }
   ],
   "source": [
    "def fstr_cw_stms(product_groups: dict) -> str:\n",
    "    grps = cw_def_groups(product_groups)\n",
    "    misc = cw_undef_groups(product_groups)\n",
    "\n",
    "    cws = '\\n        ,'.join(grps + misc)\n",
    "    return cws\n",
    "\n",
    "stm_cws = fstr_cw_stms(PRODUCT_GROUPS)\n",
    "print(stm_cws)"
   ]
  },
  {
   "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": 15,
   "id": "960f779b-fcaf-4d27-8ec7-3a3963b3b671",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT\n",
      "        fs.date_id\n",
      "        ,fs.store_id\n",
      "        ,fs.unit_id\n",
      "        ,fs.date_value\n",
      "        ,fs.store_name\n",
      "        ,fs.retailer_name\n",
      "        ,fs.segment_name\n",
      "        ,fs.unit_name\n",
      "        ,SUM(CASE WHEN product_name IN ('PRODUCT_0', 'PRODUCT_4') THEN fs.value ELSE 0 END) AS \"group_1\"\n",
      "        ,SUM(CASE WHEN product_name IN ('PRODUCT_1') THEN fs.value ELSE 0 END) AS \"group_2\"\n",
      "        ,SUM(CASE WHEN product_name IN ('PRODUCT_3', 'PRODUCT_5', 'PRODUCT_9') THEN fs.value ELSE 0 END) AS \"group_3\"\n",
      "        ,SUM(CASE WHEN product_name  IN ('PRODUCT_0', 'PRODUCT_1', 'PRODUCT_3', 'PRODUCT_4', 'PRODUCT_5', 'PRODUCT_9') THEN fs.value ELSE 0 END) AS \"included_products\"\n",
      "        ,SUM(CASE WHEN product_name NOT IN ('PRODUCT_0', 'PRODUCT_1', 'PRODUCT_3', 'PRODUCT_4', 'PRODUCT_5', 'PRODUCT_9') THEN fs.value ELSE 0 END) AS \"excluded_products\"\n",
      "        ,SUM(value) AS all_products\n",
      "    FROM (\n",
      "    \n",
      "    SELECT\n",
      "        fs.fact_id\n",
      "        ,fs.created_at\n",
      "        ,MAX(fs.created_at) OVER (PARTITION BY fs.date_id, fs.store_id, fs.product_id, fs.unit_id) AS maxfact\n",
      "        ,fs.date_id\n",
      "        ,fs.store_id\n",
      "        ,fs.product_id\n",
      "        ,fs.unit_id\n",
      "        ,fs.value\n",
      "        ,dd.date_value\n",
      "        ,ds.store_name\n",
      "        ,ds.retailer_name\n",
      "        ,ds.segment_name\n",
      "        ,dp.product_name\n",
      "        ,du.unit_name\n",
      "    FROM\n",
      "        fact_sale fs\n",
      "    JOIN (\n",
      "    \n",
      "    SELECT\n",
      "        date_id\n",
      "        ,date_value\n",
      "    FROM\n",
      "        dim_date\n",
      "    WHERE\n",
      "        date_value BETWEEN 'WEEK_00' AND 'WEEK_51'\n",
      "    OR\n",
      "        date_value BETWEEN 'WEEK_52' AND 'WEEK_57'\n",
      "    \n",
      "        ) dd\n",
      "        ON dd.date_id = fs.date_id\n",
      "    JOIN (\n",
      "    \n",
      "    SELECT\n",
      "        ds.store_id\n",
      "        ,ds.store_name\n",
      "        ,dseg.segment_name\n",
      "        ,dr.retailer_id\n",
      "        ,dr.retailer_name\n",
      "    FROM\n",
      "        fact_segmentation fseg\n",
      "    JOIN\n",
      "        dim_segment dseg\n",
      "        ON dseg.segment_id = fseg.segment_id\n",
      "    JOIN\n",
      "        dim_store ds\n",
      "        ON ds.store_id = fseg.store_id\n",
      "    JOIN\n",
      "        dim_retailer dr\n",
      "        ON dr.retailer_id = ds.retailer_id\n",
      "    WHERE\n",
      "        fseg.analysis_id IN (3)\n",
      "    \n",
      "        ) ds\n",
      "        ON ds.store_id = fs.store_id\n",
      "    JOIN (\n",
      "    \n",
      "    SELECT\n",
      "        product_id\n",
      "        ,product_name\n",
      "    FROM\n",
      "        dim_product\n",
      "    \n",
      "        ) dp\n",
      "        ON dp.product_id = fs.product_id\n",
      "    JOIN (\n",
      "    \n",
      "    SELECT\n",
      "        unit_id\n",
      "        ,unit_name\n",
      "    FROM\n",
      "        dim_unit\n",
      "    \n",
      "        ) du\n",
      "        ON du.unit_id = fs.unit_id\n",
      "    \n",
      "        ) fs\n",
      "    WHERE\n",
      "        fs.maxfact = fs.created_at\n",
      "    GROUP BY\n",
      "        fs.date_id\n",
      "        ,fs.store_id\n",
      "        ,fs.unit_id\n",
      "        ,fs.date_value\n",
      "        ,fs.store_name\n",
      "        ,fs.retailer_name\n",
      "        ,fs.segment_name\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "def sales_data_query(\n",
    "    analysis_ids: list\n",
    "    ,product_groups: dict\n",
    "    ,control_period: iter\n",
    "    ,test_period: iter\n",
    ") -> str:\n",
    "    fact_sale = fstr_fact_sale(analysis_ids, control_period, test_period)\n",
    "    cw_stms = fstr_cw_stms(product_groups)\n",
    "    gbcols = \"\"\"fs.date_id\n",
    "        ,fs.store_id\n",
    "        ,fs.unit_id\n",
    "        ,fs.date_value\n",
    "        ,fs.store_name\n",
    "        ,fs.retailer_name\n",
    "        ,fs.segment_name\"\"\"\n",
    "\n",
    "    sql = f\"\"\"\n",
    "    SELECT\n",
    "        {gbcols}\n",
    "        ,fs.unit_name\n",
    "        ,{cw_stms}\n",
    "    FROM (\n",
    "    {fact_sale}\n",
    "        ) fs\n",
    "    WHERE\n",
    "        fs.maxfact = fs.created_at\n",
    "    GROUP BY\n",
    "        {gbcols}\n",
    "    \"\"\"\n",
    "    return sql\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",
    ")\n",
    "print(sdq)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4dbfd31-bd9e-4e7d-8c19-0b3c1d156c68",
   "metadata": {},
   "source": [
    "And, finally, running it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f0caa165-3c6a-4740-bbdc-1d9150e32811",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(66336, 14)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales = read_sql(sdq)\n",
    "\n",
    "sales.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>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>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>9.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>27.00</td>\n",
       "      <td>21.00</td>\n",
       "      <td>48.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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>2.98</td>\n",
       "      <td>9.06</td>\n",
       "      <td>4.08</td>\n",
       "      <td>16.12</td>\n",
       "      <td>39.84</td>\n",
       "      <td>55.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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>6.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>14.00</td>\n",
       "      <td>13.00</td>\n",
       "      <td>27.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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>2.52</td>\n",
       "      <td>6.04</td>\n",
       "      <td>3.39</td>\n",
       "      <td>11.95</td>\n",
       "      <td>24.52</td>\n",
       "      <td>36.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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>1.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>13.00</td>\n",
       "      <td>21.00</td>\n",
       "      <td>21.00</td>\n",
       "      <td>42.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   date_id  store_id  unit_id date_value store_name retailer_name  \\\n",
       "0        1      1123        1    WEEK_00        000    RETAILER_2   \n",
       "1        1      1123        2    WEEK_00        000    RETAILER_2   \n",
       "2        1      1124        1    WEEK_00        001    RETAILER_2   \n",
       "3        1      1124        2    WEEK_00        001    RETAILER_2   \n",
       "4        1      1125        1    WEEK_00        002    RETAILER_2   \n",
       "\n",
       "  segment_name    unit_name  group_1  group_2  group_3  included_products  \\\n",
       "0            T  SALES_UNITS     9.00     6.00    12.00              27.00   \n",
       "1            T    SALES_USD     2.98     9.06     4.08              16.12   \n",
       "2            C  SALES_UNITS     6.00     4.00     4.00              14.00   \n",
       "3            C    SALES_USD     2.52     6.04     3.39              11.95   \n",
       "4            C  SALES_UNITS     1.00     7.00    13.00              21.00   \n",
       "\n",
       "   excluded_products  all_products  \n",
       "0              21.00         48.00  \n",
       "1              39.84         55.96  \n",
       "2              13.00         27.00  \n",
       "3              24.52         36.47  \n",
       "4              21.00         42.00  "
      ]
     },
     "execution_count": 17,
     "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": "7c24e274-b075-421b-9490-2edaef6aa4f9",
   "metadata": {},
   "source": [
    "### All Functions, Together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a548a264-df46-4b2f-8ee0-92493241da42",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fstr_dim_date(control_period, test_period):\n",
    "    sql = \"\"\"\n",
    "    SELECT\n",
    "        date_id\n",
    "        ,date_value\n",
    "    FROM\n",
    "        dim_date\n",
    "    WHERE\n",
    "        date_value BETWEEN '{}' AND '{}'\n",
    "    OR\n",
    "        date_value BETWEEN '{}' AND '{}'\n",
    "    \"\"\".format(*control_period, *test_period)\n",
    "    return sql\n",
    "\n",
    "\n",
    "def fstr_dim_store(analysis_ids: list):\n",
    "    r\"filter dim_stores using an analysis store segmentation\"\n",
    "    id_lst = ', '.join(str(e) for e in analysis_ids)\n",
    "\n",
    "    sql = f\"\"\"\n",
    "    SELECT\n",
    "        ds.store_id\n",
    "        ,ds.store_name\n",
    "        ,dseg.segment_name\n",
    "        ,dr.retailer_id\n",
    "        ,dr.retailer_name\n",
    "    FROM\n",
    "        fact_segmentation fseg\n",
    "    JOIN\n",
    "        dim_segment dseg\n",
    "        ON dseg.segment_id = fseg.segment_id\n",
    "    JOIN\n",
    "        dim_store ds\n",
    "        ON ds.store_id = fseg.store_id\n",
    "    JOIN\n",
    "        dim_retailer dr\n",
    "        ON dr.retailer_id = ds.retailer_id\n",
    "    WHERE\n",
    "        fseg.analysis_id IN ({id_lst})\n",
    "    \"\"\"\n",
    "    return sql\n",
    "\n",
    "\n",
    "def fstr_dim_product():\n",
    "    sql = \"\"\"\n",
    "    SELECT\n",
    "        product_id\n",
    "        ,product_name\n",
    "    FROM\n",
    "        dim_product\n",
    "    \"\"\"\n",
    "    return sql\n",
    "\n",
    "\n",
    "def fstr_dim_unit():\n",
    "    sql = \"\"\"\n",
    "    SELECT\n",
    "        unit_id\n",
    "        ,unit_name\n",
    "    FROM\n",
    "        dim_unit\n",
    "    \"\"\"\n",
    "    return sql\n",
    "\n",
    "\n",
    "def fstr_fact_sale(analysis_ids: list, control_period:iter, test_period: iter) -> str:\n",
    "    dim_date = fstr_dim_date(control_period, test_period)\n",
    "    dim_store = fstr_dim_store(analysis_ids)\n",
    "    dim_product = fstr_dim_product()\n",
    "    dim_unit = fstr_dim_unit()\n",
    "\n",
    "    sql = f\"\"\"\n",
    "    SELECT\n",
    "        fs.fact_id\n",
    "        ,fs.created_at\n",
    "        ,MAX(fs.created_at) OVER (PARTITION BY fs.date_id, fs.store_id, fs.product_id, fs.unit_id) AS maxfact\n",
    "        ,fs.date_id\n",
    "        ,fs.store_id\n",
    "        ,fs.product_id\n",
    "        ,fs.unit_id\n",
    "        ,fs.value\n",
    "        ,dd.date_value\n",
    "        ,ds.store_name\n",
    "        ,ds.retailer_name\n",
    "        ,ds.segment_name\n",
    "        ,dp.product_name\n",
    "        ,du.unit_name\n",
    "    FROM\n",
    "        fact_sale fs\n",
    "    JOIN (\n",
    "    {dim_date}\n",
    "        ) dd\n",
    "        ON dd.date_id = fs.date_id\n",
    "    JOIN (\n",
    "    {dim_store}\n",
    "        ) ds\n",
    "        ON ds.store_id = fs.store_id\n",
    "    JOIN (\n",
    "    {dim_product}\n",
    "        ) dp\n",
    "        ON dp.product_id = fs.product_id\n",
    "    JOIN (\n",
    "    {dim_unit}\n",
    "        ) du\n",
    "        ON du.unit_id = fs.unit_id\n",
    "    \"\"\"\n",
    "    return sql\n",
    "\n",
    "\n",
    "def cw_def_groups(product_groups: dict) -> list:\n",
    "    def fmt_set(s) -> str:\n",
    "        return \"'\" + \"', '\".join(str(e) for e in s) + \"'\"\n",
    "\n",
    "    cw_skel = 'SUM(CASE WHEN product_name IN ({}) THEN fs.value ELSE 0 END) AS \"{}\"'\n",
    "    return [cw_skel.format(fmt_set(v), k) for k, v in product_groups.items()]\n",
    "\n",
    "\n",
    "def cw_undef_groups(product_groups: dict) -> list:\n",
    "    def fmt_set(s) -> str:\n",
    "        return \"'\" + \"', '\".join(sorted(str(e) for e in s)) + \"'\"\n",
    "\n",
    "    cw_skel = 'SUM(CASE WHEN product_name {} IN ({}) THEN fs.value ELSE 0 END) AS \"{}\"'\n",
    "\n",
    "    pset = set()\n",
    "    for pg in product_groups.values():\n",
    "        pset = pset.union(set(pg))\n",
    "\n",
    "    cws = [\n",
    "        cw_skel.format('', fmt_set(pset), 'included_products')\n",
    "        ,cw_skel.format('NOT', fmt_set(pset), 'excluded_products')\n",
    "        ,'SUM(value) AS all_products'\n",
    "    ]\n",
    "    return cws\n",
    "\n",
    "\n",
    "def fstr_cw_stms(product_groups: dict) -> str:\n",
    "    grps = cw_def_groups(product_groups)\n",
    "    misc = cw_undef_groups(product_groups)\n",
    "\n",
    "    cws = '\\n        ,'.join(grps + misc)\n",
    "    return cws\n",
    "\n",
    "\n",
    "def sales_data_query(\n",
    "    analysis_ids: list\n",
    "    ,product_groups: dict\n",
    "    ,control_period: iter\n",
    "    ,test_period: iter\n",
    ") -> str:\n",
    "    fact_sale = fstr_fact_sale(analysis_ids, control_period, test_period)\n",
    "    cw_stms = fstr_cw_stms(product_groups)\n",
    "    gbcols = \"\"\"fs.date_id\n",
    "        ,fs.store_id\n",
    "        ,fs.unit_id\n",
    "        ,fs.date_value\n",
    "        ,fs.store_name\n",
    "        ,fs.retailer_name\n",
    "        ,fs.segment_name\"\"\"\n",
    "\n",
    "    sql = f\"\"\"\n",
    "    SELECT\n",
    "        {gbcols}\n",
    "        ,fs.unit_name\n",
    "        ,{cw_stms}\n",
    "    FROM (\n",
    "    {fact_sale}\n",
    "        ) fs\n",
    "    WHERE\n",
    "        fs.maxfact = fs.created_at\n",
    "    GROUP BY\n",
    "        {gbcols}\n",
    "    \"\"\"\n",
    "    return sql"
   ]
  }
 ],
 "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
}