final_2_3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Финальный проект karpov.courses: вариант 2\n",
    "#### Задание 3\n",
    "\n",
    "В базе данных вашей компании присутствуют следующие таблицы\n",
    "\n",
    "    city\n",
    "        city_id – id города, где проходит акция\n",
    "        client_city_id – id города, где находится покупатель\n",
    "        city – город\n",
    "        client_city – город, где находится покупатель\n",
    "    client\n",
    "        client_id – id покупателя\n",
    "        client_city_id – id города, где находится покупатель\n",
    "        birth_date – дата рождения покупателя\n",
    "        registration – дата регистрации покупателя\n",
    "    promotion\n",
    "        promotion_id – id акции\n",
    "        category_id – id категории\n",
    "        promotion_name – акция\n",
    "        category_name – категория\n",
    "        partner_id – id партнёра\n",
    "        partner_name – партнёр\n",
    "    purchase\n",
    "        purchase_id – id покупки\n",
    "        partner_id – id партнёра\n",
    "        client_id – id покупателя\n",
    "        city_id – id города\n",
    "        promotion_id – id акции\n",
    "        category_id – id категории\n",
    "        purchase_date – дата покупки\n",
    "        price – цена за единицу товара\n",
    "        quantity – число проданных единиц\n",
    "        status – статус покупки\n",
    "\n",
    "Вам нужно написать запрос, чтобы получить такую таблицу\n",
    "\n",
    "    purchase_date – дата покупки\n",
    "    purchase_id – id покупки\n",
    "    client_id – id покупателя\n",
    "    client_age – возраст покупателя\n",
    "    client_registration_age – как долго человек пользуется вашими магазинами\n",
    "    client_category – содержит new или old, если это первая или последующая покупка соответственно\n",
    "    promotion_name – акция\n",
    "    category_name – категория\n",
    "    partner_name – партнёр\n",
    "    client_city – город, где находится покупатель\n",
    "    city – город\n",
    "    revenue – сумма выручки\n",
    "    quantity – число проданных единиц\n",
    "\n",
    "При этом в таблице должны присутствовать только значения, где в поле status стоит значение 1, и только те, где purchase_date находится в диапазоне от 01.05.2020 до 01.08.2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# У меня дома развернуты свои виртуалки с ClickHouse и Redash,\n",
    "# поэтому мне было удобнее решать задачу в рамках своей инфраструктуры.\n",
    "# Думаю, это не принципиально, ведь я планирую не использовать каких-то специфичных для ClickHouse SQL-конструкций.\n",
    "from simplech import ClickHouse\n",
    "# https://rock.st/docs/reference/simplech/\n",
    "# очень простая библиотека для работы с ClickHouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_temporary_and_external_tables\n",
      "default\n",
      "project_2_3\n",
      "system\n",
      "test\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# подключаемся БД и проверяем что все ОК\n",
    "ch = ClickHouse(host='192.168.15.115', password='***')\n",
    "print(ch.select('SHOW DATABASES'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# создаем отдельную БД для таблиц проекта\n",
    "ch.run('CREATE DATABASE IF NOT EXISTS project_2_3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# создаем сами таблицы\n",
    "# city\n",
    "query = \"\"\"\\\n",
    "CREATE TABLE IF NOT EXISTS project_2_3.city (\n",
    "    city_id UInt64,\n",
    "    client_city_id UInt64,\n",
    "    city String,\n",
    "    client_city String\n",
    "    ) ENGINE=Log\n",
    "\"\"\"\n",
    "ch.run(query)\n",
    "# движок Log, а не MergeTree() просто для простоты,\n",
    "# ведь мне таблицы нужны будут не для реальной работы,\n",
    "# а просто для экспериментов с итоговым запросом."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# client\n",
    "query = \"\"\"\\\n",
    "CREATE TABLE IF NOT EXISTS project_2_3.client (\n",
    "    client_id UInt64,\n",
    "    client_city_id UInt64,\n",
    "    birth_date Date,\n",
    "    registration Date\n",
    "    ) ENGINE=Log\n",
    "\"\"\"\n",
    "ch.run(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# promotion\n",
    "query = \"\"\"\\\n",
    "CREATE TABLE IF NOT EXISTS project_2_3.promotion (\n",
    "    promotion_id UInt64,\n",
    "    category_id UInt64,\n",
    "    promotion_name String,\n",
    "    category_name String,\n",
    "    partner_id UInt64,\n",
    "    partner_name String\n",
    "    ) ENGINE=Log\n",
    "\"\"\"\n",
    "ch.run(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# purchase\n",
    "query = \"\"\"\\\n",
    "CREATE TABLE IF NOT EXISTS project_2_3.purchase (\n",
    "    purchase_id UInt64,\n",
    "    partner_id UInt64,\n",
    "    client_id UInt64,\n",
    "    city_id UInt64,\n",
    "    promotion_id UInt64,\n",
    "    category_id UInt64,\n",
    "    purchase_date Date,\n",
    "    price Float64,\n",
    "    quantity Float64,\n",
    "    status UInt8\n",
    "    ) ENGINE=Log\n",
    "\"\"\"\n",
    "ch.run(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ниже будет очень прямолинейный подход к заполнению таблиц данными.\n",
    "Правильно было бы использовать бибилиотеку Faker https://faker.readthedocs.io/en/master/, чтобы сделать небольшие списки городов, клиентов и т.д.\n",
    "И затем случаным образом выбирать элементы списков для заполнения таблиц. Айдишниками бы выступали порядковые номера элементов списков. Но время уже так сильно поджимало, что пришлось делать по простому."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# запишем даныне в city\n",
    "ch.push('project_2_3.city', {'city_id': 1, 'client_city_id': 1, 'city': 'Anapa', 'client_city': 'Anapa'})\n",
    "ch.push('project_2_3.city', {'city_id': 2, 'client_city_id': 2, 'city': 'Moscow', 'client_city': 'Moscow'})\n",
    "ch.push('project_2_3.city', {'city_id': 3, 'client_city_id': 3, 'city': 'Krasnodar', 'client_city': 'Krasnodar'})\n",
    "ch.flush_all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# запишем даныне в client\n",
    "ch.push('project_2_3.client', {\n",
    "    'client_id': 1, 'client_city_id': 1, 'birth_date': '1982-01-29', 'registration': '2020-10-01'})\n",
    "ch.push('project_2_3.client', {\n",
    "    'client_id': 2, 'client_city_id': 1, 'birth_date': '1983-02-28', 'registration': '2020-10-02'})\n",
    "ch.push('project_2_3.client', {\n",
    "    'client_id': 3, 'client_city_id': 2, 'birth_date': '1973-05-01', 'registration': '2020-10-01'})\n",
    "ch.push('project_2_3.client', {\n",
    "    'client_id': 4, 'client_city_id': 2, 'birth_date': '1980-06-09', 'registration': '2020-10-05'})\n",
    "ch.push('project_2_3.client', {\n",
    "    'client_id': 5, 'client_city_id': 3, 'birth_date': '1991-01-01', 'registration': '2020-10-06'})\n",
    "ch.flush_all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# запишем даныне в promotion\n",
    "ch.push('project_2_3.promotion', {\n",
    "    'promotion_id': 1, 'category_id': 1, 'promotion_name': 'Milka Chocolate', 'category_name': 'food',\n",
    "    'partner_id': 1, 'partner_name': 'Mars'})\n",
    "ch.push('project_2_3.promotion', {\n",
    "    'promotion_id': 2, 'category_id': 2, 'promotion_name': 'Football events', 'category_name': 'sport',\n",
    "    'partner_id': 2, 'partner_name': 'Addidas'})\n",
    "ch.push('project_2_3.promotion', {\n",
    "    'promotion_id': 3, 'category_id': 2, 'promotion_name': 'Running events', 'category_name': 'sport', \n",
    "    'partner_id': 2, 'partner_name': 'Addidas'})\n",
    "ch.flush_all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# запишем даныне в purchase\n",
    "ch.push('project_2_3.purchase', {\n",
    "    'purchase_id': 1, 'partner_id': 1, 'client_id': 1, 'city_id': 2, 'promotion_id': 1, 'category_id': 1,\n",
    "    'purchase_date': '2020-02-01', 'price': 325, 'quantity': 1, 'status': 1})\n",
    "\n",
    "ch.push('project_2_3.purchase', {\n",
    "    'purchase_id': 2, 'partner_id': 1, 'client_id': 2, 'city_id': 2, 'promotion_id': 1, 'category_id': 1,\n",
    "    'purchase_date': '2020-05-02', 'price': 500, 'quantity': 2, 'status': 1})\n",
    "\n",
    "ch.push('project_2_3.purchase', {\n",
    "    'purchase_id': 3, 'partner_id': 1, 'client_id': 2, 'city_id': 1, 'promotion_id': 1, 'category_id': 1,\n",
    "    'purchase_date': '2020-05-03', 'price': 500, 'quantity': 1, 'status': 1})\n",
    "\n",
    "ch.push('project_2_3.purchase', {\n",
    "    'purchase_id': 4, 'partner_id': 2, 'client_id': 3, 'city_id': 2, 'promotion_id': 2, 'category_id': 2,\n",
    "    'purchase_date': '2020-06-10', 'price': 890.15, 'quantity': 3, 'status': 1})\n",
    "\n",
    "ch.push('project_2_3.purchase', {\n",
    "    'purchase_id': 5, 'partner_id': 2, 'client_id': 4, 'city_id': 2, 'promotion_id': 2, 'category_id': 2,\n",
    "    'purchase_date': '2020-06-15', 'price': 750.05, 'quantity': 2, 'status': 0})\n",
    "\n",
    "ch.push('project_2_3.purchase', {\n",
    "    'purchase_id': 6, 'partner_id': 2, 'client_id': 3, 'city_id': 2, 'promotion_id': 3, 'category_id': 2,\n",
    "    'purchase_date': '2020-08-10', 'price': 900, 'quantity': 1, 'status': 1})\n",
    "\n",
    "ch.push('project_2_3.purchase', {\n",
    "    'purchase_id': 7, 'partner_id': 2, 'client_id': 3, 'city_id': 2, 'promotion_id': 3, 'category_id': 2,\n",
    "    'purchase_date': '2020-07-20', 'price': 800, 'quantity': 2, 'status': 1})\n",
    "\n",
    "ch.push('project_2_3.purchase', {\n",
    "    'purchase_id': 8, 'partner_id': 2, 'client_id': 5, 'city_id': 3, 'promotion_id': 3, 'category_id': 2,\n",
    "    'purchase_date': '2020-07-20', 'price': 850, 'quantity': 2, 'status': 1})\n",
    "\n",
    "ch.flush_all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-05-02\t2\t2\t37\t24\tnew\tMilka Chocolate\tfood\tMars\tAnapa\tMoscow\t1000\t2\n",
      "2020-05-03\t3\t2\t37\t24\told\tMilka Chocolate\tfood\tMars\tAnapa\tAnapa\t500\t1\n",
      "2020-06-10\t4\t3\t47\t25\tnew\tFootball events\tsport\tAddidas\tMoscow\tMoscow\t2670.45\t3\n",
      "2020-07-20\t7\t3\t47\t25\told\tRunning events\tsport\tAddidas\tMoscow\tMoscow\t1600\t2\n",
      "2020-07-20\t8\t5\t29\t20\tnew\tRunning events\tsport\tAddidas\tKrasnodar\tKrasnodar\t1700\t2\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Итоговый запрос для полечения результата\n",
    "query = \"\"\"\\\n",
    "SELECT \n",
    "    purchase_date,\n",
    "    purchase_id,\n",
    "    purchase.client_id AS client_id,\n",
    "    DATEDIFF(YY, birth_date, now()) AS client_age_years,\n",
    "    DATEDIFF(DD, registration, now()) AS client_registration_age_days,\n",
    "    if(purchase_date = x.first_purchase, 'new', 'old') AS client_category,\n",
    "    promotion_name,\n",
    "    category_name,\n",
    "    partner_name,\n",
    "    c2.client_city AS client_city2,\n",
    "    c1.city AS city1,\n",
    "    price * quantity AS revenue,\n",
    "    quantity\n",
    "FROM project_2_3.purchase\n",
    "LEFT JOIN project_2_3.client\n",
    "ON purchase.client_id = client.client_id\n",
    "LEFT JOIN project_2_3.promotion AS pr\n",
    "ON purchase.promotion_id = pr.promotion_id\n",
    "LEFT JOIN project_2_3.city AS c1\n",
    "ON purchase.city_id = c1.city_id\n",
    "LEFT JOIN project_2_3.city AS c2\n",
    "ON client.client_city_id  = c2.client_city_id \n",
    "LEFT JOIN (\n",
    "    SELECT client_id,\n",
    "      MIN(purchase_date) AS first_purchase\n",
    "    FROM project_2_3.purchase\n",
    "    GROUP BY client_id\n",
    ") AS x\n",
    "ON purchase.client_id = x.client_id\n",
    "WHERE status=1 AND purchase_date between '2020/05/01' AND '2020/08/01'\n",
    "\"\"\"\n",
    "print(ch.select(query))\n",
    "# Пришлось изменить накоторые элиасы:\n",
    "# у client_age и sclient_registration_age добавил к названию размерность,\n",
    "# потому что в задании не уточнялось - возраст в годах/днях.\n",
    "# Изменил client_city на client_city2, потому что иначе, видимо, значение выводилось из первого джойна c1"
   ]
  },
  {
   "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": 2
}