sql_подзапросы_представления_создание_таблиц.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Подзапросы, представления, создание таблиц\n",
    "\n",
    "Используем подзапросы и представления таблиц, вставляем новые данные. Создаем, сохраняем и удаляем таблицы."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Данные: Airbnb в Берлине.**\n",
    "\n",
    "Имеются следующие таблицы:\n",
    "\n",
    "listings – информация о жилье, включая полные описания, характеристики и средние оценки в отзывах; поскольку столбцов очень много, нужные перечислены в текстах самих задач\n",
    "\n",
    "calendar_summary – информация о доступности и цене того или иного жилья по дням\n",
    "\n",
    "- listing_id – идентификатор жилья (объявления)\n",
    "- date – дата\n",
    "- available – доступность жилья в данный день (t/f)\n",
    "- price – цена за ночь\n",
    "\n",
    "reviews – отзывы\n",
    "\n",
    "- listing_id –  идентификатор жилья  \n",
    "- id – id отзыва\n",
    "- date – дата отзыва\n",
    "- reviewer_id – id ревьюера (автора отзыва)\n",
    "- reviewer_name – имя автора\n",
    "- comments – сам отзыв\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1) Сначала оставьте только те объявления, в которых оценка на основе отзывов выше среднего, а число отзывов в месяц составляет строго меньше трёх. Затем отсортируйте по убыванию две колонки: сначала по числу отзывов в месяц, потом по оценке. В качестве ответа укажите id объявления из первой строки.\n",
    "\n",
    "    SELECT\n",
    "        id,\n",
    "        toFloat64OrNull(review_scores_rating),\n",
    "        reviews_per_month\n",
    "    FROM\n",
    "        listings \n",
    "    WHERE\n",
    "        toFloat64OrNull(review_scores_rating) > (SELECT \n",
    "                                            AVG(toFloat64OrNull(review_scores_rating)) \n",
    "                    FROM listings)\n",
    "            AND reviews_per_month < 3\n",
    "    ORDER BY\n",
    "        reviews_per_month DESC,\n",
    "        review_scores_rating DESC \n",
    "    LIMIT 10\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2) Посчитайте среднее расстояние до центра города и выведите идентификаторы объявлений о сдаче отдельных комнат, для которых расстояние оказалось меньше среднего. Результат отсортируйте по убыванию, тем самым выбрав комнату, которая является наиболее удаленной от центра, но при этом расположена ближе, чем остальные комнаты в среднем. \n",
    "\n",
    "В качестве ответа укажите идентификатор хозяина (host_id), сдающего данную комнату.\n",
    "\n",
    "    SELECT\n",
    "        host_id, \n",
    "        geoDistance(13.4050, 52.5200, toFloat64OrNull(longitude), toFloat64OrNull(latitude)) AS distance,\n",
    "        room_type\n",
    "\n",
    "    FROM\n",
    "        listings \n",
    "    WHERE\n",
    "             distance < (SELECT AVG(geoDistance(13.4050, 52.5200, toFloat64OrNull(longitude), toFloat64OrNull(latitude))) FROM \n",
    "            (SELECT * FROM listings WHERE room_type = 'Private room'))\n",
    "            AND room_type = 'Private room'\n",
    "\n",
    "    ORDER BY distance DESC\n",
    "    LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3) Представим, что вы планируете снять жилье в Берлине на 7 дней, используя более хитрые фильтры, чем предлагаются на сайте.\n",
    "В этой задаче можно потренироваться в написании подзапросов, но задание можно решить и без них.\n",
    "\n",
    "Отберите объявления из таблицы listings, которые:\n",
    "- находятся на расстоянии от центра меньше среднего (здесь нам пригодится запрос из предыдущего задания)\n",
    "- обойдутся дешевле 100 долларов в день (price с учетом cleaning_fee, который добавляется к общей сумме за неделю, т.е его нужно делить на кол-во дней)\n",
    "- имеют последние отзывы (last_review), начиная с 1 сентября 2018 года\n",
    "- имеют WiFi в списке удобств (amenities)\n",
    "\n",
    "Отсортируйте полученные значения по убыванию review_scores_rating (не забудьте перевести строку к численному виду) и в качестве ответа укажите host_id из первой строки. \n",
    "\n",
    "\n",
    "    SELECT\n",
    "        host_id, \n",
    "        geoDistance(13.4050, 52.5200, toFloat64OrNull(longitude), toFloat64OrNull(latitude)) AS distance,\n",
    "        toFloat64OrZero(replaceRegexpAll(price, '[$,]', '')) AS price,\n",
    "    (toFloat32OrZero(replaceRegexpAll(cleaning_fee, '[$,]', '')))/7 AS cleaning_fee,\n",
    "        price + cleaning_fee AS total_price,\n",
    "        toFloat64OrNull(review_scores_rating) AS review_scores_rating,\n",
    "        toDateOrNull(last_review) AS last_review,\n",
    "        amenities\n",
    "\n",
    "    FROM\n",
    "        listings \n",
    "    WHERE\n",
    "            distance < (SELECT AVG(geoDistance(13.4050, 52.5200, toFloat64OrNull(longitude), toFloat64OrNull(latitude))) FROM listings)\n",
    "        AND total_price < 100\n",
    "        AND last_review > '2018-09-01'\n",
    "        AND multiSearchAnyCaseInsensitive(amenities, ['WiFi']) != 0  \n",
    "\n",
    "    ORDER BY review_scores_rating DESC\n",
    "    LIMIT 1\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Данные: мобильное приложение.**\n",
    "\n",
    "installs — содержит данные об установках приложения по дням.\n",
    "\n",
    "- DeviceID — идентификатор устройства, на которое было установлено приложение;\n",
    "- InstallationDate — дата установки приложения;\n",
    "- InstallCost — цена установки приложения в рублях;\n",
    "- Platform — платформа, на которой было установлено приложение (iOS/ Android);\n",
    "- Source — источник установки приложения (магазин приложения/ рекламная система/ переход с сайта).\n",
    "\n",
    "events — содержит данные о том, как активно пользователи просматривают товары в приложении по дням.\n",
    "\n",
    "- DeviceID — идентификатор устройства, на котором используется приложение;\n",
    "- AppPlatform — платформа, на которой используется приложение (iOS/ Android);\n",
    "- EventDate — дата, за которую собрана статистика;\n",
    "- events — количество просмотров всех товаров за этот день у этого DeviceID.\n",
    "\n",
    "checks — содержит данные о покупках пользователей в приложении по дням\n",
    "\n",
    "- UserID — идентификатор пользователя;\n",
    "- Rub — суммарный чек пользователя на дату;\n",
    "- BuyDate — дата, за которую собрана статистика.\n",
    "\n",
    "devices – чтобы просмотры и установки можно было объединить с покупками\n",
    " \n",
    "- DeviceID — идентификатор устройства;\n",
    "- UserID — идентификатор пользователя.\n",
    "\n",
    "\n",
    "С помощью оператора CASE в SQL можно составлять условные конструкции, выполнять проверку условий и возвращать результат в зависимости от их выполнения. Самих условий под оператором WHEN может быть сколько угодно, конструкцию необходимо завершить оператором END. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4) Используйте таблицу checks и разделите всех покупателей на сегменты:\n",
    "\n",
    "- А — средний чек покупателя менее 5 ₽\n",
    "- B — средний чек покупателя от 5-10 ₽\n",
    "- C — средний чек покупателя от 10-20 ₽\n",
    "- D — средний чек покупателя от 20 ₽\n",
    "\n",
    "Отсортируйте результирующую таблицу по возрастанию UserID и укажите сегмент четвертого пользователя.\n",
    "\n",
    "\n",
    "    SELECT \n",
    "        CASE\n",
    "            WHEN AVG(Rub) < 5 THEN 'A'\n",
    "            WHEN (AVG(Rub) = 5 OR AVG(Rub) > 5) AND AVG(Rub) < 10 THEN 'B'\n",
    "            WHEN (AVG(Rub) = 10 OR AVG(Rub) > 10) AND AVG(Rub) < 20 THEN 'C'\n",
    "            ELSE 'D'\n",
    "        END AS cond, -- END – завершаем конструкцию, а новый столбец называем cond_1\n",
    "        AVG(Rub),\n",
    "        UserID  \n",
    "    FROM\n",
    "        checks\n",
    "    GROUP BY\n",
    "        UserID\n",
    "    ORDER BY\n",
    "        UserID ASC  \n",
    "    LIMIT 4\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5) Используйте предыдущий запрос как подзапрос и посчитайте, сколько клиентов приходится на каждый сегмент и сколько доходов он приносит. Отсортируйте результат по убыванию суммы доходов на сегмент и в качестве ответа укажите наибольшую сумму.\n",
    "\n",
    "    SELECT \n",
    "        count(l.UserID),\n",
    "        l.cond,\n",
    "        sum(r.Rub) as sum_rub\n",
    "\n",
    "    FROM (\n",
    "        SELECT \n",
    "            CASE\n",
    "                WHEN AVG(Rub) < 5 THEN 'A'\n",
    "                WHEN (AVG(Rub) = 5 OR AVG(Rub) > 5) AND AVG(Rub) < 10 THEN 'B'\n",
    "                WHEN (AVG(Rub) = 10 OR AVG(Rub) > 10) AND AVG(Rub) < 20 THEN 'C'\n",
    "                ELSE 'D'\n",
    "            END AS cond,\n",
    "            UserID\n",
    "        FROM checks\n",
    "        GROUP BY UserID\n",
    "        ) as l\n",
    "\n",
    "    JOIN (\n",
    "        SELECT \n",
    "            UserID, \n",
    "            SUM(Rub) as Rub  \n",
    "        FROM checks \n",
    "        GROUP BY UserID\n",
    "        ) AS r\n",
    "\n",
    "        ON l.UserID = r.UserID \n",
    "\n",
    "    GROUP BY l.cond\n",
    "    ORDER BY sum_rub DESC  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6) Вернемся к таблице **Airbnb**. Предположим, что в выборе жилья нас интересует только два параметра: наличие кухни (kitchen) и гибкой системы отмены (flexible), причем первый в приоритете.\n",
    "\n",
    "Создайте с помощью оператора CASE колонку с обозначением группы, в которую попадает жилье из таблицы listings:\n",
    "\n",
    "- 'good', если в удобствах (amenities) присутствует кухня и система отмены (cancellation_policy) гибкая\n",
    "- 'ok', если в удобствах есть кухня, но система отмены не гибкая\n",
    "- 'not ok' во всех остальных случаях\n",
    "\n",
    "Результат отсортируйте по новой колонке по возрастанию, установите ограничение в 5 строк, в качестве ответа укажите host_id первой строки.\n",
    "Обратите внимание, что cancellation_policy - это отдельная колонка, по ней необходимо смотреть систему отмены\n",
    "\n",
    "\n",
    "    SELECT \n",
    "        host_id,\n",
    "        CASE\n",
    "            WHEN multiSearchAnyCaseInsensitive(amenities, ['kitchen']) != 0 AND multiSearchAnyCaseInsensitive(cancellation_policy, ['flexible']) != 0 THEN 'good'\n",
    "            WHEN multiSearchAnyCaseInsensitive(amenities, ['kitchen']) != 0 AND multiSearchAnyCaseInsensitive(cancellation_policy, ['flexible']) == 0 THEN 'ok'\n",
    "            ELSE 'not ok'\n",
    "        END AS cond\n",
    "    FROM listings \n",
    "    ORDER BY cond ASC\n",
    "    LIMIT 5\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7) Напишите запрос для создания таблицы со следующими параметрами, также подобрав подходящий тип данных.\n",
    "\n",
    "Название таблицы: reviews\n",
    "\n",
    "База данных: test\n",
    "\n",
    "Столбцы:\n",
    "- listing_id – идентификатор объявления, может быть только положительным и целым числом, 32-битный тип данных\n",
    "- id – идентификатор хозяина, может быть только положительным и целым числом, 32-битный тип данных\n",
    "- created_at – дата со временем (2020-01-01 00:00:00), часовой пояс – 'Europe/Moscow'\n",
    "- reviewer_id – идентификатор ревьюера, может быть только положительным и целым числом, 32-битный тип данных\n",
    "- reviewer_name – имя того, кто оставил отзыв\n",
    "- comments - текст отзыва\n",
    "\n",
    "Движок: MergeTree\n",
    "\n",
    "Сортировка: listing_id, id\n",
    "\n",
    "    CREATE TABLE test.reviews (\n",
    "        listing_id UInt32, -- колонка, тип данных\n",
    "        id UInt32,\n",
    "        created_at DateTime('Europe/Moscow'),\n",
    "        reviewer_id UInt32,\n",
    "        reviewer_name String,\n",
    "        comments String  \n",
    "        ) \n",
    "\n",
    "    ENGINE = MergeTree -- движок\n",
    "    ORDER BY (listing_id, id)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "8) К вам пришел коллега с новостями: оказывается, в поле created_at будет записываться только дата, без времени, поэтому нужно изменить тип данных!\n",
    "\n",
    "Напишите запрос, который модифицирует тип данных, и введите его в поле ниже без кавычек и лишних пробелов. Не забудьте указать базу данных test перед названием таблицы! \n",
    "\n",
    "\n",
    "    ALTER TABLE test.reviews MODIFY COLUMN created_at Date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "9) Предположим, ваш коллега вставил данные, но что-то перепутал. Часть строк с комментариями осталась совершенно пустой! \n",
    "Напишите запрос, который удалит из таблицы test.reviews те строки, где в столбце comments встречаются пустые значения (''). Введите его в поле ниже без кавычек и лишних пробелов.\n",
    "\n",
    "    ALTER TABLE test.reviews DELETE WHERE comments = ''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "10) Напишите запрос, который удвоит price для всех строк с датой (created_at) после 2019-01-01.\n",
    "\n",
    "    ALTER TABLE test.reviews UPDATE price = price * 2 WHERE created_at > '2019-01-01'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}