sql_типы_данных_и_их_преобразование.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Типы данных и их преобразование\n",
    "\n",
    "Знакомимся с различными типами данных: булевы, числовые, строковые, временные, Nullable, массивы и геокоординаты. Узнаем, как менять тип данных и что такое переполнение типов."
   ]
  },
  {
   "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",
    "\n",
    "На Airbnb есть две основные группы пользователей:\n",
    "- Хозяева (хосты, hosts) – сдают жилье\n",
    "- Путешественники (travelers) – снимают\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1) Начнем с анализа характеристик хозяев в таблице listings! \n",
    "Пользователи, сдающие квартиры на Airbnb, зарегистрировались в разное время. Кто-то – очень давно, а кто-то совсем недавно. Давайте проверим, в какой месяц и год зарегистрировалось наибольшее количество новых хостов. В качестве ответа введите дату следующего формата: 2010-12\n",
    "\n",
    "    SELECT \n",
    "        toStartOfMonth(toDateOrNull(host_since)) AS host_since_month,\n",
    "        uniqExact(host_id) AS count_host_id\n",
    "    FROM listings  \n",
    "    GROUP BY  \n",
    "        host_since_month\n",
    "    ORDER BY \n",
    "        count_host_id DESC \n",
    "    LIMIT 1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2) Посмотрим на среднюю частоту ответа среди хозяев (f) и суперхозяев (t).\n",
    "Значения частоты ответа хранятся как строки и включают значок %, который необходимо заменить на пустоту (''). После этого приведите столбец к нужному типу данных с помощью toInt32OrNull() и посчитайте среднюю частоту отклика в разбивке по тому, является ли хост суперхозяином или нет. В качестве ответа укажите наибольшее среднее.\n",
    "\n",
    "    SELECT\n",
    "        AVG(toInt32OrNull(replaceAll(host_response_rate, '%', ''))) AS avg_responce_rate,\n",
    "        host_is_superhost\n",
    "\n",
    "    FROM (\n",
    "        SELECT \n",
    "            DISTINCT \n",
    "                host_is_superhost,\n",
    "                host_response_rate,\n",
    "                host_id\n",
    "\n",
    "        FROM \n",
    "            listings) AS sub \n",
    "\n",
    "    GROUP BY  \n",
    "        host_is_superhost\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3) Сгруппируйте данные из listings по хозяевам (host_id) и посчитайте, какую цену за ночь в среднем каждый из них устанавливает (у одного хоста может быть несколько объявлений). Идентификаторы сдаваемого жилья объедините в отдельный массив. Таблицу отсортируйте по убыванию средней цены и убыванию host_id (в таком порядке). В качестве ответа укажите первый массив в результирующей таблице, состоящий более чем из двух id. \n",
    "\n",
    "    SELECT \n",
    "        host_id,\n",
    "        AVG(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) AS avg_price,\n",
    "        groupArray(id) as array_id\n",
    "    FROM \n",
    "        listings  \n",
    "    GROUP BY  \n",
    "        host_id \n",
    "    HAVING \n",
    "        length(array_id) > 2\n",
    "    ORDER BY \n",
    "        avg_price DESC,\n",
    "        host_id DESC \n",
    "    LIMIT 10\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4) Немного усложним предыдущую задачу, и посчитаем разницу между максимальной и минимальной установленной ценой у каждого хозяина. В качестве ответа укажите идентификатор хоста, у которого разница оказалась наибольшей. \n",
    "\n",
    "    SELECT \n",
    "        host_id,\n",
    "        MIN(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) AS min_price,\n",
    "        MAX(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) AS max_price,\n",
    "        (max_price - min_price) AS diff_price\n",
    "    FROM \n",
    "        listings  \n",
    "    GROUP BY  \n",
    "        host_id \n",
    "    ORDER BY \n",
    "        diff_price DESC,\n",
    "        host_id DESC \n",
    "    LIMIT 5\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5) Теперь сгруппируйте данные по типу жилья и выведите средние значения цены за ночь, размера депозита и цены уборки. Обратите внимание на тип данных, наличие значка доллара и запятых в больших суммах. Для какого типа жилья среднее значение залога наибольшее?\n",
    "\n",
    "    SELECT \n",
    "        room_type,\n",
    "        AVG(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) AS avg_price,\n",
    "    AVG(toFloat32OrNull(replaceRegexpAll(security_deposit, '[$,]', ''))) AS avg_deposit,\n",
    "        AVG(toFloat32OrNull(replaceRegexpAll(cleaning_fee, '[$,]', ''))) AS avg_cleaning_fee\n",
    "    FROM \n",
    "        listings  \n",
    "    GROUP BY  \n",
    "        room_type \n",
    "    ORDER BY \n",
    "        avg_deposit DESC\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6) В каких частях города средняя стоимость за ночь является наиболее низкой? \n",
    "Сгруппируйте данные по neighbourhood_cleansed и посчитайте среднюю цену за ночь в каждом районе. В качестве ответа введите название места, где средняя стоимость за ночь ниже всего.\n",
    "\n",
    "\n",
    "    SELECT \n",
    "        neighbourhood_cleansed,\n",
    "        AVG(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) AS avg_price\n",
    "    FROM \n",
    "        listings  \n",
    "    GROUP BY  \n",
    "        neighbourhood_cleansed \n",
    "    ORDER BY \n",
    "        avg_price ASC  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7) В каких районах Берлина средняя площадь жилья, которое сдаётся целиком, является наибольшей? Отсортируйте по среднему и выберите топ-3. \n",
    "\n",
    "    SELECT \n",
    "        neighbourhood_cleansed,\n",
    "        AVG(toFloat32OrNull(square_feet)) AS avg_square \n",
    "    FROM \n",
    "        listings  \n",
    "    WHERE  \n",
    "        room_type = 'Entire home/apt'\n",
    "    GROUP BY  \n",
    "        neighbourhood_cleansed \n",
    "    ORDER BY \n",
    "        avg_square DESC   \n",
    "    LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "8) Посмотрим, какая из представленных комнат расположена ближе всего к центру города. В качестве ответа укажите id объявления.\n",
    "\n",
    "    SELECT \n",
    "        id,\n",
    "        toFloat64OrNull(longitude) as longitude,\n",
    "        toFloat64OrNull(latitude) as latitude,\n",
    "        geoDistance(13.4050, 52.5200, longitude, latitude) as distance_id\n",
    "    FROM \n",
    "        listings  \n",
    "    WHERE  \n",
    "        room_type = 'Private room'\n",
    "\n",
    "    ORDER BY \n",
    "        distance_id ASC\n",
    "    LIMIT 1\n",
    "\n"
   ]
  }
 ],
 "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
}