UseCases/4D_Analytics_on_bike_sharing/4D_Analytics_on_bike_sharing_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "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",
    "       Austin Bike Share\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>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Bike shares are becoming a popular alternative means of transportation. Suppose you had a transportation business servicing the public with various stations where they could access your transportation services. You must ensure you have equipment at the stations when the public needs them. You also know that the weather dramatically impacts the demand for your transportation services. This demonstration shows how to integrate historical trip information with weather information, leveraging Vantage Geospatial and time-series capabilities to improve your service and grow your business.\n",
    "<br>\n",
    "The City of Austin makes data available on >649k bike trips over 2013-2017.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Contents:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Explore the data</li>\n",
    "    <li>Create and Explore Temporal, Geospatial and Time index data</li>\n",
    "    <li>Insights</li>\n",
    "    <li>Clean up</li>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>1. Connect to Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=True"
   ]
  },
  {
   "cell_type": "markdown",
   "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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SET query_band='DEMO=AustinBikeShare.ipynb;' update for session;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>1.1 Getting Data for This Demo</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. You can either run the demo using foreign tables to access the data without any storage on your environment or download the data to local storage, which may yield faster execution. Still, there could be considerations of available storage. Two statements are in the following cell, and one of them is commented out. You may switch between the modes by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "-- call get_data('DEMO_AustinBikeShare_cloud');           -- Takes 20 seconds\n",
    "call get_data('DEMO_AustinBikeShare_local');           -- Takes 50 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "call space_report();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>2. Explore the data</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As a warm-up, let us look at the tables in our database TRNG_AustinBike.</p>       "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT \n",
    "    DatabaseName,\n",
    "    TableName\n",
    "FROM\n",
    "    DBC.Tables\n",
    "WHERE\n",
    "    DatabaseName = 'DEMO_AustinBikeShare'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that we have three tables in our database. The Trips table contains data on the trips taken using the bikes, the stations table has locations of the bike stations, and the weather table has details about the weather.\n",
    "    <br>\n",
    "    <br>\n",
    "The query below shows the number of rows in each of the tables in the database.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "(\n",
    "    SELECT COUNT(*)\n",
    "    FROM DEMO_AustinBikeShare.trips\n",
    ") AS trips,\n",
    "(\n",
    "    SELECT COUNT(*)\n",
    "    FROM DEMO_AustinBikeShare.stations\n",
    ") AS stations,\n",
    "(\n",
    "    SELECT COUNT(*)\n",
    "    FROM DEMO_AustinBikeShare.weather\n",
    ") AS weather;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.1 Examine the trips table</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at the sample data in the trips table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips\n",
    "SAMPLE 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Which type of subscribers take most of the rides?</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "select top 10 count(trip_id) as ride_count, subscriber_type from DEMO_AustinBikeShare.trips group by subscriber_type order by 1 desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above result we can say that <b>Walk Up</b> rides are <b>250%</b> more than second most popular subscription type.\n",
    "    <br><br>\n",
    "    From which station do highest number of trips start?</p>  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    TOP 20\n",
    "    start_station_name,\n",
    "    COUNT(trip_id) AS trips\n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart start_station_name, trips, title = \"Trips by station\", height = 200, width = 700"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that <b>Riverside @ S. Lamar</b> has the highest number of trips originating from here.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's see average number of trips originating per from a station.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT AVG(trips) FROM (\n",
    "    SELECT\n",
    "    start_station_name,\n",
    "    COUNT(1) AS trips\n",
    "    FROM\n",
    "        DEMO_AustinBikeShare.trips\n",
    "    GROUP BY 1\n",
    ") AS t;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that the top station <b>Riverside @ S. Lamar</b> has <b>4 times more trips</b> than the average.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let's look at the pattern of bike usage over time. </p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    TRUNC(start_time, 'Month') AS start_Month,\n",
    "    COUNT(1) AS trips\n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips\n",
    "GROUP BY 1\n",
    "ORDER BY 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart start_Month, trips, title = \"Trips by day\", typex = t, width = 700"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above chart we observe few things:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>There are two months where the data is nearly missing</li>\n",
    "    <li>The peak usage month is as much as 30k trips in a month</li>\n",
    "    <li>March and October are first and second busiest months across the data of 4 years.</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Can this be related to the weather? Is the weather in March and October favorable for biking? Let's see this in the next section.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.2 Examine the weather table</b></p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM DEMO_AustinBikeShare.weather SAMPLE 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The temperature data is reported hourly (the minutes and seconds are always zero). The temperature columns are in Kelvin, which few people use to decide if it is good bicycle weather, so we will create a view over the weather table to convert the temperature to Fahrenheit. We will also average the temperature for the day.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "REPLACE VIEW austin_weather AS\n",
    "    SELECT\n",
    "        TRUNC(dt, 'Month') AS dt, \n",
    "        ROUND(AVG((temp - 273.15) * 9/5 + 32) ,0) AS AveTemp,\n",
    "        SUM(CASE\n",
    "                WHEN weather_main in ('Rain', 'Mist') THEN 1\n",
    "                ELSE 0\n",
    "            END) AS Precip_hours\n",
    "    FROM DEMO_AustinBikeShare.weather\n",
    "    GROUP BY 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM austin_weather ORDER BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If we plot the data, we find we are missing some data, but we get an idea of the typical temperature ranges.  If we look at the hours each month when precipitation occurs, we see some patterns that could also be impacting the number of trips.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart dt, avetemp, width = 800, title = \"Average Temperature by Month\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can observe that for almost all of the March and October months, the temperature is around 70 degrees Fahrenheit. This is a favorable biking temperature as it is neither too cold nor too hot.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart dt, Precip_hours, width = 800, title = \"Average Precip Hours by Month\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above two charts, March and October have favorable conditions for biking, which reflects in the increased bike rides.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.3 Geospatial data</b></p>    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Geospatial columns have a type and one or more pairs of Latitude and Longitude. We included the Latitude and Longitude columns in the table so you could see how a simple geospatial feature (a POINT) is represented.\n",
    "    <br>\n",
    "For more geospatial datatypes supported by Teradata, please click <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Geospatial-Data-Types/Geospatial-Data/Geometry-Types'>here</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM DEMO_AustinBikeShare.stations SAMPLE 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Numerous geospatial functions exist, but we can demonstrate the basics by finding the distance from the main office (station_id = 1001) to other stations.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "For more geospatial functions supported by Teradata, please click <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Geospatial-Data-Types'>here</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    TOP 10 station.station_id, station.name, \n",
    "    ROUND(office.location.ST_SphericalDistance(station.location), 0) Distance_Meters\n",
    "FROM DEMO_AustinBikeShare.stations station, DEMO_AustinBikeShare.stations office\n",
    "WHERE office.station_id = 1001\n",
    "ORDER BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>3. Create and Explore Temporal, Geospatial and Time index data</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.1 Create a temporal table with weather data</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Temporal tables store and maintain information concerning time. Using temporal tables, Vantage can process statements and queries that include time-based reasoning. Temporal tables have one or two special columns which store time information:\n",
    "<ul style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>A transaction-time column records and maintains the period Vantage was aware of the information in the row. Vantage automatically enters and maintains the transaction-time column data and consequently tracks such information's history.</li>\n",
    "    <li>A valid-time column models the real-world and stores information such as the time an insurance policy or product warranty is valid, the length of employment of an employee, or other information that is important to track and manipulate in a time-aware fashion. When you add a new row to this type of table, you use the valid-time column to specify the time period for which the row information is valid. This is the period of validity (PV) of the information in the row.</li>\n",
    "</ul>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "CREATE TABLE weather_temporal (\n",
    "    begin_dt      TIMESTAMP(6) NOT NULL,\n",
    "    end_dt        TIMESTAMP(6) NOT NULL,\n",
    "    temp          FLOAT,\n",
    "    temp_min      FLOAT,\n",
    "    temp_max      FLOAT,\n",
    "    pressure      INTEGER,\n",
    "    humidity      INTEGER,\n",
    "    wind_speed    INTEGER,\n",
    "    wind_deg      INTEGER,\n",
    "    rain_1h       FLOAT,\n",
    "    rain_3h       FLOAT,\n",
    "    clouds        INTEGER,\n",
    "    weather_id    INTEGER,\n",
    "    weather_main  VARCHAR(50),\n",
    "    weather_desc  VARCHAR(50),\n",
    "    weather_icon  VARCHAR(50),\n",
    "    PERIOD FOR Weather_Duration(begin_dt,end_dt) AS VALIDTIME\n",
    ")\n",
    "PRIMARY INDEX (weather_id);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we are converting temp, temp_min, and temp_max from Kelvin to Fahrenheit while inserting the data into the weather_temporal table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "INSERT INTO weather_temporal\n",
    "SELECT\n",
    "    dt,\n",
    "    dt + INTERVAL '59' MINUTE + INTERVAL '59' SECOND,\n",
    "    round( ((temp - 273.15) * 9/5 + 32 ) ,0),\n",
    "    round( ((temp_min - 273.15) * 9/5 + 32 ) ,0),\n",
    "    round( ((temp_max - 273.15) * 9/5 + 32 ) ,0),\n",
    "    pressure,\n",
    "    humidity,\n",
    "    wind_speed,\n",
    "    wind_deg,\n",
    "    rain_1h,\n",
    "    rain_3h,\n",
    "    clouds,\n",
    "    weather_id,\n",
    "    weather_main,\n",
    "    weather_desc,\n",
    "    weather_icon\n",
    "FROM \n",
    "    DEMO_AustinBikeShare.weather;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SEQUENCED VALIDTIME SELECT * FROM weather_temporal SAMPLE 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now we can efficiently answer time-based reasoning queries faster and efficiently with Temporal tables. For example, was the weather favorable to biking in March and October 2016?</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    COUNT(weather_main) AS weather_hours, weather_main\n",
    "FROM (\n",
    "    VALIDTIME PERIOD '(2016-03-01, 2016-03-31)'\n",
    "    SELECT * FROM weather_temporal\n",
    ") AS dt\n",
    "GROUP BY weather_main;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart weather_main, weather_hours, width = 500, title = \"Duration(in hours) of weather by weather type(for March 2016)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    COUNT(weather_main) AS weather_hours, weather_main\n",
    "FROM (\n",
    "        VALIDTIME PERIOD '(2016-10-01, 2016-10-30)'\n",
    "        SELECT * FROM weather_temporal\n",
    "    ) AS dt\n",
    "GROUP BY weather_main;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart weather_main, weather_hours, width = 500, title = \"Duration(in hours) of weather by weather type(for October 2016)\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graphs suggest that March and October 2016 had more days favorable for biking(clear, cloudy, mist), hence explaining the increased number of bike rides.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.2 Create a view for all trips with start/end stations data and a GEOSEQUENCE with start/end lat/long/time</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The code below defines a view which enhances the trip data with a Geosequence field containing the location and time for the start and end points of the trip.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "REPLACE VIEW trips_geo AS\n",
    "SELECT\n",
    "    t.bikeid,\n",
    "    t.trip_ID,\n",
    "    t.subscriber_type,\n",
    "    t.start_station_id,\n",
    "    COALESCE(t.start_station_name, st.NAME) AS start_station_name,\n",
    "    t.start_time,\n",
    "    st.status starting_station_status,\n",
    "    t.end_station_id,\n",
    "    COALESCE(t.end_station_name, ed.NAME) AS end_station_name,\n",
    "    t.start_time \n",
    "        + CAST(t.duration_minutes/60 AS INTERVAL HOUR(4)) \n",
    "        + CAST(t.duration_minutes MOD 60 AS INTERVAL MINUTE(4)) AS end_time,\n",
    "    ed.status AS End_station_status,\n",
    "    t.duration_minutes,\n",
    "    NEW ST_GEOMETRY('ST_POINT' ,st.Longitude, st.Latitude) AS start_location,\n",
    "    NEW ST_GEOMETRY('ST_POINT' ,ed.Longitude, ed.Latitude) AS end_location,\n",
    "    CAST('GEOSEQUENCE( ('\n",
    "        || COALESCE(st.Longitude,-98.272797)\n",
    "        || ' '\n",
    "        || COALESCE(st.Latitude,30.578245)\n",
    "        || ','\n",
    "        || COALESCE(ed.longitude,-98.272797)\n",
    "        || ' '\n",
    "        || COALESCE(ed.latitude,30.578245)\n",
    "        || '), ('\n",
    "        || CAST(CAST(t.start_time AS FORMAT 'yyyy-mm-ddbhh:mi:ss') AS VARCHAR(50))\n",
    "        || ','\n",
    "        || CAST(CAST(end_time AS FORMAT 'yyyy-mm-ddbhh:mi:ss') AS VARCHAR(50))\n",
    "        || '), ('\n",
    "        || '1,2), (0) )' AS ST_GEOMETRY) AS GEOM\n",
    "FROM\n",
    "    DEMO_AustinBikeShare.trips AS t\n",
    "    LEFT JOIN DEMO_AustinBikeShare.stations AS st ON t.start_station_id = st.station_id\n",
    "    LEFT JOIN DEMO_AustinBikeShare.stations AS ed ON t.end_station_id = ed.station_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT TOP 10 * FROM trips_geo;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.3 Create a Time Index table of the trips to accelerate time related analysis</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage supports tables with a Primary Time Index (PTI), which is used to store and quickly look up data that arrives based on time. This time-aware index distributes data across the units of parallelism. Still, it allows the optimizer to build plans which go directly to the unit of parallelism where the data is stored based on the time constraint.<br><br>\n",
    "In this case, we will declare the index to have hourly granularity with a baseline time earlier than any date of data we have. Based on the primary index declaration, the database automatically creates the first column with the name TD_TIMECODE. When we insert data, we will use the start_time column as that value.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "CREATE TABLE trips_geo_pti (\n",
    "    bikeid                    INTEGER,\n",
    "    trip_id                   BIGINT,\n",
    "    subscriber_type           VARCHAR(50),\n",
    "    start_station_id          INTEGER,\n",
    "    start_station_name        VARCHAR(100),\n",
    "    starting_station_status   VARCHAR(50),\n",
    "    end_station_id            INTEGER,\n",
    "    end_station_name          VARCHAR(100),\n",
    "    end_time                  TIMESTAMP(6),\n",
    "    end_station_status        VARCHAR(50),\n",
    "    duration_minutes          INTEGER,\n",
    "    geom                      SYSUDTLIB.ST_GEOMETRY(16776192) INLINE LENGTH 9920\n",
    ")\n",
    "PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2013-12-20', MINUTES(60));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We now populate the local table.  This could take a minute to get data from the cloud storage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "INSERT INTO trips_geo_pti\n",
    "SELECT\n",
    "    start_time,\n",
    "    bikeid,\n",
    "    trip_id,\n",
    "    subscriber_type,\n",
    "    start_station_id,\n",
    "    start_station_name,\n",
    "    starting_station_status,\n",
    "    end_station_id,\n",
    "    end_station_name,\n",
    "    end_time,\n",
    "    End_station_status,\n",
    "    duration_minutes,\n",
    "    geom\n",
    "FROM\n",
    "    trips_geo;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT TOP 10 * FROM trips_geo_pti"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.4 Augment trips data with weather data and extract geospatial information</b></p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Finally, we bring the data together with the geosequence trip information with the available weather data, where the weather report period contains the trip's start time (TD_TIMECODE).</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "For more geospatial functions supported by Teradata, please click <a href = 'https://docs.teradata.com/r/Geospatial-Data-Types/June-2020'>here</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "CREATE TABLE trips_and_weather AS (\n",
    "    SELECT \n",
    "        t.start_station_name,\n",
    "        t.end_station_name,\n",
    "        t.bikeid,\n",
    "        t.trip_id,\n",
    "        t.subscriber_type as subscriber_type,\n",
    "        t.geom.GetInitT() AS pickup_time,\n",
    "        t.geom.GetFinalT() AS dropoff_time,\n",
    "        t.geom.ST_POINTN(1).ST_SPHEROIDALDISTANCE(geom.ST_POINTN(2))/1000 AS total_distance,\n",
    "        t.geom.ST_POINTN(1).ST_X() AS pickup_location_lon,\n",
    "        t.geom.ST_POINTN(1).ST_Y() AS pickup_location_lat,\n",
    "        t.geom.ST_POINTN(2).ST_X() AS dropoff_location_lon,\n",
    "        t.geom.ST_POINTN(2).ST_Y() AS dropoff_location_lat,        \n",
    "        t.duration_minutes,\n",
    "        t.TD_TIMECODE as Trip_TIMECODE,\n",
    "        wt.*\n",
    "    FROM \n",
    "        trips_geo_pti AS t\n",
    "        INNER JOIN Weather_temporal AS wt ON wt.weather_duration CONTAINS t.TD_TIMECODE\n",
    "        AND pickup_time >= '2017-07-01 00:00:00'\n",
    ")\n",
    "WITH DATA primary index(trip_id);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT TOP 10 * FROM trips_and_weather WHERE CAST(pickup_time AS DATE) BETWEEN '2017-07-01' AND '2017-07-31'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>4. Insights</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.1 Average distance traveled w.r.t start stations</b></p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    start_station_name, AVG(total_distance), COUNT(trip_id)\n",
    "FROM trips_and_weather\n",
    "GROUP BY start_station_name\n",
    "ORDER BY 2 DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above visualization suggests that Main Office has the highest average distance people travel. Even though only ten trips originate from the main station, it still has the highest average distance traveled. These ten trips are very long.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.2 Effect of weather on distance traveled</b></p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    TOP 10 SUM(total_distance) AS distance_km, subscriber_type, weather_main\n",
    "FROM trips_and_weather\n",
    "GROUP BY subscriber_type, weather_main\n",
    "ORDER BY 1 DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looking at the results above, walk-up, local365, and local30 subscribers traveled more distance when the weather was clear or cloudy.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.3 Average trip duration w.r.t subscriber type and trip type</b></p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    subscriber_type,\n",
    "    CASE\n",
    "        WHEN start_station_name = end_station_name THEN 'Round_Trip'\n",
    "        ELSE 'Point-to-Point'\n",
    "    END AS trip_type,\n",
    "    AVG(duration_minutes) AS time_mins\n",
    "FROM trips_and_weather\n",
    "GROUP BY subscriber_type, trip_type\n",
    "ORDER BY 3 DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looking at the results above, round trips have longer trips than point-to-point for the explorer, walk up and annual members.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.4 Does the bike require maintenance?</b></p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [],
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    bikeid, COUNT(*) AS num_trips, sum(total_distance) AS distance,\n",
    "    CASE\n",
    "        WHEN distance > 70 THEN 'Recommended'\n",
    "        ELSE 'Not Required'\n",
    "    END AS maintenance\n",
    "FROM trips_and_weather\n",
    "GROUP BY bikeid\n",
    "ORDER BY 3 DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart maintenance, bikeid, aggregatey=count, width = 200, title=\"Maintenance Required?\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looking at the results above, 50 bikes require maintenance according to our assumption that we should do bike repairs after every 70 kms.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>5. Clean up</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1 Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time. This section drops all the tables created during the demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "DROP TABLE weather_temporal;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "DROP TABLE trips_geo_pti;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "DROP TABLE trips_and_weather;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [],
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "DROP VIEW trips_geo;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b> 5.2 Databases and Tables</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,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "call remove_data('DEMO_AustinBikeShare')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Links:</b></p>\n",
    "<ul style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Information about Geospatial datatype can be found <a href = 'https://docs.teradata.com/search/all?query=geospatial&content-lang=en-US'>here</a></li>\n",
    "    <li>Information about Temporal datatype can be found <a href = 'https://docs.teradata.com/search/all?query=temporal&content-lang=en-US'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "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": 4
}