{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "febd8d5d-ad65-4297-8b0b-627c42b52141",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas import DataFrame\n",
    "from DATA225utils import make_connection, dataframe_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a60d2bf-905c-42b7-a936-bdec31ac0177",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_warehouse = make_connection(config_file = 'zagi-warehouse.ini')\n",
    "cursor_warehouse = conn_warehouse.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1796e232-1a01-4fa2-ae16-56e8a0b64c8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_table(table, sql):\n",
    "    cursor_warehouse.execute(f\"DROP TABLE IF EXISTS {table}\")\n",
    "    cursor_warehouse.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b1b3919-d88c-46e8-8424-2ab0eb0c6586",
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_table(table, order_by=''):\n",
    "    sql = f\"SELECT * FROM {table}\"\n",
    "    \n",
    "    if order_by != '':\n",
    "        sql = sql + \" ORDER BY \" + order_by\n",
    "        \n",
    "    _, df = dataframe_query(conn_warehouse, sql)    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acfb9107-83e3-4759-a37e-5a356824dab2",
   "metadata": {},
   "source": [
    "## Aggregated Sales fact table per Day, Product, Customer, Store (DPCS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57a8530c-4054-4266-8793-632580a5f5c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        CREATE TABLE sales_per_dpcs\n",
    "        (\n",
    "            calendar_key INT,\n",
    "            store_key    INT,\n",
    "            product_key  INT,\n",
    "            customer_key INT,\n",
    "            dollars_sold DOUBLE,\n",
    "            units_sold   INT,\n",
    "            line_count   INT,\n",
    "            PRIMARY KEY(calendar_key, store_key, product_key, customer_key)\n",
    "        )\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "make_table('sales_per_dpcs', sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e87fb65e-55bf-431c-adce-0ec27416ff86",
   "metadata": {},
   "source": [
    "#### Total amount sold in dollars and units \n",
    "- #### on a particular day\n",
    "- #### for a particular product\n",
    "- #### for a particular customer\n",
    "- #### for a particular store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db5f75e4-dd2c-4410-8179-2340ebb514ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO sales_per_dpcs(calendar_key, store_key, \n",
    "                                   product_key, customer_key,\n",
    "                                   dollars_sold, units_sold,\n",
    "                                   line_count)\n",
    "            SELECT calendar_key, store_key, product_key, customer_key,\n",
    "                   SUM(dollars_sold), SUM(units_sold), COUNT(tid)\n",
    "            FROM sales\n",
    "            GROUP BY calendar_key, store_key, product_key, customer_key\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "cursor_warehouse.execute(sql)\n",
    "conn_warehouse.commit()\n",
    "\n",
    "display_table('sales_per_dpcs', \n",
    "              'calendar_key, store_key, product_key, customer_key')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a653431c-810b-4241-bbe9-13133e1eaf6d",
   "metadata": {},
   "source": [
    "## Aggregated Sales fact table per Day, Customer, Store (DCS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2fec6c1-5d76-437a-bf0d-d562ba109980",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        CREATE TABLE sales_per_dcs\n",
    "        (\n",
    "            calendar_key INT,\n",
    "            store_key    INT,\n",
    "            customer_key INT,\n",
    "            dollars_sold DOUBLE,\n",
    "            units_sold   INT,\n",
    "            line_count   INT,\n",
    "            PRIMARY KEY(calendar_key, store_key, customer_key)\n",
    "        )\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "make_table('sales_per_dcs', sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cc09266-ad7d-40a8-92d4-d610a7d8dd7c",
   "metadata": {},
   "source": [
    "#### Total amount sold in dollars and units \n",
    "- ### on a particular day\n",
    "- ### for a particular customer\n",
    "- ### for a particular store\n",
    "#### for all products."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd380351-9ddd-4b80-a348-dbc0834c0aa0",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO sales_per_dcs(calendar_key, store_key, customer_key,\n",
    "                                  dollars_sold, units_sold, line_count)\n",
    "            SELECT calendar_key, store_key, customer_key,\n",
    "                   SUM(dollars_sold), SUM(units_sold), COUNT(tid)\n",
    "            FROM sales\n",
    "            GROUP BY calendar_key, store_key, customer_key\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "cursor_warehouse.execute(sql)\n",
    "conn_warehouse.commit()\n",
    "\n",
    "display_table('sales_per_dcs', \n",
    "              'calendar_key, store_key, customer_key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2eff238b-88c8-49c2-93a5-fc05a6eb2ca1",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor_warehouse.close()\n",
    "conn_warehouse.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf643f48-df6a-4e44-b9e6-8502ea81c469",
   "metadata": {},
   "source": [
    "#### (c) Copyright 2023 by Ronald Mak"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4ad44bc-2895-4b22-9ec2-7e82c8dfca04",
   "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.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}