coffee.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <a id='toc1_'></a>[Анализ данных кофейни (SQL)](#toc0_)\n",
    "\n",
    "Цель проекта: исследовать информацию из базы данных кофейни и проаналиировать информацию."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Table of contents**<a id='toc0_'></a>    \n",
    "- [Анализ данных кофейни (SQL)](#toc1_)    \n",
    "  - [Загрузка библиотек и подключение к базе данных](#toc1_1_)    \n",
    "  - [Анализ данных](#toc1_2_)    \n",
    "  - [Анализ продуктов, заказов и транзакций](#toc1_3_)    \n",
    "    - [Количество транзакций по дням](#toc1_3_1_)    \n",
    "    - [Топ 10 самых продаваемых товаров каждый день](#toc1_3_2_)    \n",
    "    - [Количество проданных и пропавших продуктов по неделям](#toc1_3_3_)    \n",
    "    - [Названия регионов , где продавался продукт “Columbian Medium Roast” с последней датой продажи в регионе](#toc1_3_4_)    \n",
    "    - [Продукты с упоминанием шоколада в названии](#toc1_3_5_)    \n",
    "    - [Сумма заказов в каждом городе и магазине](#toc1_3_6_)    \n",
    "    - [Выручка и количество проданных товаров по магазинам за каждый день](#toc1_3_7_)    \n",
    "  - [Анализ клиентов](#toc1_4_)    \n",
    "    - [Электронные адреса клиентов и их домены](#toc1_4_1_)    \n",
    "    - [Справочник клиентов](#toc1_4_2_)    \n",
    "    - [Количество клиентов по поколениям](#toc1_4_3_)    \n",
    "\n",
    "<!-- vscode-jupyter-toc-config\n",
    "\tnumbering=false\n",
    "\tanchor=true\n",
    "\tflat=false\n",
    "\tminLevel=1\n",
    "\tmaxLevel=6\n",
    "\t/vscode-jupyter-toc-config -->\n",
    "<!-- THIS CELL WILL BE REPLACED ON TOC UPDATE. DO NOT WRITE YOUR TEXT IN THIS CELL -->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <a id='toc1_1_'></a>[Загрузка библиотек и подключение к базе данных](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# импорт библиотек\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "import psycopg2\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#создаем подключение к локальной (установленный на собственный компьютер) Базе Данных Postgres\n",
    "\n",
    "HOST = '94.26.239.237'\n",
    "PORT = 5432\n",
    "DATABASE = 'postgres'\n",
    "LOGIN = 'student'\n",
    "PASSWORD = 'student'\n",
    "\n",
    "engine=sqlalchemy.create_engine(f'postgresql://{LOGIN}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}') \n",
    "con_postgres = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <a id='toc1_2_'></a>[Анализ данных таблиц](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "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>product_id</th>\n",
       "      <th>product_group</th>\n",
       "      <th>product_category</th>\n",
       "      <th>product_type</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_description</th>\n",
       "      <th>unit_of_measure</th>\n",
       "      <th>current_wholesale_price</th>\n",
       "      <th>current_retail_price</th>\n",
       "      <th>tax_exempt_yn</th>\n",
       "      <th>promo_yn</th>\n",
       "      <th>new_product_yn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Whole Bean/Teas</td>\n",
       "      <td>Coffee beans</td>\n",
       "      <td>Organic Beans</td>\n",
       "      <td>Brazilian - Organic</td>\n",
       "      <td>It's like Carnival in a cup. Clean and smooth.</td>\n",
       "      <td>12 oz</td>\n",
       "      <td>14.40</td>\n",
       "      <td>$18.00</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Whole Bean/Teas</td>\n",
       "      <td>Coffee beans</td>\n",
       "      <td>House blend Beans</td>\n",
       "      <td>Our Old Time Diner Blend</td>\n",
       "      <td>Out packed blend of beans that is reminiscent ...</td>\n",
       "      <td>12 oz</td>\n",
       "      <td>14.40</td>\n",
       "      <td>$18.00</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Whole Bean/Teas</td>\n",
       "      <td>Coffee beans</td>\n",
       "      <td>Espresso Beans</td>\n",
       "      <td>Espresso Roast</td>\n",
       "      <td>Our house blend for a good espresso shot.</td>\n",
       "      <td>1 lb</td>\n",
       "      <td>11.80</td>\n",
       "      <td>$14.75</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Whole Bean/Teas</td>\n",
       "      <td>Coffee beans</td>\n",
       "      <td>Espresso Beans</td>\n",
       "      <td>Primo Espresso Roast</td>\n",
       "      <td>Our primium single source of hand roasted beans.</td>\n",
       "      <td>1 lb</td>\n",
       "      <td>16.36</td>\n",
       "      <td>$20.45</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Whole Bean/Teas</td>\n",
       "      <td>Coffee beans</td>\n",
       "      <td>Gourmet Beans</td>\n",
       "      <td>Columbian Medium Roast</td>\n",
       "      <td>A smooth cup of coffee any time of day.</td>\n",
       "      <td>1 lb</td>\n",
       "      <td>12.00</td>\n",
       "      <td>$15.00</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>84</td>\n",
       "      <td>Add-ons</td>\n",
       "      <td>Flavours</td>\n",
       "      <td>Regular syrup</td>\n",
       "      <td>Chocolate syrup</td>\n",
       "      <td>Bursting with chocolate flavour</td>\n",
       "      <td>pump</td>\n",
       "      <td>0.04</td>\n",
       "      <td>$0.80</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>85</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>Coffee</td>\n",
       "      <td>Specialty coffee</td>\n",
       "      <td>Rio Nights</td>\n",
       "      <td>2 shots of Ouro Brasilerio and pure cane sugar...</td>\n",
       "      <td>6 oz</td>\n",
       "      <td>1.20</td>\n",
       "      <td>$6.00</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>87</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>Coffee</td>\n",
       "      <td>Barista Espresso</td>\n",
       "      <td>Ouro Brasileiro shot</td>\n",
       "      <td>From Rio</td>\n",
       "      <td>1.5 oz</td>\n",
       "      <td>0.60</td>\n",
       "      <td>$3.00</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>88</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>Coffee</td>\n",
       "      <td>Barista Espresso</td>\n",
       "      <td>Ouro Brasileiro shot promo</td>\n",
       "      <td>Ouro promo</td>\n",
       "      <td>16 oz</td>\n",
       "      <td>0.42</td>\n",
       "      <td>$2.10</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>89</td>\n",
       "      <td>Food</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Scone</td>\n",
       "      <td>Ginger Scone promo</td>\n",
       "      <td>Little bit of spice</td>\n",
       "      <td>single</td>\n",
       "      <td>1.72</td>\n",
       "      <td>$2.65</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>88 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    product_id    product_group product_category       product_type  \\\n",
       "0            1  Whole Bean/Teas     Coffee beans      Organic Beans   \n",
       "1            2  Whole Bean/Teas     Coffee beans  House blend Beans   \n",
       "2            3  Whole Bean/Teas     Coffee beans     Espresso Beans   \n",
       "3            4  Whole Bean/Teas     Coffee beans     Espresso Beans   \n",
       "4            5  Whole Bean/Teas     Coffee beans      Gourmet Beans   \n",
       "..         ...              ...              ...                ...   \n",
       "83          84          Add-ons         Flavours      Regular syrup   \n",
       "84          85        Beverages           Coffee   Specialty coffee   \n",
       "85          87        Beverages           Coffee   Barista Espresso   \n",
       "86          88        Beverages           Coffee   Barista Espresso   \n",
       "87          89             Food           Bakery              Scone   \n",
       "\n",
       "                  product_name  \\\n",
       "0          Brazilian - Organic   \n",
       "1     Our Old Time Diner Blend   \n",
       "2               Espresso Roast   \n",
       "3         Primo Espresso Roast   \n",
       "4       Columbian Medium Roast   \n",
       "..                         ...   \n",
       "83             Chocolate syrup   \n",
       "84                  Rio Nights   \n",
       "85        Ouro Brasileiro shot   \n",
       "86  Ouro Brasileiro shot promo   \n",
       "87          Ginger Scone promo   \n",
       "\n",
       "                                  product_description unit_of_measure  \\\n",
       "0      It's like Carnival in a cup. Clean and smooth.           12 oz   \n",
       "1   Out packed blend of beans that is reminiscent ...           12 oz   \n",
       "2           Our house blend for a good espresso shot.            1 lb   \n",
       "3    Our primium single source of hand roasted beans.            1 lb   \n",
       "4            A smooth cup of coffee any time of day.             1 lb   \n",
       "..                                                ...             ...   \n",
       "83                    Bursting with chocolate flavour            pump   \n",
       "84  2 shots of Ouro Brasilerio and pure cane sugar...            6 oz   \n",
       "85                                           From Rio          1.5 oz   \n",
       "86                                         Ouro promo           16 oz   \n",
       "87                                Little bit of spice          single   \n",
       "\n",
       "    current_wholesale_price current_retail_price tax_exempt_yn promo_yn  \\\n",
       "0                     14.40              $18.00              Y        N   \n",
       "1                     14.40              $18.00              Y        N   \n",
       "2                     11.80              $14.75              Y        N   \n",
       "3                     16.36              $20.45              Y        N   \n",
       "4                     12.00              $15.00              Y        N   \n",
       "..                      ...                  ...           ...      ...   \n",
       "83                     0.04               $0.80              Y        N   \n",
       "84                     1.20               $6.00              Y        N   \n",
       "85                     0.60               $3.00              Y        N   \n",
       "86                     0.42               $2.10              Y        Y   \n",
       "87                     1.72               $2.65              N        Y   \n",
       "\n",
       "   new_product_yn  \n",
       "0               N  \n",
       "1               N  \n",
       "2               N  \n",
       "3               N  \n",
       "4               N  \n",
       "..            ...  \n",
       "83              N  \n",
       "84              Y  \n",
       "85              N  \n",
       "86              N  \n",
       "87              N  \n",
       "\n",
       "[88 rows x 12 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"SELECT * FROM coffe_shop.product\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "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>sales_outlet_id</th>\n",
       "      <th>sales_outlet_type</th>\n",
       "      <th>store_square_feet</th>\n",
       "      <th>store_address</th>\n",
       "      <th>store_city</th>\n",
       "      <th>store_state_province</th>\n",
       "      <th>store_telephone</th>\n",
       "      <th>store_postal_code</th>\n",
       "      <th>store_longtitude</th>\n",
       "      <th>store_latitude</th>\n",
       "      <th>manager</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>warehouse</td>\n",
       "      <td>3400.0</td>\n",
       "      <td>164-14 Jamaica Ave</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>NY</td>\n",
       "      <td>972-871-0402</td>\n",
       "      <td>11432</td>\n",
       "      <td>-73.795168</td>\n",
       "      <td>40.705226</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>building</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>retail</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>32-20 Broadway</td>\n",
       "      <td>Long Island City</td>\n",
       "      <td>NY</td>\n",
       "      <td>777-718-3190</td>\n",
       "      <td>11106</td>\n",
       "      <td>-73.924008</td>\n",
       "      <td>40.761196</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Astoria</td>\n",
       "      <td>working</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>retail</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>604 Union Street</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>NY</td>\n",
       "      <td>619-347-5193</td>\n",
       "      <td>11215</td>\n",
       "      <td>-73.983984</td>\n",
       "      <td>40.677645</td>\n",
       "      <td>11.0</td>\n",
       "      <td>Gowanus</td>\n",
       "      <td>building</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>retail</td>\n",
       "      <td>900.0</td>\n",
       "      <td>100 Church Street</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>343-212-5151</td>\n",
       "      <td>10007</td>\n",
       "      <td>-74.01013</td>\n",
       "      <td>40.71329</td>\n",
       "      <td>16.0</td>\n",
       "      <td>Lower Manhattan</td>\n",
       "      <td>working</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>retail</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>122 E Broadway</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>613-555-4989</td>\n",
       "      <td>10002</td>\n",
       "      <td>-73.992687</td>\n",
       "      <td>40.713852</td>\n",
       "      <td>21.0</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>building</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>retail</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>224 E 57th Street</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>287-817-2330</td>\n",
       "      <td>10021</td>\n",
       "      <td>-73.96</td>\n",
       "      <td>40.77</td>\n",
       "      <td>26.0</td>\n",
       "      <td>Upper East Side</td>\n",
       "      <td>building</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8</td>\n",
       "      <td>retail</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>687 9th Avenue</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>652-212-7020</td>\n",
       "      <td>10036</td>\n",
       "      <td>-73.990338</td>\n",
       "      <td>40.761887</td>\n",
       "      <td>31.0</td>\n",
       "      <td>Hell's Kitchen</td>\n",
       "      <td>working</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9</td>\n",
       "      <td>retail</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>175 8th Avenue</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>242-212-0080</td>\n",
       "      <td>10011</td>\n",
       "      <td>-74.000502</td>\n",
       "      <td>40.74276</td>\n",
       "      <td>36.0</td>\n",
       "      <td>Chelsea</td>\n",
       "      <td>building</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10</td>\n",
       "      <td>retail</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>183 W 10th Street</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>674-646-6434</td>\n",
       "      <td>10014</td>\n",
       "      <td>-74.002722</td>\n",
       "      <td>40.734367</td>\n",
       "      <td>41.0</td>\n",
       "      <td>Greenwich Village</td>\n",
       "      <td>building</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sales_outlet_id sales_outlet_type  store_square_feet       store_address  \\\n",
       "0                2         warehouse             3400.0  164-14 Jamaica Ave   \n",
       "1                3            retail             1300.0      32-20 Broadway   \n",
       "2                4            retail             1300.0    604 Union Street   \n",
       "3                5            retail              900.0   100 Church Street   \n",
       "4                6            retail             1000.0      122 E Broadway   \n",
       "5                7            retail             1200.0   224 E 57th Street   \n",
       "6                8            retail             1500.0      687 9th Avenue   \n",
       "7                9            retail             1700.0      175 8th Avenue   \n",
       "8               10            retail             1600.0   183 W 10th Street   \n",
       "\n",
       "         store_city store_state_province store_telephone store_postal_code  \\\n",
       "0           Jamaica                   NY    972-871-0402             11432   \n",
       "1  Long Island City                   NY    777-718-3190             11106   \n",
       "2          Brooklyn                   NY    619-347-5193             11215   \n",
       "3          New York                   NY    343-212-5151             10007   \n",
       "4          New York                   NY    613-555-4989             10002   \n",
       "5          New York                   NY    287-817-2330             10021   \n",
       "6          New York                   NY    652-212-7020             10036   \n",
       "7          New York                   NY    242-212-0080             10011   \n",
       "8          New York                   NY    674-646-6434             10014   \n",
       "\n",
       "  store_longtitude store_latitude  manager       neighborhood    status  \n",
       "0       -73.795168      40.705226      0.0            Jamaica  building  \n",
       "1       -73.924008      40.761196      6.0            Astoria   working  \n",
       "2       -73.983984      40.677645     11.0            Gowanus  building  \n",
       "3        -74.01013       40.71329     16.0    Lower Manhattan   working  \n",
       "4       -73.992687      40.713852     21.0    Lower East Side  building  \n",
       "5           -73.96          40.77     26.0    Upper East Side  building  \n",
       "6       -73.990338      40.761887     31.0     Hell's Kitchen   working  \n",
       "7       -74.000502       40.74276     36.0            Chelsea  building  \n",
       "8       -74.002722      40.734367     41.0  Greenwich Village  building  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"SELECT * FROM coffe_shop.sales_outlet\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <a id='toc1_3_'></a>[Анализ продуктов, заказов и транзакций](#toc0_)\n",
    "### <a id='toc1_3_1_'></a>[Количество транзакций по дням](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "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>date_year</th>\n",
       "      <th>date_month</th>\n",
       "      <th>date_day</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>1688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>1715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1468</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    date_year  date_month  date_day  count\n",
       "0      2019.0         4.0       1.0   1657\n",
       "1      2019.0         4.0       2.0   1638\n",
       "2      2019.0         4.0       3.0   1683\n",
       "3      2019.0         4.0       4.0   1591\n",
       "4      2019.0         4.0       5.0   1634\n",
       "5      2019.0         4.0       6.0   1465\n",
       "6      2019.0         4.0       7.0   1693\n",
       "7      2019.0         4.0       8.0   1846\n",
       "8      2019.0         4.0       9.0   1757\n",
       "9      2019.0         4.0      10.0   1798\n",
       "10     2019.0         4.0      11.0   1707\n",
       "11     2019.0         4.0      12.0   1681\n",
       "12     2019.0         4.0      13.0   1829\n",
       "13     2019.0         4.0      14.0   1733\n",
       "14     2019.0         4.0      15.0   1835\n",
       "15     2019.0         4.0      16.0   1897\n",
       "16     2019.0         4.0      17.0   1809\n",
       "17     2019.0         4.0      18.0   1844\n",
       "18     2019.0         4.0      19.0   1907\n",
       "19     2019.0         4.0      20.0   1780\n",
       "20     2019.0         4.0      21.0   1688\n",
       "21     2019.0         4.0      22.0   1665\n",
       "22     2019.0         4.0      23.0   1686\n",
       "23     2019.0         4.0      24.0   1815\n",
       "24     2019.0         4.0      25.0   1689\n",
       "25     2019.0         4.0      26.0   1715\n",
       "26     2019.0         4.0      27.0   1852\n",
       "27     2019.0         4.0      28.0   1532\n",
       "28     2019.0         4.0      29.0   1468"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT DATE_PART('YEAR', transaction_date::date) AS date_year,\n",
    "              DATE_PART('MONTH', transaction_date::DATE) AS date_month,\n",
    "              DATE_PART('DAY', transaction_date::DATE) AS date_day,\n",
    "              COUNT(transaction_id)\n",
    "       FROM coffe_shop.sales_reciepts\n",
    "       GROUP BY transaction_date\n",
    "       ORDER BY transaction_date;\"\"\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <a id='toc1_3_2_'></a>[Топ 10 самых продаваемых товаров каждый день](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "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>transaction_date</th>\n",
       "      <th>product_name</th>\n",
       "      <th>quantity_sold_per_day</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4/10/2019</td>\n",
       "      <td>Almond Croissant</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4/10/2019</td>\n",
       "      <td>Chocolate Croissant</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4/10/2019</td>\n",
       "      <td>Hazelnut Biscotti</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4/10/2019</td>\n",
       "      <td>Cranberry Scone</td>\n",
       "      <td>20.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4/10/2019</td>\n",
       "      <td>Ginger Scone</td>\n",
       "      <td>18.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>4/9/2019</td>\n",
       "      <td>Chocolate Croissant</td>\n",
       "      <td>26.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>4/9/2019</td>\n",
       "      <td>Cranberry Scone</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>4/9/2019</td>\n",
       "      <td>Ginger Scone</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>4/9/2019</td>\n",
       "      <td>Hazelnut Biscotti</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>4/9/2019</td>\n",
       "      <td>Almond Croissant</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>131 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    transaction_date         product_name  quantity_sold_per_day  rating\n",
       "0          4/10/2019     Almond Croissant                   27.0       1\n",
       "1          4/10/2019  Chocolate Croissant                   24.0       2\n",
       "2          4/10/2019    Hazelnut Biscotti                   21.0       3\n",
       "3          4/10/2019      Cranberry Scone                   20.0       4\n",
       "4          4/10/2019         Ginger Scone                   18.0       5\n",
       "..               ...                  ...                    ...     ...\n",
       "126         4/9/2019  Chocolate Croissant                   26.0       1\n",
       "127         4/9/2019      Cranberry Scone                   21.0       2\n",
       "128         4/9/2019         Ginger Scone                   20.0       3\n",
       "129         4/9/2019    Hazelnut Biscotti                   18.0       4\n",
       "130         4/9/2019     Almond Croissant                    6.0       5\n",
       "\n",
       "[131 rows x 4 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT pi2.transaction_date,\n",
    "\t   p.product_name,\n",
    "\t   SUM(quantity_sold) AS quantity_sold_per_day,\n",
    "\t   RANK() OVER(PARTITION BY transaction_date ORDER BY SUM(quantity_sold) DESC) AS rating\n",
    "FROM coffe_shop.pastry_inventory pi2\n",
    "JOIN coffe_shop.product p ON pi2.product_id=p.product_id \n",
    "GROUP BY p.product_name, pi2.transaction_date\n",
    "ORDER BY pi2.transaction_date, SUM(quantity_sold) DESC;\"\"\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <a id='toc1_3_3_'></a>[Количество проданных и пропавших продуктов по неделям](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "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>week_name</th>\n",
       "      <th>sold</th>\n",
       "      <th>wasted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Week 14</td>\n",
       "      <td>355.0</td>\n",
       "      <td>605.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Week 15</td>\n",
       "      <td>869.0</td>\n",
       "      <td>1637.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Week 16</td>\n",
       "      <td>1042.0</td>\n",
       "      <td>1406.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Week 17</td>\n",
       "      <td>588.0</td>\n",
       "      <td>852.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_name    sold  wasted\n",
       "0   Week 14   355.0   605.0\n",
       "1   Week 15   869.0  1637.0\n",
       "2   Week 16  1042.0  1406.0\n",
       "3   Week 17   588.0   852.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT d.week_name,\n",
    "\t   SUM(pi2.quantity_sold) AS sold,\n",
    "\t   SUM(pi2.waste)  AS wasted\n",
    "FROM coffe_shop.pastry_inventory pi2 \n",
    "JOIN coffe_shop.dates d ON pi2.transaction_date::date=d.transaction_date\n",
    "GROUP BY d.week_name\n",
    "ORDER BY d.week_name;\"\"\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <a id='toc1_3_4_'></a>[Названия регионов , где продавался продукт “Columbian Medium Roast” с последней датой продажи в регионе](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "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>neighborhood</th>\n",
       "      <th>last_transaction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Astoria</td>\n",
       "      <td>2019-04-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hell's Kitchen</td>\n",
       "      <td>2019-04-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lower Manhattan</td>\n",
       "      <td>2019-04-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      neighborhood last_transaction\n",
       "0          Astoria       2019-04-21\n",
       "1   Hell's Kitchen       2019-04-27\n",
       "2  Lower Manhattan       2019-04-26"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT neighborhood,\n",
    "\t          MAX(transaction_date) AS last_transaction\n",
    "       FROM coffe_shop.sales_outlet so\n",
    "       JOIN coffe_shop.sales_reciepts sr ON so.sales_outlet_id=sr.sales_outlet_id\n",
    "       JOIN coffe_shop.product p ON sr.product_id=p.product_id\n",
    "       WHERE p.product_name = 'Columbian Medium Roast'\n",
    "       GROUP BY 1;\"\"\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <a id='toc1_3_5_'></a>[Продукты с упоминанием шоколада в названии](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "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>product_id</th>\n",
       "      <th>product_group</th>\n",
       "      <th>product_category</th>\n",
       "      <th>product_type</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_description</th>\n",
       "      <th>unit_of_measure</th>\n",
       "      <th>current_wholesale_price</th>\n",
       "      <th>current_retail_price</th>\n",
       "      <th>tax_exempt_yn</th>\n",
       "      <th>promo_yn</th>\n",
       "      <th>new_product_yn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19</td>\n",
       "      <td>Whole Bean/Teas</td>\n",
       "      <td>Packaged Chocolate</td>\n",
       "      <td>Drinking Chocolate</td>\n",
       "      <td>Dark chocolate</td>\n",
       "      <td>This drinking chocolate is smooth and creamy.</td>\n",
       "      <td>1 lb</td>\n",
       "      <td>5.12</td>\n",
       "      <td>$6.40</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>58</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>Drinking Chocolate</td>\n",
       "      <td>Hot chocolate</td>\n",
       "      <td>Dark chocolate Rg</td>\n",
       "      <td>Slightly bitter, but still very rich.</td>\n",
       "      <td>12 oz</td>\n",
       "      <td>2.63</td>\n",
       "      <td>$3.50</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>Drinking Chocolate</td>\n",
       "      <td>Hot chocolate</td>\n",
       "      <td>Dark chocolate Lg</td>\n",
       "      <td>Slightly bitter, but still very rich.</td>\n",
       "      <td>16 oz</td>\n",
       "      <td>3.38</td>\n",
       "      <td>$4.50</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>68</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>Drinking Chocolate</td>\n",
       "      <td>Seasonal drink</td>\n",
       "      <td>Happy Holidays hot chocolate</td>\n",
       "      <td>Candy cane and hot chocolate, perfect.</td>\n",
       "      <td>8 oz</td>\n",
       "      <td>2.81</td>\n",
       "      <td>$3.75</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id    product_group    product_category        product_type  \\\n",
       "0          19  Whole Bean/Teas  Packaged Chocolate  Drinking Chocolate   \n",
       "1          58        Beverages  Drinking Chocolate       Hot chocolate   \n",
       "2          59        Beverages  Drinking Chocolate       Hot chocolate   \n",
       "3          68        Beverages  Drinking Chocolate      Seasonal drink   \n",
       "\n",
       "                   product_name  \\\n",
       "0                Dark chocolate   \n",
       "1             Dark chocolate Rg   \n",
       "2             Dark chocolate Lg   \n",
       "3  Happy Holidays hot chocolate   \n",
       "\n",
       "                             product_description unit_of_measure  \\\n",
       "0  This drinking chocolate is smooth and creamy.            1 lb   \n",
       "1         Slightly bitter, but still very rich.            12 oz   \n",
       "2         Slightly bitter, but still very rich.            16 oz   \n",
       "3         Candy cane and hot chocolate, perfect.            8 oz   \n",
       "\n",
       "   current_wholesale_price current_retail_price tax_exempt_yn promo_yn  \\\n",
       "0                     5.12               $6.40              Y        N   \n",
       "1                     2.63               $3.50              Y        N   \n",
       "2                     3.38               $4.50              Y        N   \n",
       "3                     2.81               $3.75              Y        Y   \n",
       "\n",
       "  new_product_yn  \n",
       "0              N  \n",
       "1              N  \n",
       "2              N  \n",
       "3              Y  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT *\n",
    "       FROM coffe_shop.product \n",
    "       WHERE product_name LIKE '%%choco%%';\"\"\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <a id='toc1_3_6_'></a>[Сумма заказов в каждом городе и магазине](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "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>store_city</th>\n",
       "      <th>store_address</th>\n",
       "      <th>sale_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>687 9th Avenue</td>\n",
       "      <td>79528.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Long Island City</td>\n",
       "      <td>32-20 Broadway</td>\n",
       "      <td>77213.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>New York</td>\n",
       "      <td>100 Church Street</td>\n",
       "      <td>76894.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         store_city      store_address  sale_amount\n",
       "0          New York     687 9th Avenue      79528.0\n",
       "1  Long Island City     32-20 Broadway      77213.0\n",
       "2          New York  100 Church Street      76894.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT so.store_city,\n",
    "\t   \t\t  so.store_address,\n",
    "\t   \t\t  round(SUM(sr.line_item_amount)) AS sale_amount\n",
    "\t   FROM coffe_shop.sales_outlet so\n",
    "\t   RIGHT JOIN coffe_shop.sales_reciepts sr ON so.sales_outlet_id=sr.sales_outlet_id\n",
    "       GROUP BY so.store_address, so.store_city\n",
    "\t   ORDER BY sale_amount DESC;\"\"\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <a id='toc1_3_7_'></a>[Выручка и количество проданных товаров по магазинам за каждый день](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "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>transaction_date</th>\n",
       "      <th>sales_outlet_id</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>total_quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>3</td>\n",
       "      <td>2571.40</td>\n",
       "      <td>829.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>8</td>\n",
       "      <td>2560.15</td>\n",
       "      <td>817.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>5</td>\n",
       "      <td>2330.45</td>\n",
       "      <td>745.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>3</td>\n",
       "      <td>2701.50</td>\n",
       "      <td>871.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>8</td>\n",
       "      <td>2477.60</td>\n",
       "      <td>803.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>2019-04-28</td>\n",
       "      <td>5</td>\n",
       "      <td>2149.00</td>\n",
       "      <td>695.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>2019-04-28</td>\n",
       "      <td>3</td>\n",
       "      <td>2540.75</td>\n",
       "      <td>833.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>3</td>\n",
       "      <td>2618.10</td>\n",
       "      <td>828.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>8</td>\n",
       "      <td>1826.35</td>\n",
       "      <td>591.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>5</td>\n",
       "      <td>1997.95</td>\n",
       "      <td>647.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>87 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   transaction_date  sales_outlet_id  total_amount  total_quantity\n",
       "0        2019-04-01                3       2571.40           829.0\n",
       "1        2019-04-01                8       2560.15           817.0\n",
       "2        2019-04-01                5       2330.45           745.0\n",
       "3        2019-04-02                3       2701.50           871.0\n",
       "4        2019-04-02                8       2477.60           803.0\n",
       "..              ...              ...           ...             ...\n",
       "82       2019-04-28                5       2149.00           695.0\n",
       "83       2019-04-28                3       2540.75           833.0\n",
       "84       2019-04-29                3       2618.10           828.0\n",
       "85       2019-04-29                8       1826.35           591.0\n",
       "86       2019-04-29                5       1997.95           647.0\n",
       "\n",
       "[87 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"WITH \n",
    "    vitrin  AS \n",
    "\t(\n",
    "\t\tSELECT\n",
    "\t\ttransaction_date,\n",
    "\t\tsales_outlet_id,\n",
    "\t\tsum(line_item_amount) AS sum_line_item_amount,\n",
    "\t\tsum(quantity) AS sum_quantity\n",
    "\t\tFROM\n",
    "\t\tcoffe_shop.sales_reciepts r\n",
    "\t\tGROUP BY\n",
    "\t\ttransaction_date ,sales_outlet_id\n",
    "\t)\n",
    "\tSELECT \n",
    "\ttransaction_date,\n",
    "\tsales_outlet_id,\n",
    "\tsum_line_item_amount AS total_amount,\n",
    "\tsum_quantity AS total_quantity\n",
    "\tFROM \n",
    "\tvitrin\n",
    " ORDER BY transaction_date;\"\"\", con=con_postgres)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <a id='toc1_4_'></a>[Анализ клиентов](#toc0_)\n",
    "### <a id='toc1_4_1_'></a>[Электронные адреса клиентов и их домены](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "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>customer_name</th>\n",
       "      <th>email</th>\n",
       "      <th>email_domen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kelly Key</td>\n",
       "      <td>Venus@adipiscing.edu</td>\n",
       "      <td>adipiscing.edu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Clark Schroeder</td>\n",
       "      <td>Nora@fames.gov</td>\n",
       "      <td>fames.gov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Elvis Cardenas</td>\n",
       "      <td>Brianna@tellus.edu</td>\n",
       "      <td>tellus.edu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rafael Estes</td>\n",
       "      <td>Ina@non.gov</td>\n",
       "      <td>non.gov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Colin Lynn</td>\n",
       "      <td>Dale@Integer.com</td>\n",
       "      <td>Integer.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2241</th>\n",
       "      <td>Rae</td>\n",
       "      <td>Kylie@facilisi.org</td>\n",
       "      <td>facilisi.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2242</th>\n",
       "      <td>April</td>\n",
       "      <td>Daniel@placerat.us</td>\n",
       "      <td>placerat.us</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2243</th>\n",
       "      <td>Clementine</td>\n",
       "      <td>Dieter@ac.us</td>\n",
       "      <td>ac.us</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2244</th>\n",
       "      <td>Linda</td>\n",
       "      <td>Dillon@pretium.gov</td>\n",
       "      <td>pretium.gov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2245</th>\n",
       "      <td>Moana</td>\n",
       "      <td>Herrod@ultrices.gov</td>\n",
       "      <td>ultrices.gov</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2246 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        customer_name                 email     email_domen\n",
       "0           Kelly Key  Venus@adipiscing.edu  adipiscing.edu\n",
       "1     Clark Schroeder        Nora@fames.gov       fames.gov\n",
       "2      Elvis Cardenas    Brianna@tellus.edu      tellus.edu\n",
       "3        Rafael Estes           Ina@non.gov         non.gov\n",
       "4          Colin Lynn      Dale@Integer.com     Integer.com\n",
       "...               ...                   ...             ...\n",
       "2241              Rae    Kylie@facilisi.org    facilisi.org\n",
       "2242            April    Daniel@placerat.us     placerat.us\n",
       "2243       Clementine          Dieter@ac.us           ac.us\n",
       "2244            Linda    Dillon@pretium.gov     pretium.gov\n",
       "2245            Moana   Herrod@ultrices.gov    ultrices.gov\n",
       "\n",
       "[2246 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT customer_name,\n",
    "              email,\n",
    "\t          split_part(email, '@', 2) AS email_domen\n",
    "       FROM coffe_shop.customer;\"\"\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <a id='toc1_4_2_'></a>[Справочник клиентов](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthdate</th>\n",
       "      <th>customer_age</th>\n",
       "      <th>loyalty_card_number</th>\n",
       "      <th>card_number_new</th>\n",
       "      <th>transaction_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Elvis</td>\n",
       "      <td>Cardenas</td>\n",
       "      <td>Male</td>\n",
       "      <td>1950-09-30</td>\n",
       "      <td>73.0</td>\n",
       "      <td>459-375-9187</td>\n",
       "      <td>4593759187</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Francesca</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "      <td>1990-09-21</td>\n",
       "      <td>33.0</td>\n",
       "      <td>174-904-8035</td>\n",
       "      <td>1749048035</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Raya</td>\n",
       "      <td>Sanders</td>\n",
       "      <td>Female</td>\n",
       "      <td>1981-11-16</td>\n",
       "      <td>42.0</td>\n",
       "      <td>878-481-9768</td>\n",
       "      <td>8784819768</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ferdinand</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "      <td>1973-02-22</td>\n",
       "      <td>50.0</td>\n",
       "      <td>238-448-5604</td>\n",
       "      <td>2384485604</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Germaine</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "      <td>1993-01-20</td>\n",
       "      <td>31.0</td>\n",
       "      <td>241-694-6251</td>\n",
       "      <td>2416946251</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2241</th>\n",
       "      <td>Inez</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "      <td>1986-07-12</td>\n",
       "      <td>37.0</td>\n",
       "      <td>787-638-7405</td>\n",
       "      <td>7876387405</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2242</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>Langley</td>\n",
       "      <td>Female</td>\n",
       "      <td>1957-10-06</td>\n",
       "      <td>66.0</td>\n",
       "      <td>291-731-7772</td>\n",
       "      <td>2917317772</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2243</th>\n",
       "      <td>Paula</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "      <td>1956-08-13</td>\n",
       "      <td>67.0</td>\n",
       "      <td>830-360-5082</td>\n",
       "      <td>8303605082</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2244</th>\n",
       "      <td>Sean</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "      <td>1980-01-16</td>\n",
       "      <td>44.0</td>\n",
       "      <td>609-773-7717</td>\n",
       "      <td>6097737717</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2245</th>\n",
       "      <td>Medge</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "      <td>1970-05-14</td>\n",
       "      <td>53.0</td>\n",
       "      <td>743-441-9073</td>\n",
       "      <td>7434419073</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2246 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     first_name last_name  gender   birthdate  customer_age  \\\n",
       "0         Elvis  Cardenas    Male  1950-09-30          73.0   \n",
       "1     Francesca            Female  1990-09-21          33.0   \n",
       "2          Raya   Sanders  Female  1981-11-16          42.0   \n",
       "3     Ferdinand              Male  1973-02-22          50.0   \n",
       "4      Germaine            Female  1993-01-20          31.0   \n",
       "...         ...       ...     ...         ...           ...   \n",
       "2241       Inez            Female  1986-07-12          37.0   \n",
       "2242     Nevada   Langley  Female  1957-10-06          66.0   \n",
       "2243      Paula            Female  1956-08-13          67.0   \n",
       "2244       Sean              Male  1980-01-16          44.0   \n",
       "2245      Medge            Female  1970-05-14          53.0   \n",
       "\n",
       "     loyalty_card_number card_number_new  transaction_qty  \n",
       "0           459-375-9187      4593759187               31  \n",
       "1           174-904-8035      1749048035               31  \n",
       "2           878-481-9768      8784819768               31  \n",
       "3           238-448-5604      2384485604               29  \n",
       "4           241-694-6251      2416946251               28  \n",
       "...                  ...             ...              ...  \n",
       "2241        787-638-7405      7876387405                1  \n",
       "2242        291-731-7772      2917317772                1  \n",
       "2243        830-360-5082      8303605082                1  \n",
       "2244        609-773-7717      6097737717                1  \n",
       "2245        743-441-9073      7434419073                0  \n",
       "\n",
       "[2246 rows x 8 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT\n",
    "\t   split_part(customer_name, ' ', 1) as first_name,\n",
    "\t   split_part(customer_name, ' ', 2) as last_name,\n",
    "       (CASE\n",
    "\t   \t\tWHEN gender = 'M' THEN 'Male'\n",
    "\t   \t\tELSE 'Female'\n",
    "\t   END) AS gender,\n",
    "       birthdate,\n",
    "\t   date_part('year',AGE(now(), birthdate::date)) AS customer_age,\n",
    "       loyalty_card_number,\n",
    "\t   REPLACE(loyalty_card_number, '-', '') AS card_number_new,\n",
    "\t   COUNT(transaction_id) AS transaction_qty\n",
    "FROM coffe_shop.customer c\n",
    "LEFT JOIN coffe_shop.sales_reciepts sr \n",
    "ON c.customer_id = sr.customer_id\n",
    "GROUP BY c.customer_name, loyalty_card_number, gender, birthdate\n",
    "ORDER BY transaction_qty DESC, first_name;\"\"\", con=con_postgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <a id='toc1_4_3_'></a>[Количество клиентов по поколениям](#toc0_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "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>generation</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Baby Boomers</td>\n",
       "      <td>529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gen X</td>\n",
       "      <td>522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Older Millennials</td>\n",
       "      <td>515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gen Z</td>\n",
       "      <td>382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Younger Millennials</td>\n",
       "      <td>298</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            generation  count\n",
       "0         Baby Boomers    529\n",
       "1                Gen X    522\n",
       "2    Older Millennials    515\n",
       "3                Gen Z    382\n",
       "4  Younger Millennials    298"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"SELECT generation,\n",
    "\t   COUNT(customer_id)\n",
    "FROM coffe_shop.generations g\n",
    "JOIN coffe_shop.customer c ON g.birth_year=c.birth_year\n",
    "GROUP BY generation\n",
    "ORDER BY count DESC;\"\"\", con=con_postgres)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}