sql_группировка_агрегатные_функции.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Группировка и агрегатные функции\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",
    "Особенность приложения заключается в том, что для просмотра товаров не обязательно авторизовываться. До момента авторизации про пользователя известен только его DeviceID — идентификатор устройства. При этом для совершения покупки логин обязателен. На моменте авторизации пользователю присваивается UserID, и тогда мы уже знаем два его идентификатора: DeviceID (устройство) и UserID (логин). Так как на этапах установки приложения и просмотра каталога пользователь еще может быть не авторизован, там мы сохраняем только DeviceID. Но так как покупки нельзя совершить без авторизации, то покупки сохраняются только с UserID. Для того чтобы просмотры и установки можно было объединить с покупками, нам нужна таблица соответствия DeviceID к UserID, то есть таблица devices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1) Работаем с таблицей checks!\n",
    "Посчитайте, сколько покупок приходится на каждого клиента, указав имя столбца как NumChecks. Результирующую таблицу с UserID, NumChecks отсортируйте по убыванию нового столбца. Ограничение вывода – 10.\n",
    "\n",
    "    SELECT \n",
    "        UserID, \n",
    "        count(*) AS NumChecks \n",
    "    FROM \n",
    "        checks \n",
    "    GROUP BY \n",
    "        UserID   \n",
    "    ORDER BY \n",
    "        NumChecks DESC  \n",
    "    LIMIT 10\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2) Давайте немного усложним задачу, и посчитаем ещё и сумму сделанных покупок в рублях (Rub).\n",
    "Выведите в результирующую таблицу UserID клиентов, количество сделанных покупок (NumChecks) и сумму потраченных денег (Revenue). Результат отсортируйте по убыванию Revenue, оставив 10 записей.\n",
    "\n",
    "    SELECT \n",
    "        UserID, \n",
    "        count(*) AS NumChecks,\n",
    "        SUM(Rub) AS Revenue  \n",
    "    FROM \n",
    "        checks \n",
    "    GROUP BY \n",
    "        UserID   \n",
    "    ORDER BY \n",
    "        Revenue DESC \n",
    "    LIMIT 10\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3) Теперь давайте попробуем с помощью группировки и агрегатных функций посмотреть динамику трат клиентов. Для этого нужно посчитать по дням минимальный, средний, и максимальный чек. Иными словами, необходимо сгруппироваться по датам, и использовать агрегатные функции MIN, AVG, MAX.\n",
    "\n",
    "Выведите минимальный (MinCheck), максимальный (MaxCheck) и средний (AvgCheck) чек по дням. В результирующей таблице используйте сортировку по убыванию (DESC) столбца BuyDate. Как и на предыдущем шаге, ограничение на вывод равно 10.\n",
    "\n",
    "\n",
    "    SELECT \n",
    "        BuyDate,\n",
    "        MIN(Rub) AS MinCheck, \n",
    "        MAX(Rub) AS MaxCheck, \n",
    "        AVG(Rub) AS AvgCheck \n",
    "\n",
    "    FROM \n",
    "        checks \n",
    "    GROUP BY \n",
    "        BuyDate   \n",
    "    ORDER BY \n",
    "        BuyDate DESC \n",
    "    LIMIT 10\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4) Представим, что для проведения новой акции нам потребовалось выгрузить сегмент пользователей, которые приносят наибольшую выручку.\n",
    "\n",
    "Найдите покупателей, сумма покупок которых превышает 10 000 рублей (Rub). Для этого сгруппируйте пользователей по UserID, затем примените нужную агрегатную функцию и назовите новую колонку Revenue. Отфильтруйте нужные значения, результат отсортируйте по убыванию UserID и выведите 10 строк.\n",
    "\n",
    "    SELECT \n",
    "        UserID,\n",
    "        SUM(Rub) AS Revenue \n",
    "\n",
    "    FROM \n",
    "        checks \n",
    "    GROUP BY \n",
    "        UserID   \n",
    "    HAVING \n",
    "        Revenue > 10000 \n",
    "    ORDER BY \n",
    "        UserID DESC \n",
    "    LIMIT 10\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "В этой и последующих задачах мы будем работать с таблицей **retail** в ClickHouse, которая содержит данные о покупках. \n",
    "\n",
    "Таблица включает следующие поля: \n",
    "- InvoiceNo — идентификатор покупки\n",
    "- StockCode — идентификатор товара на складе\n",
    "- Description — текстовое описание товара\n",
    "- Quantity — количество товара\n",
    "- InvoiceDate — дата покупки\n",
    "- UnitPrice — цена товара\n",
    "- CustomerID — идентификатор покупателя\n",
    "- Country — страна\n",
    "\n",
    "5) Представим, что к вам пришел менеджер с проблемой: за последний месяц он наблюдает снижение выручки в некоторых регионах и хочет определить возможную причину этого снижения. Для начала можно сгруппировать данные о выручке по странам и найти среди них топ-5 стран по величине выручки, так как интереснее всего нам будет смотреть именно на данные этих стран.\n",
    "\n",
    "Посчитайте выручку (Revenue) по странам и отсортируйте таблицу в порядке убывания выручки. В качестве ответа скопируйте название страны с самой высокой выручкой.  Обратите внимание, что в изначальной таблице указана цена одной единицы товара (UnitPrice) и количество купленного товара (Quantity), а не выручка, и для получения Revenue нужно будет использовать агрегирующую функцию Sum и оператор умножения *.\n",
    "\n",
    "    SELECT \n",
    "        Country,  \n",
    "        SUM(UnitPrice * Quantity) AS Revenue  \n",
    "    FROM \n",
    "        retail  \n",
    "    GROUP BY \n",
    "        Country  \n",
    "    ORDER BY \n",
    "        Revenue DESC  \n",
    "    LIMIT 1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6) В качестве более подробного описания выручки по странам можно посмотреть, каково среднее количество купленных товаров и средняя цена товара в покупках, совершенных в определенной стране.\n",
    "\n",
    "Посчитайте среднее число купленных товаров по стране и среднюю цену товара, сгруппировав данные по странам и используя агрегирующую функцию Avg, и отсортируйте по убыванию средней цены товара (DESC).\n",
    "\n",
    "В качестве ответа впишите название страны, в которой самая высокая средняя цена за купленный товар.\n",
    "\n",
    "    SELECT \n",
    "        Country,  \n",
    "        AVG(UnitPrice) AS Avg_UnitPrice,\n",
    "        AVG(Quantity) AS Avg_Quantity\n",
    "    FROM \n",
    "        retail  \n",
    "    WHERE\n",
    "        Description != 'Manual'\n",
    "    GROUP BY \n",
    "        Country  \n",
    "    ORDER BY \n",
    "        Avg_UnitPrice DESC  \n",
    "    LIMIT 1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7) Теперь посмотрим на динамику общей суммы выручки по месяцам.\n",
    "\n",
    "Посчитайте выручку (Revenue) по месяцам (обратите внимание, что в таблице указана цена одной единицы товара и количество купленного товара), округлив InvoiceDate к началу месяца с помощью toStartOfMonth().\n",
    "\n",
    "В качестве ответа укажите сумму выручки (Revenue) из первой строки результирующей таблицы, используя сортировку по убыванию (DESC) по столбцу Revenue. Не забывайте отфильтровать строки по условию Description != 'Manual'\n",
    "\n",
    "    SELECT \n",
    "        toStartOfMonth(InvoiceDate) AS Month_Date,\n",
    "        SUM(UnitPrice * Quantity) AS Revenue  \n",
    "    FROM \n",
    "        retail  \n",
    "    WHERE\n",
    "        Description != 'Manual'\n",
    "    GROUP BY \n",
    "        Month_Date\n",
    "    ORDER BY \n",
    "        Revenue DESC  \n",
    "    LIMIT 1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "8) Давайте посмотрим на динамику выручки от покупателей, которые в среднем покупают самые дорогие товары.  В качестве целевой метрики будем использовать среднюю цену купленного товара (UnitPrice), данные посмотрим за март 2011 года.\n",
    "\n",
    "Посчитайте среднюю цену товара клиента (UnitPrice) с группировкой по id покупателя, используя агрегирующую функцию Avg, затем выведите строки с покупками тех покупателей, у которых самая высокая средняя цена купленного товара в марте. Не забывайте отфильтровать строки по условию Description != 'Manual'. В качестве ответа впишите CustomerID покупателя с самой высокой средней ценой купленного товара.\n",
    "\n",
    "    SELECT \n",
    "        CustomerID,  \n",
    "        AVG(UnitPrice) AS Avg_UnitPrice,\n",
    "        toStartOfMonth(InvoiceDate) AS Month_Date\n",
    "\n",
    "    FROM \n",
    "        retail  \n",
    "    WHERE\n",
    "        Description != 'Manual'\n",
    "\n",
    "    GROUP BY \n",
    "        CustomerID,\n",
    "        Month_Date\n",
    "    HAVING \n",
    "        Month_Date = '2011-03-01'\n",
    "    ORDER BY \n",
    "        Avg_UnitPrice DESC   \n",
    "    LIMIT 1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "9) Давайте попробуем ответить на следующий вопрос: \"Как изменилось среднее, минимальное и максимальное количество купленного товара в стране с наибольшей выручкой в течение последних месяцев?\"\n",
    "\n",
    "Для этого нужно сгруппировать данные по странам и месяцам, посчитать среднее (Avg), минимальное (Min) и максимальное (Max) количество купленных товаров (Quantity), и отсортировать данные по месяцу, применить фильтр по стране (нам нужны данные из страны с наибольшей выручкой из задания 8 (step 8)). Если обратить внимание на полученное значение минимального количества товаров, то станет понятно, что там есть отрицательные значения, которые следует отфильтровать (также не забывайте отфильтровывать значения с Description != 'Manual').\n",
    "\n",
    "В качестве ответа скопируйте наибольшее историческое значение среднего числа купленных товаров для этой страны.\n",
    "\n",
    "    SELECT \n",
    "        Country,\n",
    "        toStartOfMonth(InvoiceDate) AS Month_Date,\n",
    "        AVG(Quantity) AS Avg_Quantity,\n",
    "        MIN(Quantity) AS Min_Quantity,\n",
    "        MAX(Quantity) AS Max_Quantity\n",
    "\n",
    "    FROM \n",
    "        retail  \n",
    "\n",
    "    WHERE\n",
    "        Description != 'Manual'\n",
    "        AND Country = 'United Kingdom'\n",
    "        AND Quantity >= 0  \n",
    "\n",
    "    GROUP BY \n",
    "        Month_Date,\n",
    "        Country\n",
    "\n",
    "    ORDER BY \n",
    "        Avg_Quantity DESC  \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
}