UseCases/Insurance_Policy_Temporal/Insurance_Policy_Temporal_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8d8a3c4f",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Insurance Analysis Using Temporal\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20ade183-2b03-46eb-a626-c0bc75dcd47a",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Applications require to design and build databases dependent on time or data changes over time.  Building such applications is possible but becomes easier with Temporal\n",
    "   \n",
    "</p>  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c8f88a7-163b-4ee7-8c5d-299f1492e304",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>WHY TEMPORAL? </b> </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "Things You No Longer Have To Do in the Time-Aware Teradata Database\n",
    "Data Maintenance : </p>\n",
    "    \n",
    "<li style = 'font-size:16px;font-family:Arial'> Update effective dates in data.</li></<li style = 'font-size:16px;font-family:Arial'> <li style = 'font-size:16px;font-family:Arial'> Specify “transaction time” timestamp when data are entered or updated. </li>\n",
    "<li style = 'font-size:16px;font-family:Arial'> Insert new data rows for updates. </li>     \n",
    "<li style = 'font-size:16px;font-family:Arial'> Manage temporal history storage space. </li> \n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05080528-4583-4180-8f6b-7a79a797237d",
   "metadata": {
    "tags": []
   },
   "source": [
    " \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Query and Application Development: </p>\n",
    "\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> Write complex period based condition clauses. </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> Add effective date qualifications to “current queries.” </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> Train users and application developers in effective date columns and processing for “AS OF” query development.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> Save old reports or database snapshots to facilitate looking back for analysis, compliance reporting, or audit trails.</li>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64ff501a-5f7d-4b31-a8db-72511554d2f8",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we are considering the example of Insurance company storing data for various policies whose validity, conditions , premium amount and many other factors will change over Time period.</p>\n",
    "    \n",
    "    \n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c30a145c-a2e4-43d2-8ae0-1a5378add8fb",
   "metadata": {
    "tags": []
   },
   "source": [
    "   \n",
    "    \n",
    "    \n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Some scenarios that can be handled using temporal features are as below </p>\n",
    "    \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>    Consider the scenario where there is a hail storm striking on a particular date. The As of date feature can be used to check the validity of the policy and then the incident date can be used to check which claims fall in the validity period of the policies.</li>\n",
    "    \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>    Another scenario is that the customer has sold the vehicle and the closure of the policy happened a week late as the customer informed late about the sale , so based on the transaction time the validity of the policies will change. </li>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "976d48f0",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press Enter, then use down arrow to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2df43a2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=true"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06f1c8a5-20cb-4078-b923-51514359670f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Setup for execution of notebook. Begin running steps with Shift + Enter keys.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2704c5b-654e-419a-9690-661df1240ca3",
   "metadata": {},
   "outputs": [],
   "source": [
    "SET query_band='DEMO=Insurance_Policy_Temporal_SQL.ipynb;' UPDATE FOR SESSION;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "367e2ed6-7169-4ef9-91a4-dbbdfd8a157c",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We have provided data for this demo on cloud storage. In this demo since we are using Temporal table we will be creating databases and tables in local storage and use them in the notebook. Please execute the procedure in the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a983e788-b2df-4ee3-8689-87c01fc34bc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "call get_data('DEMO_Insurance_local');    -- Takes about 90 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0153c011-d5af-4c56-8e45-3636bf3ab623",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>Optional step – if you want to see status of databases/tables created and space used.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdcdbbb1-d674-4d49-923c-f6a2faabc141",
   "metadata": {},
   "outputs": [],
   "source": [
    "call space_report();  -- optional, takes about 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00960a68-6ba8-4d3d-8bbc-b6247390508b",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Comparison of Temporal and Non-Temporal:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To understand how Temporal can be helpful we will consider a simple example where we need to check policies which were valid on 31st December 2020 and had a premium amount less than 1000. for a normal table with start and end date columns the query will be as below:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea655210-c846-49f4-8f1d-4142e395114f",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    Policy_ID,\n",
    "    Customer_ID,\n",
    "    Policy_premium_Amt,\n",
    "    policy_state,\n",
    "    Policy_start_date,\n",
    "    policy_end_date\n",
    "FROM\n",
    "    DEMO_Insurance.Policy_Details_Org\n",
    "WHERE\n",
    "    policy_start_date <= '2020-12-31'\n",
    "    AND policy_end_date >= '2020-12-31'\n",
    "    AND Policy_premium_Amt < 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b13b2f78-924a-4d88-94b5-a2ceb22229fb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The same query with Temporal table would be as below:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7edd317e-30a6-49f7-a9e0-b21e4b4cc79c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "VALIDTIME AS OF DATE '2020-12-31'\n",
    "SELECT\n",
    "    Policy_ID,\n",
    "    Customer_ID,\n",
    "    Policy_premium_Amt,\n",
    "    policy_state,\n",
    "    Policy_Validity\n",
    "FROM\n",
    "    DEMO_Insurance.Policy_Details\n",
    "WHERE\n",
    "    Policy_premium_Amt < 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab1e21c8-31bf-427e-81eb-798b50dedea6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Now let's check how temporal can help simplify queries in a complex scenario:</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "487a2a68",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Consider scenario where we have to check all valid policies whose validity period is similar to the Policy of the Mercedes -ML350 in the state IL. In a table without the temporal columns the query would have to collect data for various scenarios where the policy_start_date and policy_end_date for other policies lie in the range of the policy_start_date and policy_end_date of the policy of Mercedes-ML350 in state of IL. So, the query will have to do a UNION ALL with different date checks for checking the start and end date of the policies looks like the below: </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "542c4b8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    OthPol.Policy_id,\n",
    "    OthPol.Customer_id,\n",
    "    OthPol.auto_make,\n",
    "    OthPol.Policy_start_date,\n",
    "    OthPol.Policy_end_date\n",
    "FROM\n",
    "    DEMO_Insurance.policy_details_org MercPol\n",
    "JOIN\n",
    "    DEMO_Insurance.policy_details_org OthPol\n",
    "ON\n",
    "    MercPol.auto_make = 'Mercedes'\n",
    "    AND MercPol.policy_state = 'IL'\n",
    "    AND MercPol.auto_model = 'ML350'\n",
    "    AND MercPol.policy_state = OthPol.Policy_state\n",
    "    AND MercPol.Policy_start_date <= OthPol.Policy_start_date\n",
    "    AND MercPol.Policy_end_date > OthPol.Policy_start_date\n",
    "    AND MercPol.Policy_end_date <= OthPol.Policy_end_date\n",
    "UNION ALL\n",
    "SELECT\n",
    "    OthPol.Policy_id,\n",
    "    OthPol.Customer_id,\n",
    "    OthPol.auto_make,\n",
    "    OthPol.Policy_start_date,\n",
    "    OthPol.Policy_end_date\n",
    "FROM\n",
    "    DEMO_Insurance.policy_details_org MercPol\n",
    "JOIN\n",
    "    DEMO_Insurance.policy_details_org OthPol\n",
    "ON\n",
    "    MercPol.auto_make = 'Mercedes'\n",
    "    AND MercPol.policy_state = 'IL'\n",
    "    AND MercPol.auto_model = 'ML350'\n",
    "    AND MercPol.policy_state = OthPol.Policy_state\n",
    "    AND MercPol.Policy_start_date <= OthPol.Policy_start_date\n",
    "    AND MercPol.Policy_end_date >= OthPol.Policy_end_date\n",
    "UNION ALL\n",
    "SELECT\n",
    "    OthPol.Policy_id,\n",
    "    OthPol.Customer_id,\n",
    "    OthPol.auto_make,\n",
    "    OthPol.Policy_start_date,\n",
    "    OthPol.Policy_end_date\n",
    "FROM\n",
    "    DEMO_Insurance.policy_details_org MercPol\n",
    "JOIN\n",
    "    DEMO_Insurance.policy_details_org OthPol\n",
    "ON\n",
    "    MercPol.auto_make = 'Mercedes'\n",
    "    AND MercPol.policy_state = 'IL'\n",
    "    AND MercPol.auto_model = 'ML350'\n",
    "    AND MercPol.policy_state = OthPol.Policy_state\n",
    "    AND MercPol.Policy_start_date >= OthPol.Policy_start_date\n",
    "    AND MercPol.Policy_end_date <= OthPol.Policy_end_date\n",
    "UNION ALL\n",
    "SELECT\n",
    "    OthPol.Policy_id,\n",
    "    OthPol.Customer_id,\n",
    "    OthPol.auto_make,\n",
    "    OthPol.Policy_start_date,\n",
    "    OthPol.Policy_end_date\n",
    "FROM\n",
    "    DEMO_Insurance.policy_details_org MercPol\n",
    "JOIN\n",
    "    DEMO_Insurance.policy_details_org OthPol\n",
    "ON\n",
    "    MercPol.auto_make = 'Mercedes'\n",
    "    AND MercPol.policy_state = 'IL'\n",
    "    AND MercPol.auto_model = 'ML350'\n",
    "    AND MercPol.policy_state = OthPol.Policy_state\n",
    "    AND MercPol.Policy_start_date >= OthPol.Policy_start_date\n",
    "    AND MercPol.Policy_start_date < OthPol.Policy_end_date\n",
    "    AND MercPol.Policy_end_date >= OthPol.Policy_end_date;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22fda8e8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Use the Temporal ValidTime function to display similar results as the query written above on table without Period datatype. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c80ccbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "VALIDTIME SELECT\n",
    "    OthPol.Policy_id,\n",
    "    OthPol.Customer_id,\n",
    "    OthPol.auto_make\n",
    "FROM\n",
    "    DEMO_Insurance.policy_details MercPol\n",
    "JOIN\n",
    "    DEMO_Insurance.policy_details OthPol\n",
    "ON\n",
    "    MercPol.auto_make = 'Mercedes'\n",
    "    AND MercPol.policy_state = 'IL'\n",
    "    AND MercPol.auto_model = 'ML350'\n",
    "    AND MercPol.policy_state = OthPol.Policy_state;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "289cccbb",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Comparison for Queries with and without Temporal </b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "144bce39",
   "metadata": {},
   "source": [
    "<table>\n",
    "<tr>\n",
    "<td style = 'font-size:18px;font-family:Arial;color:#00233C;font-weight:bold'>WITH TEMPORAL</td>\n",
    "<td style = 'font-size:16px;font-family:Arial;color:#00233C;font-weight:bold'>WITHOUT TEMPORAL</td> \n",
    "</tr>\n",
    "\n",
    "<tr>\n",
    "<td style = 'font-size:16px;font-family:Arial;color:#00233C'>Validtime select b.Policy_id, b.Customer_id, b.auto_make from policy_details a , policy_details b where a.auto_make = 'Mercedes' and a.policy_state = 'IL' and a.auto_model = 'ML350' and a.policy_state = b.Policy_state; </td>\n",
    "<td style = 'font-size:16px;font-family:Arial;color:#00233C'>select b.Policy_id, b.Customer_id, b.auto_make , b.Policy_start_date,b.Policy_end_date from policy_details_org_extrapolate a , policy_details_org_extrapolate b where a.auto_make = 'Mercedes' and a.policy_state = 'IL' and a.auto_model = 'ML350' and a.policy_state = b.Policy_state AND a.policy_start_date b.policy_start_date AND a.policy_end_date    UNION ALL \n",
    "select b.Policy_id, b.Customer_id, b.auto_make , b.Policy_start_date,b.Policy_end_date from policy_details_org_extrapolate a , policy_details_org_extrapolate b where a.auto_make = 'Mercedes' and a.policy_state = 'IL' and a.auto_model = 'ML350' and a.policy_state = b.Policy_state AND a.policy_start_date <= b.policy_start_date AND a.policy_end_date >= b.policy_end_date\n",
    "\n",
    "   UNION ALL \n",
    "select b.Policy_id, b.Customer_id, b.auto_make , b.Policy_start_date,b.Policy_end_date from policy_details_org_extrapolate a , policy_details_org_extrapolate b where a.auto_make = 'Mercedes' and a.policy_state = 'IL' and a.auto_model = 'ML350' and a.policy_state = b.Policy_state AND a.policy_start_date >= b.policy_start_date AND a.policy_end_date <= b.policy_end_date\n",
    "\n",
    "   UNION ALL\n",
    "select b.Policy_id, b.Customer_id, b.auto_make , b.Policy_start_date,b.Policy_end_date from policy_details_org_extrapolate a , policy_details_org_extrapolate b where a.auto_make = 'Mercedes' and a.policy_state = 'IL' and a.auto_model = 'ML350' and a.policy_state = b.Policy_state AND a.policy_start_date >= b.policy_start_date AND a.policy_start_date < b.policy_end_date AND a.policy_end_date >= b.policy_end_date;</td>\n",
    "</tr>    \n",
    "</table>    \n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1a5895d-1405-4e01-aa8c-f1741336deb0",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Performance Comparison for above queries </b> </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0871d591-10e4-4fa5-81e2-22ec63370e70",
   "metadata": {},
   "source": [
    "<img src=\"images/PerformanceMetrics.png\" style=\"width:1000px;height:200px;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11fb1568-11a6-4634-ab34-6cb08df6d713",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Temporal concepts:- Aggregations </b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Comparing the Validity and VALIDTIME columns demonstrates that the   \n",
    "    valid-time period of the result set is the intersection of the valid-time period in the original Policy table Validity \n",
    "    column with the Period specified in the sequenced select statement.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe6dd1c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'\n",
    "SELECT\n",
    "    Policy_ID,\n",
    "    Customer_ID,\n",
    "    Policy_Validity\n",
    "FROM\n",
    "    (\n",
    "        SEQUENCED VALIDTIME SELECT\n",
    "            Policy_details.*\n",
    "        FROM\n",
    "            DEMO_Insurance.Policy_Details\n",
    "        WHERE\n",
    "            policy_state = 'IN'\n",
    "    ) AS my_derived_table;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbc9a34e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Aggregations can be used for analysis of the data based on the validity period. \n",
    "Comparisons of data between different validity periods can be achieved with Temporal. Like the minimum and maximum premium amounts for a particular state based on the model of the cars.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cd01c92",
   "metadata": {},
   "outputs": [],
   "source": [
    "SEQUENCED VALIDTIME PERIOD (DATE '2011-01-01', DATE '2020-12-31')\n",
    "SELECT\n",
    "    auto_make,\n",
    "    MIN(Policy_premium_Amt) AS MinPremium,\n",
    "    MAX(Policy_premium_Amt) AS MaxPremium\n",
    "FROM\n",
    "    DEMO_Insurance.Policy_Details\n",
    "WHERE\n",
    "    policy_state = 'OH'\n",
    "GROUP BY 1\n",
    "ORDER BY VALIDTIME;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4ab68b0-0ae8-43ae-ad0c-d0b24b2937a9",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Temporal concepts:- Validtime usage </b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ae3a600-8b98-4317-9164-0f2a9a9e2a8e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> <b> Time line for various Policies:-    Analysis can be done for </b> </p>\n",
    "    \n",
    "   \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Policies valid during a particular period.</li>\n",
    "    \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Validity period of various policies.</li>\n",
    "    \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Premium amount collected during a particular period </li>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78ed0686",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using the As Of concept to verify the active policies on a particular date</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52301a5c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>On a given date, there was a hailstorm and we want to determine the count of policies in effect on that date\n",
    "The scenario mentioned here can be solved by the below query using the As of Date feature of Temporal tables   \n",
    " </p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82ecf26e",
   "metadata": {},
   "source": [
    "<img src=\"images/TemporalTimePeriod.png\" style=\"width:600px;height:400px;\">\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "278b3401",
   "metadata": {},
   "outputs": [],
   "source": [
    "VALIDTIME AS OF DATE '2022-02-15'\n",
    "SELECT\n",
    "    Policy_ID,\n",
    "    Customer_ID,\n",
    "    Policy_premium_Amt,\n",
    "    Policy_Validity\n",
    "FROM\n",
    "    DEMO_Insurance.Policy_Details\n",
    "WHERE\n",
    "    policy_state = 'IL';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e3a7d2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    COUNT(*) AS \"no of Policies\"\n",
    "FROM\n",
    "    (\n",
    "        VALIDTIME AS OF DATE '2022-02-15'\n",
    "        SELECT\n",
    "            Policy_ID,\n",
    "            Customer_ID,\n",
    "            Policy_premium_Amt,\n",
    "            Policy_Validity\n",
    "        FROM\n",
    "            DEMO_Insurance.Policy_Details\n",
    "        WHERE\n",
    "            policy_state = 'IL'\n",
    "    ) AS DerivTbl;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f89ee3f1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Consider scenario where we need to check that for a particular period (here for year 2021) how many policies are valid for each auto make type and then compare the no of policies which are valid in the current period. This helps us in the comparison in the increase or decrease of policies for the current year as compared to the past year of validity\n",
    " </p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc7a2394-35e6-4679-acf9-9e9d883fa1e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    auto_make,\n",
    "    COUNT(*) AS \"no of Policies\"\n",
    "FROM\n",
    "    (\n",
    "        VALIDTIME PERIOD (DATE '2021-01-01', DATE '2021-12-31')\n",
    "        SELECT\n",
    "            Policy_ID,\n",
    "            Customer_ID,\n",
    "            Policy_premium_Amt,\n",
    "            auto_make\n",
    "        FROM\n",
    "            DEMO_Insurance.Policy_Details\n",
    "    ) AS DerivTbl\n",
    "GROUP BY\n",
    "    auto_make;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74bb6c4d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%chart x = auto_make, y = \"no of Policies\", title = 'Valid Number of Policies last year'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6a9c581",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>Now we check how many policies are still valid for each type using the current functionality\n",
    " </p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c41f0521",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    auto_make,\n",
    "    COUNT(*) AS \"no of Policies\"\n",
    "FROM\n",
    "    (\n",
    "        CURRENT VALIDTIME\n",
    "        SELECT\n",
    "            Policy_ID,\n",
    "            Customer_ID,\n",
    "            Policy_premium_Amt,\n",
    "            auto_make\n",
    "        FROM\n",
    "            DEMO_Insurance.Policy_Details\n",
    "    ) AS DerivTbl\n",
    "GROUP BY\n",
    "    auto_make;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ea15877",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%chart x = auto_make, y = \"no of Policies\", title = 'Current Valid Number of Policies'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "553a1791",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this scenario we are considering that there are a number of claims raised against policies on various incident dates. Below queries help to identify which claims made are in the validity period and which are not and which policies are valid at the time of the incidents. There are total 69 claims in the Insurance Claims tables and based on the incident date we can verify which claims are valid and which are not valid.\n",
    " </p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1740309f",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM DEMO_Insurance.Insurance_Claims_Details;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1401557",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Based on the Claims made, we need to validate which claims are valid on the incident date mentioned. Below query helps in the analysis. Only the rows that qualify for the claims are displayed.\n",
    " </p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8faa8894",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    'Valid Claims' AS ClaimType,\n",
    "    Policy.customer_id,\n",
    "    Policy.Policy_id,\n",
    "    Insurance_Claims_Details.Claim_Id,\n",
    "    Claim_amount\n",
    "FROM\n",
    "    (\n",
    "        CURRENT VALIDTIME\n",
    "        SELECT\n",
    "            Policy_Details.*\n",
    "        FROM\n",
    "            DEMO_Insurance.Policy_Details\n",
    "    ) Policy\n",
    "INNER JOIN\n",
    "    DEMO_Insurance.Insurance_Claims_Details\n",
    "ON\n",
    "    Policy.Policy_id = Insurance_Claims_Details.Policy_id\n",
    "WHERE\n",
    "    Policy.Policy_Validity CONTAINS Insurance_Claims_Details.Incident_date;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b834d7ae",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below query displays the Invalid rows from Claims table\n",
    " </p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53bebbde",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    customer_id,\n",
    "    Policy.Policy_id,\n",
    "    Claim_Id,\n",
    "    Claim_amount\n",
    "FROM\n",
    "    DEMO_Insurance.Insurance_Claims_Details\n",
    "INNER JOIN\n",
    "    DEMO_Insurance.Policy_Details Policy\n",
    "ON\n",
    "    Policy.Policy_id = Insurance_Claims_Details.Policy_id\n",
    "MINUS\n",
    "SELECT\n",
    "    Policy.customer_id,\n",
    "    Policy.Policy_id,\n",
    "    Insurance_Claims_Details.Claim_Id,\n",
    "    Claim_amount\n",
    "FROM\n",
    "    (\n",
    "        CURRENT VALIDTIME\n",
    "        SELECT\n",
    "            Policy_Details.*\n",
    "        FROM\n",
    "            DEMO_Insurance.Policy_Details\n",
    "    ) Policy\n",
    "INNER JOIN\n",
    "    DEMO_Insurance.Insurance_Claims_Details\n",
    "ON\n",
    "    Policy.Policy_id = Insurance_Claims_Details.Policy_id\n",
    "WHERE\n",
    "    Policy.Policy_Validity CONTAINS Insurance_Claims_Details.Incident_date;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "941148d3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Explaining the Temporal Concepts of Inserts /Deletes </b></p> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4aa9781d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customer 1 has policy 1 with Validity Period . In between the validity period the customer decides to change some factors which causes the premium to increase , so there will be a new addition which will insert a new row having validity period from changed date to the end of period. </p>\n",
    "    \n",
    "    \n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customer 2 has Policy 1 with validity period. In between the company decides to increase the premium which also increases the validity period.\n",
    " </p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a23e55f9",
   "metadata": {},
   "source": [
    "<img src=\"images/Temporal_Inserts_Updates.png\" style=\"width:600px;height:400px;\">\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e7ad022",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Inserting data into temporal table is simple for current validtime. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ac18d39",
   "metadata": {},
   "outputs": [],
   "source": [
    "CURRENT VALIDTIME INSERT DEMO_Insurance_DB.DEMO_Insurance_Policy_Det\n",
    "VALUES\n",
    "    (203914, 2316038, 'OH', 1200, 'Audi', 'A3');"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84aa1296",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Inserting history rows will need Temporal Qualifier. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "490a4845",
   "metadata": {},
   "outputs": [],
   "source": [
    "SEQUENCED VALIDTIME INSERT DEMO_Insurance_DB.DEMO_Insurance_Policy_Det\n",
    "VALUES\n",
    "    (203914, 2316038, 'OH', 1050, 'Audi', 'A3', PERIOD(DATE '2002-06-10', DATE '2012-06-09'));\n",
    "\n",
    "SEQUENCED VALIDTIME INSERT DEMO_Insurance_DB.DEMO_Insurance_Policy_Det\n",
    "VALUES\n",
    "    (203914, 2316038, 'OH', 1150, 'Audi', 'A3', PERIOD(DATE '2012-06-10', DATE '2022-06-27'));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9ec6562",
   "metadata": {},
   "outputs": [],
   "source": [
    "SEQUENCED VALIDTIME\n",
    "SELECT * \n",
    "FROM\n",
    "    DEMO_Insurance.Policy_Details \n",
    "WHERE\n",
    "    customer_id = 2316038 \n",
    "ORDER BY\n",
    "    validtime;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65306e68",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Without Temporal Qualifier the select will display only current rows. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdcded1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "SET SESSION CURRENT VALIDTIME;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f500e4d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT *\n",
    "FROM \n",
    "    DEMO_Insurance.Policy_Details\n",
    "WHERE\n",
    "    customer_id = 2316038\n",
    "ORDER BY\n",
    "    Policy_validity;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddd1d8eb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When period is specified , Delete will only delete rows for that specific period (highlighted row in the above dataset) and the validity period for rows will change to the end of period specified in the delete. </p> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a314c9ac",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Display rows for Specific Valid Period.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc6bfdaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "SEQUENCED VALIDTIME PERIOD '(1990-01-01, 1990-03-31)'\n",
    "SELECT * \n",
    "FROM\n",
    "    DEMO_Insurance.Policy_details\n",
    "WHERE\n",
    "    policy_state = 'IN'\n",
    "ORDER BY\n",
    "    validtime;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4db8cf2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Delete rows for Specific Valid Period will change the validity period to the end date specified in delete. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "913e20fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "SEQUENCED VALIDTIME PERIOD '(1990-01-01, 1990-01-30)'\n",
    "DELETE FROM\n",
    "    DEMO_Insurance_DB.DEMO_Insurance_Policy_Det\n",
    "WHERE\n",
    "    policy_state = 'IN';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3fa8957",
   "metadata": {},
   "outputs": [],
   "source": [
    "SEQUENCED VALIDTIME PERIOD '(1990-01-01, 1990-03-31)'\n",
    "SELECT * \n",
    "FROM\n",
    "    DEMO_Insurance_DB.DEMO_Insurance_Policy_Det\n",
    "WHERE\n",
    "    policy_state = 'IN'\n",
    "ORDER BY\n",
    "    validtime;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35607b87",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Bi-Temporal Table with ValidTime and TransactionTime Columns and its usage.</b> </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Bi-Temporal tables or Transaction time tables have both Valid Date(Date) Columns and Transaction Time (Timestamp) Columns. Transaction time is the time period during which a fact, represented by all the information in a row, is or was known to be in effect in the database. It models the database reality, recording when rows have been added, modified, and changed in the database. Transaction-time periods are stored in a transaction-time column:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The beginning of the transaction-time period is the time when the database became aware of a row, when the row was first recorded in the database. This is when the row was added to a table.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The end of a transaction time period reflects when the fact was superseded by an update to the row, or when the row was deleted from the database. Rows containing information that is currently in effect have transaction-time periods with indefinite ending bounds, represented as UNTIL_CLOSED.</li>\n",
    "</p>\n",
    "\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, temporal features can help in simplifying time dependent applications. Temporal and Bi-Temporal tables can be analyzed and used in various scenarios.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b062822-3093-4838-8697-fe9a6a6112c0",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Cleanup </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "759bfdf9-7f7a-4645-9ab8-9814854c53e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "call remove_data('DEMO_Insurance');    -- Takes about 10 seconds, optional if you want to use the data later"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea24ebfa-330d-4cd7-9c57-1689aa88523a",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Reference Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Information about Temporal datatype can be found at: <a href = 'https://docs.teradata.com/search/all?query=Tempora<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>able+Support&content-lang=en-US'>https://docs.teradata.com/search/all?query=Temporal+Table+Support&content-lang=en-US</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "feaba294-aa49-4d8e-94f9-3e5ad291f287",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Teradata SQL",
   "language": "Teradata SQL",
   "name": "teradatasql"
  },
  "language_info": {
   "codemirror_mode": "Teradata SQL",
   "file_extension": ".tdrs",
   "mimetype": "application/vnd.teradata.resultset",
   "name": "Teradata SQL",
   "nbconvert_exporter": "",
   "pygments_lexer": "",
   "version": "16.20"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}