11주차_SQL_실습_3일차_1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "SQL 실습: 3일차-1",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/learn-programmers/programmers_kdt_II/blob/main/9%EC%A3%BC%EC%B0%A8_SQL_%EC%8B%A4%EC%8A%B5_3%EC%9D%BC%EC%B0%A8_1.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EVuF1_D9zWBV"
      },
      "source": [
        "주피터 SQL 엔진 설정"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wOS9-QY1amnt"
      },
      "source": [
        "%load_ext sql"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XehcNor8a-za",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 89
        },
        "outputId": "faaae6f1-4670-438a-b9c1-0806d36417ec"
      },
      "source": [
        "# ID와 PW를 자신의 환경에 맞게 수정\n",
        "%sql postgresql://guest:Guest1!*@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.6/dist-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.\n",
            "  \"\"\")\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'Connected: guest@prod'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 2
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bmS7kKUHzi1S"
      },
      "source": [
        "다양한 SELECT 실행해보기"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mPtvJEvubNoV",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 273
        },
        "outputId": "53744810-9d4a-43de-ee9d-e540c8303e62"
      },
      "source": [
        "%%sql\n",
        "\n",
        "SELECT * FROM raw_data.session_timestamp LIMIT 10"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "10 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>sessionid</th>\n",
              "            <th>ts</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>c41dd99a69df04044aa4e33ece9c9249</td>\n",
              "            <td>2019-05-01 00:35:59.897000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>fdc0eb412a84fa549afe68373d9087e9</td>\n",
              "            <td>2019-05-01 02:53:48.503000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>0a54b19a13b6712dc04d1b49215423d8</td>\n",
              "            <td>2019-05-01 12:18:26.637000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>a914ecef9c12ffdb9bede64bb703d877</td>\n",
              "            <td>2019-05-01 13:41:29.267000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>05ae14d7ae387b93370d142d82220f1b</td>\n",
              "            <td>2019-05-01 14:17:54.083000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>eddeb82df22554fa67c641e3f8a25566</td>\n",
              "            <td>2019-05-01 14:42:50.487000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>4c4ea5258ef3fb3fb1fc48fee9b4408c</td>\n",
              "            <td>2019-05-01 15:08:15.557000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>8ae1da0fe37c98412768453f82490da2</td>\n",
              "            <td>2019-05-01 15:20:27.377000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>60131a2a3f223dc8f4753bcc5771660c</td>\n",
              "            <td>2019-05-01 15:53:07.017000</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>90f4760fcc9b69c13da7368c5c2917f3</td>\n",
              "            <td>2019-05-01 16:15:43.423000</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[('c41dd99a69df04044aa4e33ece9c9249', datetime.datetime(2019, 5, 1, 0, 35, 59, 897000)),\n",
              " ('fdc0eb412a84fa549afe68373d9087e9', datetime.datetime(2019, 5, 1, 2, 53, 48, 503000)),\n",
              " ('0a54b19a13b6712dc04d1b49215423d8', datetime.datetime(2019, 5, 1, 12, 18, 26, 637000)),\n",
              " ('a914ecef9c12ffdb9bede64bb703d877', datetime.datetime(2019, 5, 1, 13, 41, 29, 267000)),\n",
              " ('05ae14d7ae387b93370d142d82220f1b', datetime.datetime(2019, 5, 1, 14, 17, 54, 83000)),\n",
              " ('eddeb82df22554fa67c641e3f8a25566', datetime.datetime(2019, 5, 1, 14, 42, 50, 487000)),\n",
              " ('4c4ea5258ef3fb3fb1fc48fee9b4408c', datetime.datetime(2019, 5, 1, 15, 8, 15, 557000)),\n",
              " ('8ae1da0fe37c98412768453f82490da2', datetime.datetime(2019, 5, 1, 15, 20, 27, 377000)),\n",
              " ('60131a2a3f223dc8f4753bcc5771660c', datetime.datetime(2019, 5, 1, 15, 53, 7, 17000)),\n",
              " ('90f4760fcc9b69c13da7368c5c2917f3', datetime.datetime(2019, 5, 1, 16, 15, 43, 423000))]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 273
        },
        "id": "38FMxXuK6yxl",
        "outputId": "b1f070fd-d684-4863-bde4-8b1e59efbf27"
      },
      "source": [
        "%%sql\n",
        "\n",
        "SELECT DATE(ts) date, sessionID FROM raw_data.session_timestamp LIMIT 10;"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "10 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>date</th>\n",
              "            <th>sessionid</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>7cdace91c487558e27ce54df7cdb299c</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>94f192dee566b018e0acf31e1f99a2d9</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>7ed2d3454c5eea71148b11d0c25104ff</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>f1daf122cde863010844459363cd31db</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>fd0efcca272f704a760c3b61dcc70fd0</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>8804f94e16ba5b680e239a554a08f7d2</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>c5f441cd5f43eb2f2c024e1f8b5d00cd</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>d5fcc35c94879a4afad61cacca56192c</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>3d191ef6e236bd1b9bdb9ff4743c47fe</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>c17028c9b6e0c5deaad29665d582284a</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(datetime.date(2019, 5, 1), '7cdace91c487558e27ce54df7cdb299c'),\n",
              " (datetime.date(2019, 5, 1), '94f192dee566b018e0acf31e1f99a2d9'),\n",
              " (datetime.date(2019, 5, 1), '7ed2d3454c5eea71148b11d0c25104ff'),\n",
              " (datetime.date(2019, 5, 1), 'f1daf122cde863010844459363cd31db'),\n",
              " (datetime.date(2019, 5, 1), 'fd0efcca272f704a760c3b61dcc70fd0'),\n",
              " (datetime.date(2019, 5, 1), '8804f94e16ba5b680e239a554a08f7d2'),\n",
              " (datetime.date(2019, 5, 1), 'c5f441cd5f43eb2f2c024e1f8b5d00cd'),\n",
              " (datetime.date(2019, 5, 1), 'd5fcc35c94879a4afad61cacca56192c'),\n",
              " (datetime.date(2019, 5, 1), '3d191ef6e236bd1b9bdb9ff4743c47fe'),\n",
              " (datetime.date(2019, 5, 1), 'c17028c9b6e0c5deaad29665d582284a')]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 273
        },
        "id": "4P6TWcbGl2j7",
        "outputId": "831d5885-f5b4-40c6-e3de-651e43003e84"
      },
      "source": [
        "%%sql\n",
        "\n",
        "SELECT DATE(ts) date, sessionID FROM raw_data.session_timestamp ORDER BY ts \n",
        "desc LIMIT 10;"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "10 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>date</th>\n",
              "            <th>sessionid</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>6309ff4befccf8ba77b16141fab763c6</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>42daed3b750cc5c6270636fddee0486d</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>398c674511e98d3e9bd40ba5bfa67af8</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>1e65c9f788d6382abc0ee60886e7fa4a</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>ec6d363a01a8a0691d24b8556bc1fb61</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>8f48bd8292fc4540404dc9dae06175e6</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>89a76004709bb668a8aefb6306a6aed1</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>9cfaefd1e81f637fad6330ff16eb1f39</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>bbf2f1c020c5e39734c73223784bd7b4</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-30</td>\n",
              "            <td>bac82af401b714e895c9c46af11f76ea</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(datetime.date(2019, 11, 30), '6309ff4befccf8ba77b16141fab763c6'),\n",
              " (datetime.date(2019, 11, 30), '42daed3b750cc5c6270636fddee0486d'),\n",
              " (datetime.date(2019, 11, 30), '398c674511e98d3e9bd40ba5bfa67af8'),\n",
              " (datetime.date(2019, 11, 30), '1e65c9f788d6382abc0ee60886e7fa4a'),\n",
              " (datetime.date(2019, 11, 30), 'ec6d363a01a8a0691d24b8556bc1fb61'),\n",
              " (datetime.date(2019, 11, 30), '8f48bd8292fc4540404dc9dae06175e6'),\n",
              " (datetime.date(2019, 11, 30), '89a76004709bb668a8aefb6306a6aed1'),\n",
              " (datetime.date(2019, 11, 30), '9cfaefd1e81f637fad6330ff16eb1f39'),\n",
              " (datetime.date(2019, 11, 30), 'bbf2f1c020c5e39734c73223784bd7b4'),\n",
              " (datetime.date(2019, 11, 30), 'bac82af401b714e895c9c46af11f76ea')]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 273
        },
        "id": "ILNS7QDN63mu",
        "outputId": "2394101d-bc24-44c2-a4fd-c290824be9c5"
      },
      "source": [
        "%%sql\n",
        "\n",
        "SELECT DATE(ts) date, COUNT(sessionID)\n",
        "FROM raw_data.session_timestamp\n",
        "GROUP BY 1\n",
        "LIMIT 10;"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "10 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>date</th>\n",
              "            <th>count</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>147</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-02</td>\n",
              "            <td>161</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-03</td>\n",
              "            <td>150</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-04</td>\n",
              "            <td>142</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-06</td>\n",
              "            <td>164</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-07</td>\n",
              "            <td>180</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-08</td>\n",
              "            <td>161</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-10</td>\n",
              "            <td>176</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-12</td>\n",
              "            <td>171</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-13</td>\n",
              "            <td>178</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(datetime.date(2019, 5, 1), 147),\n",
              " (datetime.date(2019, 5, 2), 161),\n",
              " (datetime.date(2019, 5, 3), 150),\n",
              " (datetime.date(2019, 5, 4), 142),\n",
              " (datetime.date(2019, 5, 6), 164),\n",
              " (datetime.date(2019, 5, 7), 180),\n",
              " (datetime.date(2019, 5, 8), 161),\n",
              " (datetime.date(2019, 5, 10), 176),\n",
              " (datetime.date(2019, 5, 12), 171),\n",
              " (datetime.date(2019, 5, 13), 178)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 273
        },
        "id": "9T0H93a7mxHF",
        "outputId": "86315e82-6024-42bb-d66b-45636ffe3cef"
      },
      "source": [
        "%%sql\n",
        "\n",
        "SELECT DATE(ts) date, COUNT(sessionID)\n",
        "FROM raw_data.session_timestamp\n",
        "GROUP BY 1\n",
        "ORDER BY 2 DESC\n",
        "LIMIT 10;"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "10 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>date</th>\n",
              "            <th>count</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2019-10-23</td>\n",
              "            <td>678</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-13</td>\n",
              "            <td>671</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-12</td>\n",
              "            <td>668</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-10-11</td>\n",
              "            <td>665</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-10-29</td>\n",
              "            <td>660</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-10-21</td>\n",
              "            <td>653</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-05</td>\n",
              "            <td>652</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-11</td>\n",
              "            <td>651</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-10-14</td>\n",
              "            <td>648</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-11-07</td>\n",
              "            <td>645</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(datetime.date(2019, 10, 23), 678),\n",
              " (datetime.date(2019, 11, 13), 671),\n",
              " (datetime.date(2019, 11, 12), 668),\n",
              " (datetime.date(2019, 10, 11), 665),\n",
              " (datetime.date(2019, 10, 29), 660),\n",
              " (datetime.date(2019, 10, 21), 653),\n",
              " (datetime.date(2019, 11, 5), 652),\n",
              " (datetime.date(2019, 11, 11), 651),\n",
              " (datetime.date(2019, 10, 14), 648),\n",
              " (datetime.date(2019, 11, 7), 645)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 273
        },
        "id": "afZBLY9d7IaR",
        "outputId": "8e963098-8c86-4310-a995-095ba2068541"
      },
      "source": [
        "%%sql\n",
        "\n",
        "SELECT DATE(ts) date, COUNT(sessionID)\n",
        "FROM raw_data.session_timestamp\n",
        "GROUP BY 1\n",
        "ORDER BY 1\n",
        "LIMIT 10; -- DESC"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "10 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>date</th>\n",
              "            <th>count</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>147</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-02</td>\n",
              "            <td>161</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-03</td>\n",
              "            <td>150</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-04</td>\n",
              "            <td>142</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-05</td>\n",
              "            <td>144</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-06</td>\n",
              "            <td>164</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-07</td>\n",
              "            <td>180</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-08</td>\n",
              "            <td>161</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-09</td>\n",
              "            <td>169</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-10</td>\n",
              "            <td>176</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(datetime.date(2019, 5, 1), 147),\n",
              " (datetime.date(2019, 5, 2), 161),\n",
              " (datetime.date(2019, 5, 3), 150),\n",
              " (datetime.date(2019, 5, 4), 142),\n",
              " (datetime.date(2019, 5, 5), 144),\n",
              " (datetime.date(2019, 5, 6), 164),\n",
              " (datetime.date(2019, 5, 7), 180),\n",
              " (datetime.date(2019, 5, 8), 161),\n",
              " (datetime.date(2019, 5, 9), 169),\n",
              " (datetime.date(2019, 5, 10), 176)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7icH7h1W7RUn"
      },
      "source": [
        "JOIN에 대해 배워보자. 일별 방문 유니크한 사용자의 수를 계산하고 싶다면? "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 273
        },
        "id": "6D8ht2Fy7RAq",
        "outputId": "57f28634-d534-462a-d404-c3946b77ff88"
      },
      "source": [
        "# raw_data.user_session_channel과 raw_data.session_timestamp 테이블의 조인이 필요\n",
        "%%sql\n",
        "\n",
        "SELECT DATE(st.ts) date, COUNT(usc.userID)\n",
        "FROM raw_data.session_timestamp st\n",
        "JOIN raw_data.user_session_channel usc ON st.sessionID = usc.sessionID\n",
        "GROUP BY 1\n",
        "ORDER BY 1\n",
        "LIMIT 10;"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "10 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>date</th>\n",
              "            <th>count</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>147</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-02</td>\n",
              "            <td>161</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-03</td>\n",
              "            <td>150</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-04</td>\n",
              "            <td>142</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-05</td>\n",
              "            <td>144</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-06</td>\n",
              "            <td>164</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-07</td>\n",
              "            <td>180</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-08</td>\n",
              "            <td>161</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-09</td>\n",
              "            <td>169</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-10</td>\n",
              "            <td>176</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(datetime.date(2019, 5, 1), 147),\n",
              " (datetime.date(2019, 5, 2), 161),\n",
              " (datetime.date(2019, 5, 3), 150),\n",
              " (datetime.date(2019, 5, 4), 142),\n",
              " (datetime.date(2019, 5, 5), 144),\n",
              " (datetime.date(2019, 5, 6), 164),\n",
              " (datetime.date(2019, 5, 7), 180),\n",
              " (datetime.date(2019, 5, 8), 161),\n",
              " (datetime.date(2019, 5, 9), 169),\n",
              " (datetime.date(2019, 5, 10), 176)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 273
        },
        "id": "Wak8gB2mAwYj",
        "outputId": "55164089-2684-4798-c464-0163a84edeea"
      },
      "source": [
        "# raw_data.user_session_channel과 raw_data.session_timestamp 테이블의 조인이 필요\n",
        "%%sql\n",
        "\n",
        "SELECT DATE(st.ts) date, COUNT(DISTINCT usc.userID)\n",
        "FROM raw_data.session_timestamp st\n",
        "JOIN raw_data.user_session_channel usc ON st.sessionID = usc.sessionID\n",
        "GROUP BY 1\n",
        "ORDER BY 1\n",
        "LIMIT 10;"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "10 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>date</th>\n",
              "            <th>count</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2019-05-01</td>\n",
              "            <td>119</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-02</td>\n",
              "            <td>127</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-03</td>\n",
              "            <td>130</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-04</td>\n",
              "            <td>122</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-05</td>\n",
              "            <td>124</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-06</td>\n",
              "            <td>133</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-07</td>\n",
              "            <td>147</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-08</td>\n",
              "            <td>135</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-09</td>\n",
              "            <td>145</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2019-05-10</td>\n",
              "            <td>144</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(datetime.date(2019, 5, 1), 119),\n",
              " (datetime.date(2019, 5, 2), 127),\n",
              " (datetime.date(2019, 5, 3), 130),\n",
              " (datetime.date(2019, 5, 4), 122),\n",
              " (datetime.date(2019, 5, 5), 124),\n",
              " (datetime.date(2019, 5, 6), 133),\n",
              " (datetime.date(2019, 5, 7), 147),\n",
              " (datetime.date(2019, 5, 8), 135),\n",
              " (datetime.date(2019, 5, 9), 145),\n",
              " (datetime.date(2019, 5, 10), 144)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 193
        },
        "id": "5ZqLeBtU7OKo",
        "outputId": "96c33e3c-608c-4038-ba2c-2399fbbc2b68"
      },
      "source": [
        "%%sql\n",
        "\n",
        "SELECT channel, count(st.sessionID)\n",
        "FROM raw_data.session_timestamp st\n",
        "JOIN raw_data.user_session_channel usc ON st.sessionID = usc.sessionID\n",
        "GROUP BY 1\n",
        "ORDER BY 1"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "6 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>channel</th>\n",
              "            <th>count</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>Facebook</td>\n",
              "            <td>16791</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Google</td>\n",
              "            <td>16982</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Instagram</td>\n",
              "            <td>16831</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Naver</td>\n",
              "            <td>16921</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Organic</td>\n",
              "            <td>16904</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Youtube</td>\n",
              "            <td>17091</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[('Facebook', 16791),\n",
              " ('Google', 16982),\n",
              " ('Instagram', 16831),\n",
              " ('Naver', 16921),\n",
              " ('Organic', 16904),\n",
              " ('Youtube', 17091)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 93
        },
        "id": "B0aDTmJ5ChIW",
        "outputId": "1b2c6f65-f238-461d-e5e2-c64a928c05d8"
      },
      "source": [
        "%%sql\n",
        "\n",
        "SELECT COUNT(1) FROM raw_data.user_session_channel \n",
        "WHERE channel ilike '%o%'"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "1 rows affected.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>count</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>67768</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(67768,)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Kka4fmL_A6C_"
      },
      "source": [
        "판다스와 연동하는 방법"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2-us2zrscs25",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6c40f887-287f-452b-dc11-f383993f352d"
      },
      "source": [
        "result = %sql SELECT * FROM raw_data.user_session_channel\n",
        "df = result.DataFrame()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "101520 rows affected.\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7AmUNAj8c3fZ",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 203
        },
        "outputId": "2d2b0d36-9896-41c0-a772-481f9d439a7e"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>userid</th>\n",
              "      <th>sessionid</th>\n",
              "      <th>channel</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>184</td>\n",
              "      <td>c41dd99a69df04044aa4e33ece9c9249</td>\n",
              "      <td>Naver</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>80</td>\n",
              "      <td>fdc0eb412a84fa549afe68373d9087e9</td>\n",
              "      <td>Organic</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>251</td>\n",
              "      <td>0a54b19a13b6712dc04d1b49215423d8</td>\n",
              "      <td>Facebook</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>264</td>\n",
              "      <td>a914ecef9c12ffdb9bede64bb703d877</td>\n",
              "      <td>Google</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>744</td>\n",
              "      <td>05ae14d7ae387b93370d142d82220f1b</td>\n",
              "      <td>Facebook</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   userid                         sessionid   channel\n",
              "0     184  c41dd99a69df04044aa4e33ece9c9249     Naver\n",
              "1      80  fdc0eb412a84fa549afe68373d9087e9   Organic\n",
              "2     251  0a54b19a13b6712dc04d1b49215423d8  Facebook\n",
              "3     264  a914ecef9c12ffdb9bede64bb703d877    Google\n",
              "4     744  05ae14d7ae387b93370d142d82220f1b  Facebook"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zx3dWMB4BSo7",
        "outputId": "20396427-80a9-4299-f27c-e602658af36f"
      },
      "source": [
        "df.groupby([\"channel\"]).size()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "channel\n",
              "Facebook     16791\n",
              "Google       16982\n",
              "Instagram    16831\n",
              "Naver        16921\n",
              "Organic      16904\n",
              "Youtube      17091\n",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "mpXSqwAgEV3B",
        "outputId": "36fd76a7-1cdf-44cb-f15a-99fa17ac651a"
      },
      "source": [
        "df.groupby([\"channel\"])[\"sessionid\"].count()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "channel\n",
              "Facebook     16791\n",
              "Google       16982\n",
              "Instagram    16831\n",
              "Naver        16921\n",
              "Organic      16904\n",
              "Youtube      17091\n",
              "Name: sessionid, dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fVdRUALFC-to",
        "outputId": "f15f8de0-3126-4c28-f126-51d20f94df30"
      },
      "source": [
        "result = %sql SELECT * FROM raw_data.session_timestamp\n",
        "df_st = result.DataFrame()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * postgresql://guest:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/prod\n",
            "101520 rows affected.\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kCLUOSUBBWu5"
      },
      "source": [
        "df_st['date'] = df_st['ts'].apply(lambda x: \"%d-%02d-%02d\" % (x.year, x.month, x.day))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 203
        },
        "id": "Xadp1GIBp6st",
        "outputId": "ee569f8d-b61f-4ca1-8ac6-a7cdad68c89b"
      },
      "source": [
        "df_st.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>sessionid</th>\n",
              "      <th>ts</th>\n",
              "      <th>date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>7cdace91c487558e27ce54df7cdb299c</td>\n",
              "      <td>2019-05-01 00:13:11.783</td>\n",
              "      <td>2019-05-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>94f192dee566b018e0acf31e1f99a2d9</td>\n",
              "      <td>2019-05-01 00:49:46.073</td>\n",
              "      <td>2019-05-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>7ed2d3454c5eea71148b11d0c25104ff</td>\n",
              "      <td>2019-05-01 10:18:43.210</td>\n",
              "      <td>2019-05-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>f1daf122cde863010844459363cd31db</td>\n",
              "      <td>2019-05-01 13:10:56.413</td>\n",
              "      <td>2019-05-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>fd0efcca272f704a760c3b61dcc70fd0</td>\n",
              "      <td>2019-05-01 13:45:19.793</td>\n",
              "      <td>2019-05-01</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                          sessionid                      ts        date\n",
              "0  7cdace91c487558e27ce54df7cdb299c 2019-05-01 00:13:11.783  2019-05-01\n",
              "1  94f192dee566b018e0acf31e1f99a2d9 2019-05-01 00:49:46.073  2019-05-01\n",
              "2  7ed2d3454c5eea71148b11d0c25104ff 2019-05-01 10:18:43.210  2019-05-01\n",
              "3  f1daf122cde863010844459363cd31db 2019-05-01 13:10:56.413  2019-05-01\n",
              "4  fd0efcca272f704a760c3b61dcc70fd0 2019-05-01 13:45:19.793  2019-05-01"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SxBArtJ-DPwO",
        "outputId": "d0360338-27bf-444c-cdc9-0b99e3f17967"
      },
      "source": [
        "df_st.groupby([\"date\"]).size()  # .sort_values([\"date\"])"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date\n",
              "2019-05-01    147\n",
              "2019-05-02    161\n",
              "2019-05-03    150\n",
              "2019-05-04    142\n",
              "2019-05-05    144\n",
              "             ... \n",
              "2019-11-26    633\n",
              "2019-11-27    617\n",
              "2019-11-28    516\n",
              "2019-11-29    531\n",
              "2019-11-30    562\n",
              "Length: 214, dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 417
        },
        "id": "izdTN1ukDU9M",
        "outputId": "9792313b-afa0-45d8-9202-c86cf6c1b413"
      },
      "source": [
        "df_st.groupby([\"date\"])[\"sessionid\"].count().reset_index(name='count').sort_values(\"date\", ascending=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>count</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>213</th>\n",
              "      <td>2019-11-30</td>\n",
              "      <td>562</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>212</th>\n",
              "      <td>2019-11-29</td>\n",
              "      <td>531</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>211</th>\n",
              "      <td>2019-11-28</td>\n",
              "      <td>516</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>210</th>\n",
              "      <td>2019-11-27</td>\n",
              "      <td>617</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>209</th>\n",
              "      <td>2019-11-26</td>\n",
              "      <td>633</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2019-05-05</td>\n",
              "      <td>144</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2019-05-04</td>\n",
              "      <td>142</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2019-05-03</td>\n",
              "      <td>150</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2019-05-02</td>\n",
              "      <td>161</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2019-05-01</td>\n",
              "      <td>147</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>214 rows × 2 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "           date  count\n",
              "213  2019-11-30    562\n",
              "212  2019-11-29    531\n",
              "211  2019-11-28    516\n",
              "210  2019-11-27    617\n",
              "209  2019-11-26    633\n",
              "..          ...    ...\n",
              "4    2019-05-05    144\n",
              "3    2019-05-04    142\n",
              "2    2019-05-03    150\n",
              "1    2019-05-02    161\n",
              "0    2019-05-01    147\n",
              "\n",
              "[214 rows x 2 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aRxG7LMSEoDb"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}