4. Analyzing Business Pattern and Seasonality.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "code",
            "source": [
                "use mavenfuzzyfactory"
            ],
            "metadata": {
                "azdata_cell_guid": "cd2f2c97-3e0e-4a38-82e6-ca9937046937",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.002"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Understanding Seasonality**\n",
                "\n",
                "<span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">2012 was a great year for us. As we continue to grow, we&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">should take a look at 2012's monthly and weekly volume&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">patterns, to see if we can find any seasonal trends we&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">should plan for in 2013.&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">If you can pull session volume and order volume, that&nbsp;</span> <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">would be excellent</span>"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "409eb1f7-3ec0-4a3c-8ff7-3d46a67c7f8d"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "select * from website_sessions limit 2"
            ],
            "metadata": {
                "azdata_cell_guid": "bf5039ac-ee2c-4afa-bcf4-d4af69efdbd7",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.008"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 3,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "created_at"
                                    },
                                    {
                                        "name": "user_id"
                                    },
                                    {
                                        "name": "is_repeat_session"
                                    },
                                    {
                                        "name": "utm_source"
                                    },
                                    {
                                        "name": "utm_campaign"
                                    },
                                    {
                                        "name": "utm_content"
                                    },
                                    {
                                        "name": "device_type"
                                    },
                                    {
                                        "name": "http_referer"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "website_session_id": "1",
                                    "created_at": "2012-03-19 08:04:16",
                                    "user_id": "1",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "mobile",
                                    "http_referer": "https://www.gsearch.com"
                                },
                                {
                                    "website_session_id": "2",
                                    "created_at": "2012-03-19 08:16:49",
                                    "user_id": "2",
                                    "is_repeat_session": "0",
                                    "utm_source": "gsearch",
                                    "utm_campaign": "nonbrand",
                                    "utm_content": "g_ad_1",
                                    "device_type": "desktop",
                                    "http_referer": "https://www.gsearch.com"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>website_session_id</th><th>created_at</th><th>user_id</th><th>is_repeat_session</th><th>utm_source</th><th>utm_campaign</th><th>utm_content</th><th>device_type</th><th>http_referer</th></tr>",
                            "<tr><td>1</td><td>2012-03-19 08:04:16</td><td>1</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>mobile</td><td>https://www.gsearch.com</td></tr>",
                            "<tr><td>2</td><td>2012-03-19 08:16:49</td><td>2</td><td>0</td><td>gsearch</td><td>nonbrand</td><td>g_ad_1</td><td>desktop</td><td>https://www.gsearch.com</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "code",
            "source": [
                "select * from orders limit 2"
            ],
            "metadata": {
                "azdata_cell_guid": "c4b6a48f-2670-44eb-ab38-62d8980abf97",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 4,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "order_id"
                                    },
                                    {
                                        "name": "created_at"
                                    },
                                    {
                                        "name": "website_session_id"
                                    },
                                    {
                                        "name": "user_id"
                                    },
                                    {
                                        "name": "primary_product_id"
                                    },
                                    {
                                        "name": "items_purchased"
                                    },
                                    {
                                        "name": "price_usd"
                                    },
                                    {
                                        "name": "cogs_usd"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "order_id": "1",
                                    "created_at": "2012-03-19 10:42:46",
                                    "website_session_id": "20",
                                    "user_id": "20",
                                    "primary_product_id": "1",
                                    "items_purchased": "1",
                                    "price_usd": "49.99",
                                    "cogs_usd": "19.49"
                                },
                                {
                                    "order_id": "2",
                                    "created_at": "2012-03-19 19:27:37",
                                    "website_session_id": "104",
                                    "user_id": "104",
                                    "primary_product_id": "1",
                                    "items_purchased": "1",
                                    "price_usd": "49.99",
                                    "cogs_usd": "19.49"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>order_id</th><th>created_at</th><th>website_session_id</th><th>user_id</th><th>primary_product_id</th><th>items_purchased</th><th>price_usd</th><th>cogs_usd</th></tr>",
                            "<tr><td>1</td><td>2012-03-19 10:42:46</td><td>20</td><td>20</td><td>1</td><td>1</td><td>49.99</td><td>19.49</td></tr>",
                            "<tr><td>2</td><td>2012-03-19 19:27:37</td><td>104</td><td>104</td><td>1</td><td>1</td><td>49.99</td><td>19.49</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "code",
            "source": [
                "select\n",
                "min(date(website_sessions.created_at)) as week_start_date,\n",
                "count(DISTINCT website_sessions.website_session_id) as sessions,\n",
                "count(DISTINCT orders.order_id) as orders\n",
                "from website_sessions\n",
                "left join orders\n",
                "on website_sessions.website_session_id = orders.website_session_id\n",
                "where website_sessions.created_at < '2013-01-01'\n",
                "GROUP by YEARWEEK(date(website_sessions.created_at))"
            ],
            "metadata": {
                "azdata_cell_guid": "cbe25dbb-bdf6-4765-95c1-63e8f3c4e5da",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(42 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.255"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 5,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "week_start_date"
                                    },
                                    {
                                        "name": "sessions"
                                    },
                                    {
                                        "name": "orders"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "week_start_date": "2012-03-19",
                                    "sessions": "896",
                                    "orders": "25"
                                },
                                {
                                    "week_start_date": "2012-03-25",
                                    "sessions": "983",
                                    "orders": "35"
                                },
                                {
                                    "week_start_date": "2012-04-01",
                                    "sessions": "1193",
                                    "orders": "29"
                                },
                                {
                                    "week_start_date": "2012-04-08",
                                    "sessions": "1029",
                                    "orders": "28"
                                },
                                {
                                    "week_start_date": "2012-04-15",
                                    "sessions": "679",
                                    "orders": "22"
                                },
                                {
                                    "week_start_date": "2012-04-22",
                                    "sessions": "655",
                                    "orders": "18"
                                },
                                {
                                    "week_start_date": "2012-04-29",
                                    "sessions": "770",
                                    "orders": "19"
                                },
                                {
                                    "week_start_date": "2012-05-06",
                                    "sessions": "798",
                                    "orders": "17"
                                },
                                {
                                    "week_start_date": "2012-05-13",
                                    "sessions": "706",
                                    "orders": "23"
                                },
                                {
                                    "week_start_date": "2012-05-20",
                                    "sessions": "965",
                                    "orders": "28"
                                },
                                {
                                    "week_start_date": "2012-05-27",
                                    "sessions": "875",
                                    "orders": "31"
                                },
                                {
                                    "week_start_date": "2012-06-03",
                                    "sessions": "920",
                                    "orders": "34"
                                },
                                {
                                    "week_start_date": "2012-06-10",
                                    "sessions": "994",
                                    "orders": "29"
                                },
                                {
                                    "week_start_date": "2012-06-17",
                                    "sessions": "966",
                                    "orders": "37"
                                },
                                {
                                    "week_start_date": "2012-06-24",
                                    "sessions": "883",
                                    "orders": "32"
                                },
                                {
                                    "week_start_date": "2012-07-01",
                                    "sessions": "892",
                                    "orders": "30"
                                },
                                {
                                    "week_start_date": "2012-07-08",
                                    "sessions": "925",
                                    "orders": "36"
                                },
                                {
                                    "week_start_date": "2012-07-15",
                                    "sessions": "987",
                                    "orders": "47"
                                },
                                {
                                    "week_start_date": "2012-07-22",
                                    "sessions": "954",
                                    "orders": "41"
                                },
                                {
                                    "week_start_date": "2012-07-29",
                                    "sessions": "1172",
                                    "orders": "55"
                                },
                                {
                                    "week_start_date": "2012-08-05",
                                    "sessions": "1235",
                                    "orders": "48"
                                },
                                {
                                    "week_start_date": "2012-08-12",
                                    "sessions": "1181",
                                    "orders": "39"
                                },
                                {
                                    "week_start_date": "2012-08-19",
                                    "sessions": "1522",
                                    "orders": "55"
                                },
                                {
                                    "week_start_date": "2012-08-26",
                                    "sessions": "1593",
                                    "orders": "52"
                                },
                                {
                                    "week_start_date": "2012-09-02",
                                    "sessions": "1418",
                                    "orders": "56"
                                },
                                {
                                    "week_start_date": "2012-09-09",
                                    "sessions": "1488",
                                    "orders": "72"
                                },
                                {
                                    "week_start_date": "2012-09-16",
                                    "sessions": "1776",
                                    "orders": "76"
                                },
                                {
                                    "week_start_date": "2012-09-23",
                                    "sessions": "1624",
                                    "orders": "70"
                                },
                                {
                                    "week_start_date": "2012-09-30",
                                    "sessions": "1553",
                                    "orders": "67"
                                },
                                {
                                    "week_start_date": "2012-10-07",
                                    "sessions": "1632",
                                    "orders": "73"
                                },
                                {
                                    "week_start_date": "2012-10-14",
                                    "sessions": "1955",
                                    "orders": "93"
                                },
                                {
                                    "week_start_date": "2012-10-21",
                                    "sessions": "2042",
                                    "orders": "95"
                                },
                                {
                                    "week_start_date": "2012-10-28",
                                    "sessions": "1923",
                                    "orders": "82"
                                },
                                {
                                    "week_start_date": "2012-11-04",
                                    "sessions": "2086",
                                    "orders": "91"
                                },
                                {
                                    "week_start_date": "2012-11-11",
                                    "sessions": "1973",
                                    "orders": "101"
                                },
                                {
                                    "week_start_date": "2012-11-18",
                                    "sessions": "5130",
                                    "orders": "223"
                                },
                                {
                                    "week_start_date": "2012-11-25",
                                    "sessions": "4172",
                                    "orders": "179"
                                },
                                {
                                    "week_start_date": "2012-12-02",
                                    "sessions": "2727",
                                    "orders": "145"
                                },
                                {
                                    "week_start_date": "2012-12-09",
                                    "sessions": "2489",
                                    "orders": "123"
                                },
                                {
                                    "week_start_date": "2012-12-16",
                                    "sessions": "2718",
                                    "orders": "135"
                                },
                                {
                                    "week_start_date": "2012-12-23",
                                    "sessions": "1682",
                                    "orders": "74"
                                },
                                {
                                    "week_start_date": "2012-12-30",
                                    "sessions": "309",
                                    "orders": "21"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>week_start_date</th><th>sessions</th><th>orders</th></tr>",
                            "<tr><td>2012-03-19</td><td>896</td><td>25</td></tr>",
                            "<tr><td>2012-03-25</td><td>983</td><td>35</td></tr>",
                            "<tr><td>2012-04-01</td><td>1193</td><td>29</td></tr>",
                            "<tr><td>2012-04-08</td><td>1029</td><td>28</td></tr>",
                            "<tr><td>2012-04-15</td><td>679</td><td>22</td></tr>",
                            "<tr><td>2012-04-22</td><td>655</td><td>18</td></tr>",
                            "<tr><td>2012-04-29</td><td>770</td><td>19</td></tr>",
                            "<tr><td>2012-05-06</td><td>798</td><td>17</td></tr>",
                            "<tr><td>2012-05-13</td><td>706</td><td>23</td></tr>",
                            "<tr><td>2012-05-20</td><td>965</td><td>28</td></tr>",
                            "<tr><td>2012-05-27</td><td>875</td><td>31</td></tr>",
                            "<tr><td>2012-06-03</td><td>920</td><td>34</td></tr>",
                            "<tr><td>2012-06-10</td><td>994</td><td>29</td></tr>",
                            "<tr><td>2012-06-17</td><td>966</td><td>37</td></tr>",
                            "<tr><td>2012-06-24</td><td>883</td><td>32</td></tr>",
                            "<tr><td>2012-07-01</td><td>892</td><td>30</td></tr>",
                            "<tr><td>2012-07-08</td><td>925</td><td>36</td></tr>",
                            "<tr><td>2012-07-15</td><td>987</td><td>47</td></tr>",
                            "<tr><td>2012-07-22</td><td>954</td><td>41</td></tr>",
                            "<tr><td>2012-07-29</td><td>1172</td><td>55</td></tr>",
                            "<tr><td>2012-08-05</td><td>1235</td><td>48</td></tr>",
                            "<tr><td>2012-08-12</td><td>1181</td><td>39</td></tr>",
                            "<tr><td>2012-08-19</td><td>1522</td><td>55</td></tr>",
                            "<tr><td>2012-08-26</td><td>1593</td><td>52</td></tr>",
                            "<tr><td>2012-09-02</td><td>1418</td><td>56</td></tr>",
                            "<tr><td>2012-09-09</td><td>1488</td><td>72</td></tr>",
                            "<tr><td>2012-09-16</td><td>1776</td><td>76</td></tr>",
                            "<tr><td>2012-09-23</td><td>1624</td><td>70</td></tr>",
                            "<tr><td>2012-09-30</td><td>1553</td><td>67</td></tr>",
                            "<tr><td>2012-10-07</td><td>1632</td><td>73</td></tr>",
                            "<tr><td>2012-10-14</td><td>1955</td><td>93</td></tr>",
                            "<tr><td>2012-10-21</td><td>2042</td><td>95</td></tr>",
                            "<tr><td>2012-10-28</td><td>1923</td><td>82</td></tr>",
                            "<tr><td>2012-11-04</td><td>2086</td><td>91</td></tr>",
                            "<tr><td>2012-11-11</td><td>1973</td><td>101</td></tr>",
                            "<tr><td>2012-11-18</td><td>5130</td><td>223</td></tr>",
                            "<tr><td>2012-11-25</td><td>4172</td><td>179</td></tr>",
                            "<tr><td>2012-12-02</td><td>2727</td><td>145</td></tr>",
                            "<tr><td>2012-12-09</td><td>2489</td><td>123</td></tr>",
                            "<tr><td>2012-12-16</td><td>2718</td><td>135</td></tr>",
                            "<tr><td>2012-12-23</td><td>1682</td><td>74</td></tr>",
                            "<tr><td>2012-12-30</td><td>309</td><td>21</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Subject: Data for Customer Service**\n",
                "\n",
                "<span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">We're considering adding live chat support to the website&nbsp;</span>    <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">to improve our customer experience. Could you analyze&nbsp;</span>    <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">the average website session volume, by hour of day and&nbsp;</span>    <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">by day week, so that we can staff appropriately?&nbsp;</span>    <span style=\"font-family: -apple-system, BlinkMacSystemFont, sans-serif; color: var(--vscode-foreground);\">Let's avoid the holiday time period and use a date range of&nbsp;</span>   Sep 15 - Nov 15, 2012.\n",
                "\n",
                "**Important: To Extract Date we use date() function which is not required in other function such as hour, week, quater and other function**"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "a3a1e913-57f5-4ddc-8360-51cc443c8ffd"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT\n",
                "DATE(created_at) AS created_date,\n",
                "WEEKDAY(created_at) AS wday,\n",
                "HOUR(created_at) AS hr,\n",
                "COUNT(DISTINCT website_session_id) AS website_sessions\n",
                "FROM website_sessions\n",
                "WHERE created_at BETWEEN '2012-09-15' AND '2012-11-15'\n",
                "GROUP BY 1,2,3 limit 15"
            ],
            "metadata": {
                "azdata_cell_guid": "449f1e97-1d43-4b5e-81ca-8eec69baf770",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(15 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.228"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 12,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "created_date"
                                    },
                                    {
                                        "name": "wday"
                                    },
                                    {
                                        "name": "hr"
                                    },
                                    {
                                        "name": "website_sessions"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "0",
                                    "website_sessions": "1"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "1",
                                    "website_sessions": "8"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "2",
                                    "website_sessions": "2"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "3",
                                    "website_sessions": "4"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "4",
                                    "website_sessions": "2"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "5",
                                    "website_sessions": "3"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "6",
                                    "website_sessions": "2"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "7",
                                    "website_sessions": "7"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "8",
                                    "website_sessions": "2"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "9",
                                    "website_sessions": "6"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "10",
                                    "website_sessions": "4"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "11",
                                    "website_sessions": "6"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "12",
                                    "website_sessions": "8"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "13",
                                    "website_sessions": "9"
                                },
                                {
                                    "created_date": "2012-09-15",
                                    "wday": "5",
                                    "hr": "14",
                                    "website_sessions": "9"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>created_date</th><th>wday</th><th>hr</th><th>website_sessions</th></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>0</td><td>1</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>1</td><td>8</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>2</td><td>2</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>3</td><td>4</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>4</td><td>2</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>5</td><td>3</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>6</td><td>2</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>7</td><td>7</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>8</td><td>2</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>9</td><td>6</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>10</td><td>4</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>11</td><td>6</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>12</td><td>8</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>13</td><td>9</td></tr>",
                            "<tr><td>2012-09-15</td><td>5</td><td>14</td><td>9</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT\n",
                "hr,\n",
                "ROUND (AVG(CASE WHEN wday = 0 THEN website_sessions ELSE NULL END), 1) AS mon,\n",
                "ROUND (AVG(CASE WHEN wday = 1 THEN website_sessions ELSE NULL END),1) AS tues,\n",
                "ROUND (AVG (CASE WHEN wday = 2 THEN website_sessions ELSE NULL END),1) AS weds,\n",
                "ROUND (AVG (CASE WHEN wday = 3 THEN website_sessions ELSE NULL END),1) AS thurs,\n",
                "ROUND (AVG (CASE WHEN wday = 4 THEN website_sessions ELSE NULL END), 1) AS fri,\n",
                "ROUND (AVG(CASE WHEN wday = 5 THEN website_sessions ELSE NULL END),1) AS sat,\n",
                "ROUND (AVG(CASE WHEN wday = 6 THEN website_sessions ELSE NULL END), 1) AS sun\n",
                "FROM (\n",
                "SELECT\n",
                "DATE(created_at) AS created_date,\n",
                "WEEKDAY(created_at) AS wday,\n",
                "HOUR(created_at) AS hr,\n",
                "COUNT(DISTINCT website_session_id) AS website_sessions\n",
                "FROM website_sessions\n",
                "WHERE created_at BETWEEN '2012-09-15' AND '2012-11-15'\n",
                "GROUP BY 1,2,3\n",
                ") AS daily_hourly_sessions\n",
                "GROUP BY 1\n",
                "ORDER BY 1"
            ],
            "metadata": {
                "azdata_cell_guid": "694dd558-f498-492d-894b-8927526c89d4",
                "language": "sql",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(24 row(s) affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:01.254"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 10,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "hr"
                                    },
                                    {
                                        "name": "mon"
                                    },
                                    {
                                        "name": "tues"
                                    },
                                    {
                                        "name": "weds"
                                    },
                                    {
                                        "name": "thurs"
                                    },
                                    {
                                        "name": "fri"
                                    },
                                    {
                                        "name": "sat"
                                    },
                                    {
                                        "name": "sun"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "hr": "0",
                                    "mon": "8.7",
                                    "tues": "7.7",
                                    "weds": "6.3",
                                    "thurs": "7.4",
                                    "fri": "6.8",
                                    "sat": "5.0",
                                    "sun": "5.0"
                                },
                                {
                                    "hr": "1",
                                    "mon": "6.6",
                                    "tues": "6.7",
                                    "weds": "5.3",
                                    "thurs": "4.9",
                                    "fri": "7.1",
                                    "sat": "5.0",
                                    "sun": "3.0"
                                },
                                {
                                    "hr": "2",
                                    "mon": "6.1",
                                    "tues": "4.4",
                                    "weds": "4.4",
                                    "thurs": "6.1",
                                    "fri": "4.6",
                                    "sat": "3.7",
                                    "sun": "3.0"
                                },
                                {
                                    "hr": "3",
                                    "mon": "5.7",
                                    "tues": "4.0",
                                    "weds": "4.7",
                                    "thurs": "4.6",
                                    "fri": "3.6",
                                    "sat": "3.9",
                                    "sun": "3.4"
                                },
                                {
                                    "hr": "4",
                                    "mon": "5.9",
                                    "tues": "6.3",
                                    "weds": "6.0",
                                    "thurs": "4.0",
                                    "fri": "6.1",
                                    "sat": "2.8",
                                    "sun": "2.4"
                                },
                                {
                                    "hr": "5",
                                    "mon": "5.0",
                                    "tues": "5.4",
                                    "weds": "5.1",
                                    "thurs": "5.4",
                                    "fri": "4.6",
                                    "sat": "4.3",
                                    "sun": "3.9"
                                },
                                {
                                    "hr": "6",
                                    "mon": "5.4",
                                    "tues": "5.6",
                                    "weds": "4.8",
                                    "thurs": "6.0",
                                    "fri": "6.8",
                                    "sat": "4.0",
                                    "sun": "2.6"
                                },
                                {
                                    "hr": "7",
                                    "mon": "7.3",
                                    "tues": "7.8",
                                    "weds": "7.4",
                                    "thurs": "10.6",
                                    "fri": "7.0",
                                    "sat": "5.7",
                                    "sun": "4.8"
                                },
                                {
                                    "hr": "8",
                                    "mon": "12.3",
                                    "tues": "12.2",
                                    "weds": "13.0",
                                    "thurs": "16.5",
                                    "fri": "10.5",
                                    "sat": "4.3",
                                    "sun": "4.1"
                                },
                                {
                                    "hr": "9",
                                    "mon": "17.6",
                                    "tues": "15.7",
                                    "weds": "19.6",
                                    "thurs": "19.3",
                                    "fri": "17.5",
                                    "sat": "7.6",
                                    "sun": "6.0"
                                },
                                {
                                    "hr": "10",
                                    "mon": "18.4",
                                    "tues": "17.7",
                                    "weds": "21.0",
                                    "thurs": "18.4",
                                    "fri": "19.0",
                                    "sat": "8.3",
                                    "sun": "6.3"
                                },
                                {
                                    "hr": "11",
                                    "mon": "18.0",
                                    "tues": "19.1",
                                    "weds": "24.9",
                                    "thurs": "21.6",
                                    "fri": "20.9",
                                    "sat": "7.2",
                                    "sun": "7.7"
                                },
                                {
                                    "hr": "12",
                                    "mon": "21.1",
                                    "tues": "23.3",
                                    "weds": "22.8",
                                    "thurs": "24.1",
                                    "fri": "19.0",
                                    "sat": "8.6",
                                    "sun": "6.1"
                                },
                                {
                                    "hr": "13",
                                    "mon": "17.8",
                                    "tues": "23.0",
                                    "weds": "20.8",
                                    "thurs": "20.6",
                                    "fri": "21.6",
                                    "sat": "8.1",
                                    "sun": "8.4"
                                },
                                {
                                    "hr": "14",
                                    "mon": "17.9",
                                    "tues": "21.6",
                                    "weds": "22.3",
                                    "thurs": "18.5",
                                    "fri": "19.5",
                                    "sat": "8.7",
                                    "sun": "6.7"
                                },
                                {
                                    "hr": "15",
                                    "mon": "21.6",
                                    "tues": "17.1",
                                    "weds": "25.3",
                                    "thurs": "23.5",
                                    "fri": "21.3",
                                    "sat": "6.9",
                                    "sun": "7.1"
                                },
                                {
                                    "hr": "16",
                                    "mon": "21.1",
                                    "tues": "23.7",
                                    "weds": "23.7",
                                    "thurs": "19.6",
                                    "fri": "20.9",
                                    "sat": "7.6",
                                    "sun": "6.6"
                                },
                                {
                                    "hr": "17",
                                    "mon": "19.4",
                                    "tues": "15.9",
                                    "weds": "20.2",
                                    "thurs": "19.8",
                                    "fri": "12.9",
                                    "sat": "6.4",
                                    "sun": "7.6"
                                },
                                {
                                    "hr": "18",
                                    "mon": "12.7",
                                    "tues": "15.0",
                                    "weds": "14.8",
                                    "thurs": "15.3",
                                    "fri": "10.9",
                                    "sat": "5.3",
                                    "sun": "6.8"
                                },
                                {
                                    "hr": "19",
                                    "mon": "12.4",
                                    "tues": "14.1",
                                    "weds": "13.3",
                                    "thurs": "11.6",
                                    "fri": "14.3",
                                    "sat": "7.1",
                                    "sun": "6.4"
                                },
                                {
                                    "hr": "20",
                                    "mon": "12.1",
                                    "tues": "12.4",
                                    "weds": "14.2",
                                    "thurs": "10.6",
                                    "fri": "10.3",
                                    "sat": "5.7",
                                    "sun": "8.4"
                                },
                                {
                                    "hr": "21",
                                    "mon": "9.1",
                                    "tues": "12.6",
                                    "weds": "11.4",
                                    "thurs": "9.4",
                                    "fri": "7.3",
                                    "sat": "5.7",
                                    "sun": "10.2"
                                },
                                {
                                    "hr": "22",
                                    "mon": "9.1",
                                    "tues": "10.0",
                                    "weds": "9.8",
                                    "thurs": "12.1",
                                    "fri": "6.0",
                                    "sat": "5.7",
                                    "sun": "10.2"
                                },
                                {
                                    "hr": "23",
                                    "mon": "8.8",
                                    "tues": "8.6",
                                    "weds": "9.6",
                                    "thurs": "10.6",
                                    "fri": "7.6",
                                    "sat": "5.3",
                                    "sun": "8.3"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>hr</th><th>mon</th><th>tues</th><th>weds</th><th>thurs</th><th>fri</th><th>sat</th><th>sun</th></tr>",
                            "<tr><td>0</td><td>8.7</td><td>7.7</td><td>6.3</td><td>7.4</td><td>6.8</td><td>5.0</td><td>5.0</td></tr>",
                            "<tr><td>1</td><td>6.6</td><td>6.7</td><td>5.3</td><td>4.9</td><td>7.1</td><td>5.0</td><td>3.0</td></tr>",
                            "<tr><td>2</td><td>6.1</td><td>4.4</td><td>4.4</td><td>6.1</td><td>4.6</td><td>3.7</td><td>3.0</td></tr>",
                            "<tr><td>3</td><td>5.7</td><td>4.0</td><td>4.7</td><td>4.6</td><td>3.6</td><td>3.9</td><td>3.4</td></tr>",
                            "<tr><td>4</td><td>5.9</td><td>6.3</td><td>6.0</td><td>4.0</td><td>6.1</td><td>2.8</td><td>2.4</td></tr>",
                            "<tr><td>5</td><td>5.0</td><td>5.4</td><td>5.1</td><td>5.4</td><td>4.6</td><td>4.3</td><td>3.9</td></tr>",
                            "<tr><td>6</td><td>5.4</td><td>5.6</td><td>4.8</td><td>6.0</td><td>6.8</td><td>4.0</td><td>2.6</td></tr>",
                            "<tr><td>7</td><td>7.3</td><td>7.8</td><td>7.4</td><td>10.6</td><td>7.0</td><td>5.7</td><td>4.8</td></tr>",
                            "<tr><td>8</td><td>12.3</td><td>12.2</td><td>13.0</td><td>16.5</td><td>10.5</td><td>4.3</td><td>4.1</td></tr>",
                            "<tr><td>9</td><td>17.6</td><td>15.7</td><td>19.6</td><td>19.3</td><td>17.5</td><td>7.6</td><td>6.0</td></tr>",
                            "<tr><td>10</td><td>18.4</td><td>17.7</td><td>21.0</td><td>18.4</td><td>19.0</td><td>8.3</td><td>6.3</td></tr>",
                            "<tr><td>11</td><td>18.0</td><td>19.1</td><td>24.9</td><td>21.6</td><td>20.9</td><td>7.2</td><td>7.7</td></tr>",
                            "<tr><td>12</td><td>21.1</td><td>23.3</td><td>22.8</td><td>24.1</td><td>19.0</td><td>8.6</td><td>6.1</td></tr>",
                            "<tr><td>13</td><td>17.8</td><td>23.0</td><td>20.8</td><td>20.6</td><td>21.6</td><td>8.1</td><td>8.4</td></tr>",
                            "<tr><td>14</td><td>17.9</td><td>21.6</td><td>22.3</td><td>18.5</td><td>19.5</td><td>8.7</td><td>6.7</td></tr>",
                            "<tr><td>15</td><td>21.6</td><td>17.1</td><td>25.3</td><td>23.5</td><td>21.3</td><td>6.9</td><td>7.1</td></tr>",
                            "<tr><td>16</td><td>21.1</td><td>23.7</td><td>23.7</td><td>19.6</td><td>20.9</td><td>7.6</td><td>6.6</td></tr>",
                            "<tr><td>17</td><td>19.4</td><td>15.9</td><td>20.2</td><td>19.8</td><td>12.9</td><td>6.4</td><td>7.6</td></tr>",
                            "<tr><td>18</td><td>12.7</td><td>15.0</td><td>14.8</td><td>15.3</td><td>10.9</td><td>5.3</td><td>6.8</td></tr>",
                            "<tr><td>19</td><td>12.4</td><td>14.1</td><td>13.3</td><td>11.6</td><td>14.3</td><td>7.1</td><td>6.4</td></tr>",
                            "<tr><td>20</td><td>12.1</td><td>12.4</td><td>14.2</td><td>10.6</td><td>10.3</td><td>5.7</td><td>8.4</td></tr>",
                            "<tr><td>21</td><td>9.1</td><td>12.6</td><td>11.4</td><td>9.4</td><td>7.3</td><td>5.7</td><td>10.2</td></tr>",
                            "<tr><td>22</td><td>9.1</td><td>10.0</td><td>9.8</td><td>12.1</td><td>6.0</td><td>5.7</td><td>10.2</td></tr>",
                            "<tr><td>23</td><td>8.8</td><td>8.6</td><td>9.6</td><td>10.6</td><td>7.6</td><td>5.3</td><td>8.3</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 10
        }
    ]
}