SQL_sturtup_for_reading_lovers.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "ExecuteTimeLog": [
      {
        "duration": 728,
        "start_time": "2021-09-24T18:05:04.026Z"
      },
      {
        "duration": 264,
        "start_time": "2021-09-24T18:05:04.756Z"
      },
      {
        "duration": 71,
        "start_time": "2021-09-24T18:05:05.023Z"
      },
      {
        "duration": 71,
        "start_time": "2021-09-24T18:05:05.096Z"
      },
      {
        "duration": 74,
        "start_time": "2021-09-24T18:05:05.169Z"
      },
      {
        "duration": 75,
        "start_time": "2021-09-24T18:05:05.245Z"
      },
      {
        "duration": 74,
        "start_time": "2021-09-24T18:05:05.322Z"
      },
      {
        "duration": 86,
        "start_time": "2021-09-24T18:05:05.398Z"
      },
      {
        "duration": 72,
        "start_time": "2021-09-24T18:05:05.486Z"
      },
      {
        "duration": 76,
        "start_time": "2021-09-24T18:05:05.561Z"
      },
      {
        "duration": 73,
        "start_time": "2021-09-24T18:05:05.639Z"
      },
      {
        "duration": 108,
        "start_time": "2021-09-24T18:06:11.273Z"
      },
      {
        "duration": 110,
        "start_time": "2021-09-24T18:06:21.592Z"
      },
      {
        "duration": 319,
        "start_time": "2021-09-24T18:14:36.123Z"
      },
      {
        "duration": 711,
        "start_time": "2021-09-24T18:14:42.744Z"
      },
      {
        "duration": 239,
        "start_time": "2021-09-24T18:14:43.457Z"
      },
      {
        "duration": 67,
        "start_time": "2021-09-24T18:14:43.698Z"
      },
      {
        "duration": 68,
        "start_time": "2021-09-24T18:14:43.767Z"
      },
      {
        "duration": 64,
        "start_time": "2021-09-24T18:14:43.837Z"
      },
      {
        "duration": 65,
        "start_time": "2021-09-24T18:14:43.903Z"
      },
      {
        "duration": 64,
        "start_time": "2021-09-24T18:14:43.970Z"
      },
      {
        "duration": 101,
        "start_time": "2021-09-24T18:14:44.036Z"
      },
      {
        "duration": 65,
        "start_time": "2021-09-24T18:14:44.138Z"
      },
      {
        "duration": 68,
        "start_time": "2021-09-24T18:14:44.205Z"
      },
      {
        "duration": 66,
        "start_time": "2021-09-24T18:14:44.274Z"
      },
      {
        "duration": 84,
        "start_time": "2021-09-24T18:20:53.808Z"
      },
      {
        "duration": 3,
        "start_time": "2021-09-24T18:21:59.357Z"
      },
      {
        "duration": 95,
        "start_time": "2021-09-24T18:22:23.281Z"
      },
      {
        "duration": 92,
        "start_time": "2021-09-24T18:22:58.573Z"
      },
      {
        "duration": 89,
        "start_time": "2021-09-24T18:24:46.756Z"
      },
      {
        "duration": 88,
        "start_time": "2021-09-24T18:27:13.968Z"
      },
      {
        "duration": 81,
        "start_time": "2021-09-24T18:28:57.654Z"
      },
      {
        "duration": 384,
        "start_time": "2021-09-24T18:29:34.768Z"
      },
      {
        "duration": 88,
        "start_time": "2021-09-24T18:30:48.562Z"
      },
      {
        "duration": 346,
        "start_time": "2021-09-24T18:31:38.642Z"
      },
      {
        "duration": 346,
        "start_time": "2021-09-24T18:31:48.102Z"
      },
      {
        "duration": 817,
        "start_time": "2021-09-24T18:34:51.361Z"
      },
      {
        "duration": 369,
        "start_time": "2021-09-24T18:35:18.239Z"
      },
      {
        "duration": 322,
        "start_time": "2021-09-24T18:35:27.063Z"
      },
      {
        "duration": 324,
        "start_time": "2021-09-24T18:36:08.269Z"
      },
      {
        "duration": 362,
        "start_time": "2021-09-24T18:36:18.702Z"
      },
      {
        "duration": 99,
        "start_time": "2021-09-24T18:37:07.975Z"
      },
      {
        "duration": 99,
        "start_time": "2021-09-24T18:37:18.263Z"
      },
      {
        "duration": 101,
        "start_time": "2021-09-24T18:37:41.312Z"
      },
      {
        "duration": 329,
        "start_time": "2021-09-24T18:37:58.034Z"
      },
      {
        "duration": 348,
        "start_time": "2021-09-24T18:38:07.202Z"
      },
      {
        "duration": 389,
        "start_time": "2021-09-24T18:38:20.394Z"
      },
      {
        "duration": 82,
        "start_time": "2021-09-24T18:38:56.699Z"
      },
      {
        "duration": 98,
        "start_time": "2021-09-24T18:40:59.410Z"
      },
      {
        "duration": 98,
        "start_time": "2021-09-24T18:41:19.081Z"
      },
      {
        "duration": 98,
        "start_time": "2021-09-24T18:46:32.922Z"
      },
      {
        "duration": 79,
        "start_time": "2021-09-24T18:55:17.613Z"
      }
    ],
    "colab": {
      "name": "SQL_sturtup for reading lovers.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.8"
    },
    "toc": {
      "base_numbering": 1,
      "nav_menu": {},
      "number_sections": true,
      "sideBar": true,
      "skip_h1_title": true,
      "title_cell": "Table of Contents",
      "title_sidebar": "Contents",
      "toc_cell": false,
      "toc_position": {},
      "toc_section_display": true,
      "toc_window_display": false
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LJz0yFqsCJlI"
      },
      "source": [
        "# SQL. Sturtup for reading lovers"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "srHCM3BgSWui"
      },
      "source": [
        "\n",
        "**Цель исследования:**\n",
        "\n",
        "сформулировать ценностное предложение для нового продукта сервиса для чтения книг по подписке.\n",
        "\n",
        "**Задача исследования:**\n",
        "\n",
        "проанализировать базу данных. "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YdFKHp-qTAIM"
      },
      "source": [
        "**Описание данных**\n",
        "\n",
        "**Таблица `books`**\n",
        "\n",
        "Содержит данные о книгах:\n",
        "\n",
        "- `book_id` — идентификатор книги;\n",
        "\n",
        "- `author_id` — идентификатор автора;\n",
        "\n",
        "- `title` — название книги;\n",
        "\n",
        "- `num_pages` — количество страниц;\n",
        "\n",
        "- `publication_date` — дата публикации книги;\n",
        "\n",
        "- `publisher_id` — идентификатор издателя.\n",
        "\n",
        "\n",
        "**Таблица `authors`**\n",
        "\n",
        "Содержит данные об авторах:\n",
        "\n",
        "- `author_id` — идентификатор автора;\n",
        "\n",
        "- `author` — имя автора.\n",
        "\n",
        "**Таблица `publishers`**\n",
        "\n",
        "Содержит данные об издательствах:\n",
        "\n",
        "- `publisher_id` — идентификатор издательства;\n",
        "\n",
        "- `publisher` — название издательства;\n",
        "\n",
        "**Таблица `ratings`**\n",
        "\n",
        "Содержит данные о пользовательских оценках книг:\n",
        "\n",
        "- `rating_id` — идентификатор оценки;\n",
        "\n",
        "- `book_id` — идентификатор книги;\n",
        "\n",
        "- `username` — имя пользователя, оставившего оценку;\n",
        "\n",
        "- `rating` — оценка книги.\n",
        "\n",
        "**Таблица `reviews`**\n",
        "\n",
        "Содержит данные о пользовательских обзорах на книги:\n",
        "\n",
        "- `review_id` — идентификатор обзора;\n",
        "\n",
        "- `book_id` — идентификатор книги;\n",
        "\n",
        "- `username` — имя пользователя, написавшего обзор;\n",
        "\n",
        "- `text` — текст обзора."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f76aWOwUThVB"
      },
      "source": [
        "**ШАГ 1. Импорт библиотек и загрузка данных**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zNJ5N2vdF3yN"
      },
      "source": [
        "import pandas as pd \n",
        "from sqlalchemy import create_engine \n",
        "\n",
        "db_config = {'user': '...',  \n",
        "                     'pwd': 'Sdf4$2...',  \n",
        "                     'host': 'rc1b....net', \n",
        "                     'port': 6432, \n",
        "                     'db': 'data-anal...'}  \n",
        "connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(db_config['user'], \n",
        "                                                                                    db_config['pwd'], \n",
        "                                                                         db_config['host'], \n",
        "                                                                                    db_config['port'], \n",
        "                                                                                    db_config['db']) \n",
        " \n",
        "engine = create_engine(connection_string, connect_args={'sslmode':'require'})"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "U2IgAThkGADS",
        "outputId": "d4625b84-8d15-4f01-fa97-614ed5f43705"
      },
      "source": [
        "books = ''' SELECT * \n",
        "            FROM books\n",
        "            LIMIT 5'''\n",
        "pd.io.sql.read_sql(books, con = engine)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>book_id</th>\n",
              "      <th>author_id</th>\n",
              "      <th>title</th>\n",
              "      <th>num_pages</th>\n",
              "      <th>publication_date</th>\n",
              "      <th>publisher_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>546</td>\n",
              "      <td>'Salem's Lot</td>\n",
              "      <td>594</td>\n",
              "      <td>2005-11-01</td>\n",
              "      <td>93</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>465</td>\n",
              "      <td>1 000 Places to See Before You Die</td>\n",
              "      <td>992</td>\n",
              "      <td>2003-05-22</td>\n",
              "      <td>336</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>407</td>\n",
              "      <td>13 Little Blue Envelopes (Little Blue Envelope...</td>\n",
              "      <td>322</td>\n",
              "      <td>2010-12-21</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>4</td>\n",
              "      <td>82</td>\n",
              "      <td>1491: New Revelations of the Americas Before C...</td>\n",
              "      <td>541</td>\n",
              "      <td>2006-10-10</td>\n",
              "      <td>309</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>5</td>\n",
              "      <td>125</td>\n",
              "      <td>1776</td>\n",
              "      <td>386</td>\n",
              "      <td>2006-07-04</td>\n",
              "      <td>268</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   book_id  author_id                                              title  \\\n",
              "0        1        546                                       'Salem's Lot   \n",
              "1        2        465                 1 000 Places to See Before You Die   \n",
              "2        3        407  13 Little Blue Envelopes (Little Blue Envelope...   \n",
              "3        4         82  1491: New Revelations of the Americas Before C...   \n",
              "4        5        125                                               1776   \n",
              "\n",
              "   num_pages publication_date  publisher_id  \n",
              "0        594       2005-11-01            93  \n",
              "1        992       2003-05-22           336  \n",
              "2        322       2010-12-21           135  \n",
              "3        541       2006-10-10           309  \n",
              "4        386       2006-07-04           268  "
            ]
          },
          "execution_count": 3,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "pDqJKJlFGAps",
        "outputId": "ebccf8d0-d20d-478d-d0a2-998e52e99e96"
      },
      "source": [
        "authors = ''' SELECT * \n",
        "            FROM authors\n",
        "            LIMIT 5'''\n",
        "pd.io.sql.read_sql(authors, con = engine)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>author_id</th>\n",
              "      <th>author</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>A.S. Byatt</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>Aesop/Laura Harris/Laura Gibbs</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>Agatha Christie</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>4</td>\n",
              "      <td>Alan Brennert</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>5</td>\n",
              "      <td>Alan Moore/David   Lloyd</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   author_id                          author\n",
              "0          1                      A.S. Byatt\n",
              "1          2  Aesop/Laura Harris/Laura Gibbs\n",
              "2          3                 Agatha Christie\n",
              "3          4                   Alan Brennert\n",
              "4          5        Alan Moore/David   Lloyd"
            ]
          },
          "execution_count": 4,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "fbLISxj2UWAi",
        "outputId": "2fbbfa86-1573-4bff-d322-2fb96112c557"
      },
      "source": [
        "publishers = ''' SELECT * \n",
        "            FROM publishers\n",
        "            LIMIT 5'''\n",
        "pd.io.sql.read_sql(publishers, con = engine)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>publisher_id</th>\n",
              "      <th>publisher</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>Ace</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>Ace Book</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>Ace Books</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>4</td>\n",
              "      <td>Ace Hardcover</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>5</td>\n",
              "      <td>Addison Wesley Publishing Company</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   publisher_id                          publisher\n",
              "0             1                                Ace\n",
              "1             2                           Ace Book\n",
              "2             3                          Ace Books\n",
              "3             4                      Ace Hardcover\n",
              "4             5  Addison Wesley Publishing Company"
            ]
          },
          "execution_count": 5,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "4aq5xly_UYHJ",
        "outputId": "17e876c2-b8cc-45f8-a756-81903fe891a5"
      },
      "source": [
        "ratings = ''' SELECT * \n",
        "            FROM ratings\n",
        "            LIMIT 5'''\n",
        "pd.io.sql.read_sql(ratings, con = engine)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>rating_id</th>\n",
              "      <th>book_id</th>\n",
              "      <th>username</th>\n",
              "      <th>rating</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>ryanfranco</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>grantpatricia</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>1</td>\n",
              "      <td>brandtandrea</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>4</td>\n",
              "      <td>2</td>\n",
              "      <td>lorichen</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>5</td>\n",
              "      <td>2</td>\n",
              "      <td>mariokeller</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   rating_id  book_id       username  rating\n",
              "0          1        1     ryanfranco       4\n",
              "1          2        1  grantpatricia       2\n",
              "2          3        1   brandtandrea       5\n",
              "3          4        2       lorichen       3\n",
              "4          5        2    mariokeller       2"
            ]
          },
          "execution_count": 6,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "yn4uletoUfa4",
        "outputId": "dc39c0ee-d019-4a7f-ef8f-40b797855cc7"
      },
      "source": [
        "reviews = ''' SELECT * \n",
        "            FROM reviews\n",
        "            LIMIT 5'''\n",
        "pd.io.sql.read_sql(reviews, con = engine)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>review_id</th>\n",
              "      <th>book_id</th>\n",
              "      <th>username</th>\n",
              "      <th>text</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>brandtandrea</td>\n",
              "      <td>Mention society tell send professor analysis. ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>ryanfranco</td>\n",
              "      <td>Foot glass pretty audience hit themselves. Amo...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>2</td>\n",
              "      <td>lorichen</td>\n",
              "      <td>Listen treat keep worry. Miss husband tax but ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>4</td>\n",
              "      <td>3</td>\n",
              "      <td>johnsonamanda</td>\n",
              "      <td>Finally month interesting blue could nature cu...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>5</td>\n",
              "      <td>3</td>\n",
              "      <td>scotttamara</td>\n",
              "      <td>Nation purpose heavy give wait song will. List...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   review_id  book_id       username  \\\n",
              "0          1        1   brandtandrea   \n",
              "1          2        1     ryanfranco   \n",
              "2          3        2       lorichen   \n",
              "3          4        3  johnsonamanda   \n",
              "4          5        3    scotttamara   \n",
              "\n",
              "                                                text  \n",
              "0  Mention society tell send professor analysis. ...  \n",
              "1  Foot glass pretty audience hit themselves. Amo...  \n",
              "2  Listen treat keep worry. Miss husband tax but ...  \n",
              "3  Finally month interesting blue could nature cu...  \n",
              "4  Nation purpose heavy give wait song will. List...  "
            ]
          },
          "execution_count": 7,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GEkrBd7_wqCB"
      },
      "source": [
        "Данные загружены и прочитаны."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EtKHHAORwt9f"
      },
      "source": [
        "**Количество книг, вышедших после 1 января 2000 года.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "id": "haAJAhdlwtJ5",
        "outputId": "6537191c-57be-4f66-fe2a-05f87befc9a2"
      },
      "source": [
        "books_number = '''SELECT \n",
        "                      COUNT(book_id) count_of_books\n",
        "                  FROM \n",
        "                      books \n",
        "                  WHERE \n",
        "                      publication_date > '01-01-2000';\n",
        "'''\n",
        "pd.io.sql.read_sql(books_number, con = engine)      "
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>count_of_books</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>819</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   count_of_books\n",
              "0             819"
            ]
          },
          "execution_count": 8,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZCHR_tvoxoVj"
      },
      "source": [
        "После 1 января 2000 года вышло 819 книг."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KXVDiVJDxyoy"
      },
      "source": [
        "**Количество обзоров и средняя оценка каждой книги.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GSayi_wFCJlO",
        "outputId": "bd30a5bf-b4b1-4437-d473-7330545cbd70"
      },
      "source": [
        "review_rating = '''SELECT\n",
        "                       books.book_id AS id,\n",
        "                       books.title AS title,\n",
        "                       COUNT(DISTINCT reviews.review_id) AS reviews_cnt,\n",
        "                       AVG(ratings.rating) AS avg_rating\n",
        "                   FROM \n",
        "                       books\n",
        "                   LEFT JOIN reviews ON  reviews.book_id = books.book_id\n",
        "                   LEFT JOIN ratings ON  ratings.book_id = books.book_id\n",
        "                   GROUP BY\n",
        "                       books.book_id\n",
        "                   ORDER BY\n",
        "                       reviews_cnt DESC;\n",
        "'''    \n",
        "    \n",
        "    \n",
        "pd.io.sql.read_sql(review_rating, con = engine)    "
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>id</th>\n",
              "      <th>title</th>\n",
              "      <th>reviews_cnt</th>\n",
              "      <th>avg_rating</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>948</td>\n",
              "      <td>Twilight (Twilight  #1)</td>\n",
              "      <td>7</td>\n",
              "      <td>3.662500</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>963</td>\n",
              "      <td>Water for Elephants</td>\n",
              "      <td>6</td>\n",
              "      <td>3.977273</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>734</td>\n",
              "      <td>The Glass Castle</td>\n",
              "      <td>6</td>\n",
              "      <td>4.206897</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>302</td>\n",
              "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
              "      <td>6</td>\n",
              "      <td>4.414634</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>695</td>\n",
              "      <td>The Curious Incident of the Dog in the Night-Time</td>\n",
              "      <td>6</td>\n",
              "      <td>4.081081</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>995</td>\n",
              "      <td>83</td>\n",
              "      <td>Anne Rice's The Vampire Lestat: A Graphic Novel</td>\n",
              "      <td>0</td>\n",
              "      <td>3.666667</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>996</td>\n",
              "      <td>808</td>\n",
              "      <td>The Natural Way to Draw</td>\n",
              "      <td>0</td>\n",
              "      <td>3.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>997</td>\n",
              "      <td>672</td>\n",
              "      <td>The Cat in the Hat and Other Dr. Seuss Favorites</td>\n",
              "      <td>0</td>\n",
              "      <td>5.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>998</td>\n",
              "      <td>221</td>\n",
              "      <td>Essential Tales and Poems</td>\n",
              "      <td>0</td>\n",
              "      <td>4.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>999</td>\n",
              "      <td>191</td>\n",
              "      <td>Disney's Beauty and the Beast (A Little Golden...</td>\n",
              "      <td>0</td>\n",
              "      <td>4.000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1000 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      id                                              title  reviews_cnt  \\\n",
              "0    948                            Twilight (Twilight  #1)            7   \n",
              "1    963                                Water for Elephants            6   \n",
              "2    734                                   The Glass Castle            6   \n",
              "3    302  Harry Potter and the Prisoner of Azkaban (Harr...            6   \n",
              "4    695  The Curious Incident of the Dog in the Night-Time            6   \n",
              "..   ...                                                ...          ...   \n",
              "995   83    Anne Rice's The Vampire Lestat: A Graphic Novel            0   \n",
              "996  808                            The Natural Way to Draw            0   \n",
              "997  672   The Cat in the Hat and Other Dr. Seuss Favorites            0   \n",
              "998  221                          Essential Tales and Poems            0   \n",
              "999  191  Disney's Beauty and the Beast (A Little Golden...            0   \n",
              "\n",
              "     avg_rating  \n",
              "0      3.662500  \n",
              "1      3.977273  \n",
              "2      4.206897  \n",
              "3      4.414634  \n",
              "4      4.081081  \n",
              "..          ...  \n",
              "995    3.666667  \n",
              "996    3.000000  \n",
              "997    5.000000  \n",
              "998    4.000000  \n",
              "999    4.000000  \n",
              "\n",
              "[1000 rows x 4 columns]"
            ]
          },
          "execution_count": 38,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bPPPJWSk2bsz"
      },
      "source": [
        "**Издательство, которое выпустило наибольшее число книг толще 50 страниц.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "id": "q21t8O631aM_",
        "outputId": "b1c1810d-93b7-4b9b-e889-ce5b2a31494d"
      },
      "source": [
        "top_publisher = '''SELECT\n",
        "                       books.publisher_id AS publisher_id,\n",
        "                       publishers.publisher,\n",
        "                       COUNT(books.book_id) AS books_number\n",
        "                    FROM\n",
        "                       books\n",
        "                    INNER JOIN publishers ON publishers.publisher_id = books.publisher_id\n",
        "                    WHERE\n",
        "                       books.num_pages > 50\n",
        "                    GROUP BY\n",
        "                       books.publisher_id,\n",
        "                       publishers.publisher\n",
        "                    ORDER BY\n",
        "                       books_number DESC\n",
        "                    LIMIT 1; \n",
        "'''\n",
        "pd.io.sql.read_sql(top_publisher, con = engine)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>publisher_id</th>\n",
              "      <th>publisher</th>\n",
              "      <th>books_number</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>212</td>\n",
              "      <td>Penguin Books</td>\n",
              "      <td>42</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   publisher_id      publisher  books_number\n",
              "0           212  Penguin Books            42"
            ]
          },
          "execution_count": 10,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ueDvYn425435"
      },
      "source": [
        "Издательство \"Penguin Books\"выпустило 42 книги толще 50 страниц."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PiGwEj5F6Br6"
      },
      "source": [
        "**Автор с самой высокой средней оценкой книг с учетом только книги с 50 и более оценками.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "id": "iscjQNl06Ak6",
        "outputId": "cd0afb70-927f-414d-bd6c-3e6ad4be450f"
      },
      "source": [
        "top_author = '''SELECT\n",
        "                    authors.author AS author,\n",
        "                    COUNT(rating_id) AS rating_cnt,\n",
        "                    AVG(rating) as rating_avg\n",
        "                  FROM \n",
        "                     ratings\n",
        "                  INNER JOIN books ON ratings.book_id = books.book_id \n",
        "                  INNER JOIN authors ON authors.author_id = books.author_id\n",
        "                  GROUP BY\n",
        "                      authors.author\n",
        "                  HAVING \n",
        "                      COUNT(rating_id) > 50     \n",
        "                  ORDER BY\n",
        "                      rating_avg DESC\n",
        "                  LIMIT 1; \n",
        "\n",
        "'''\n",
        "pd.io.sql.read_sql(top_author, con = engine)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>author</th>\n",
              "      <th>rating_cnt</th>\n",
              "      <th>rating_avg</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>J.K. Rowling/Mary GrandPré</td>\n",
              "      <td>312</td>\n",
              "      <td>4.288462</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                       author  rating_cnt  rating_avg\n",
              "0  J.K. Rowling/Mary GrandPré         312    4.288462"
            ]
          },
          "execution_count": 11,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5aaDiCrG_chH"
      },
      "source": [
        "Дж.К. Роулинг - автор с самой высокой средней оценкой книг."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zjWOax3DAnBg"
      },
      "source": [
        "**Среднее количество обзоров от пользователей, которые поставили больше 50 оценок.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "id": "id2Px--P_kAw",
        "outputId": "75a77d24-0eb8-484d-aa31-42a00cb7f33a"
      },
      "source": [
        "mean_reviews = '''\n",
        "        SELECT\n",
        "            AVG(sub.count)\n",
        "        FROM\n",
        "            (SELECT COUNT(*)\n",
        "            FROM \n",
        "                reviews\n",
        "            WHERE \n",
        "                username IN\n",
        "                (SELECT username\n",
        "                    FROM \n",
        "                        ratings\n",
        "                    GROUP BY \n",
        "                        username\n",
        "                    HAVING \n",
        "                        COUNT(rating) > 50)\n",
        "            GROUP BY \n",
        "                username)\n",
        "            AS sub;\n",
        "'''\n",
        "pd.io.sql.read_sql(mean_reviews, con = engine)        "
      ],
      "execution_count": null,
      "outputs": [
        {
          "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>avg</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>24.333333</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         avg\n",
              "0  24.333333"
            ]
          },
          "execution_count": 12,
          "metadata": {},
          "output_type": "execute_result"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lNobRaWfBkrN"
      },
      "source": [
        "Пользователи, которые поставили больше 50 оценок, в среднем сделали 24,3 обзора."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oxRkq6d9B4jT"
      },
      "source": [
        "**ИТОГ ИССЛЕДОВАНИЯ:**\n",
        "\n",
        "После 1 января 2000 года вышло 819 книг.\n",
        "\n",
        "Издательство Penguin Books является лидером по количеству выпущенных книг с более чем 50 страницами, а именно издательство выпустило 42 таких книги.\n",
        "\n",
        "Дж.К. Роулинг является авторомс самой высокой средней оценкой книг. \n",
        "\n",
        "Пользователи, поставившие более 50 оценок, в среднем сделали 24,3 обзора.\n",
        "\n"
      ]
    }
  ]
}