Seller_analysis/Seller_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1b2cac47-2f03-433f-b636-f537d40f3bda",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dee989ff-4401-43c3-abd1-978c56a15043",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: postgres@olist'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "host = \"localhost\"\n",
    "database = \"olist\"\n",
    "user = \"postgres\"\n",
    "password = \"sql123\"\n",
    "connection_string = f\"postgresql://{user}:{password}@{host}/{database}\"\n",
    "%sql $connection_string"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "128ff40f-70a1-439b-af39-79b287a4f381",
   "metadata": {},
   "source": [
    "### **Sellers by state**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "feb46b67-51c8-43c9-bdc4-9737d44aae6c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/olist\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>seller_state</th>\n",
       "            <th>number_of_sellers</th>\n",
       "            <th>rank</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>SP</td>\n",
       "            <td>1849</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PR</td>\n",
       "            <td>349</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MG</td>\n",
       "            <td>244</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SC</td>\n",
       "            <td>190</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RJ</td>\n",
       "            <td>171</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RS</td>\n",
       "            <td>129</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GO</td>\n",
       "            <td>40</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DF</td>\n",
       "            <td>30</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ES</td>\n",
       "            <td>23</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BA</td>\n",
       "            <td>19</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SP', 1849, 1),\n",
       " ('PR', 349, 2),\n",
       " ('MG', 244, 3),\n",
       " ('SC', 190, 4),\n",
       " ('RJ', 171, 5),\n",
       " ('RS', 129, 6),\n",
       " ('GO', 40, 7),\n",
       " ('DF', 30, 8),\n",
       " ('ES', 23, 9),\n",
       " ('BA', 19, 10)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT seller_state,\n",
    "       number_of_sellers,\n",
    "       RANK() OVER(ORDER BY number_of_sellers DESC)\n",
    "FROM  (SELECT seller_state,\n",
    "              COUNT(seller_id) AS number_of_sellers\n",
    "       FROM sellers\n",
    "       GROUP BY seller_state) AS nos\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64693ce4-00df-4ef0-b863-41095cdf1960",
   "metadata": {},
   "source": [
    "### **Top 5 sellers by average shipping time**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4b42e600-0d36-42b6-9cbe-7a4da67ab5cb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/olist\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>seller_id</th>\n",
       "            <th>avg_del_time</th>\n",
       "            <th>ship_limit_time</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>89de2d6f23e9746ff309705b23581faa</td>\n",
       "            <td>-413.78876</td>\n",
       "            <td>148.53955</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1fa2d3def6adfa70e58c276bb64fe5bb</td>\n",
       "            <td>-1.0680555</td>\n",
       "            <td>144.33945</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1927cdb9fa74f5270097f6250e8e8f34</td>\n",
       "            <td>-0.6963889</td>\n",
       "            <td>96.12444</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>b19c48688808720822399ffa9f2dbe2f</td>\n",
       "            <td>-0.48055556</td>\n",
       "            <td>144.17473</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>0f94588695d71662beec8d883ffacf09</td>\n",
       "            <td>1.2702777</td>\n",
       "            <td>144.14223</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('89de2d6f23e9746ff309705b23581faa', -413.78876, 148.53955),\n",
       " ('1fa2d3def6adfa70e58c276bb64fe5bb', -1.0680555, 144.33945),\n",
       " ('1927cdb9fa74f5270097f6250e8e8f34', -0.6963889, 96.12444),\n",
       " ('b19c48688808720822399ffa9f2dbe2f', -0.48055556, 144.17473),\n",
       " ('0f94588695d71662beec8d883ffacf09', 1.2702777, 144.14223)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH ship_interval AS(SELECT seller_id,\n",
    "                          order_id,\n",
    "                          EXTRACT(EPOCH FROM (order_delivered_carrier - order_purchase)::interval)/3600 AS delivery_interval,\n",
    "                          EXTRACT(EPOCH FROM (shipping_limit_date - order_purchase)::interval)/3600 AS shipping_limit_interval\n",
    "                      FROM(SELECT order_id,\n",
    "                                  seller_id,\n",
    "                                  order_purchase,\n",
    "                                  order_delivered_carrier,\n",
    "                                  shipping_limit_date\n",
    "                           FROM order_items\n",
    "                           JOIN orders USING(order_id)\n",
    "                           JOIN sellers USING(seller_id)\n",
    "                           WHERE order_status IN('delivered')) AS ship_time)\n",
    "SELECT seller_id,\n",
    "       AVG(delivery_interval)::real AS avg_del_time,\n",
    "       AVG(shipping_limit_interval)::real AS ship_limit_time\n",
    "FROM ship_interval\n",
    "GROUP BY seller_id\n",
    "ORDER BY avg_del_time \n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ef1318e-91c4-47bf-9145-3e234cbab7e4",
   "metadata": {},
   "source": [
    "### **Categories per seller**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d934781e-0833-4ae5-8439-9f1edabad16d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/olist\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>avg_categories</th>\n",
       "            <th>median_categories</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>35.8794830371567044</td>\n",
       "            <td>7.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('35.8794830371567044'), 7.0)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH seller_category AS(SELECT seller_id,\n",
    "                               COUNT(product_category) AS num_of_cat\n",
    "                        FROM sellers\n",
    "                        JOIN order_items USING(seller_id)\n",
    "                        JOIN products USING(product_id)\n",
    "                        GROUP BY seller_id)\n",
    "SELECT AVG(num_of_cat) AS avg_categories,\n",
    "       percentile_cont(.5)\n",
    "       WITHIN GROUP (ORDER BY num_of_cat) AS median_categories\n",
    "FROM seller_category\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fec3c618-5753-4be7-a07e-be4df635c566",
   "metadata": {},
   "source": [
    "### **Average sales per order by seller**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8e9323be-842c-4c56-ac68-1d762c974637",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/olist\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>seller_id</th>\n",
       "            <th>avg_order_rev</th>\n",
       "            <th>rank</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>b37c4c02bda3161a7546a4e6d222d5b2</td>\n",
       "            <td>7560.0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>e3b4998c7a498169dc7bce44e6bb6277</td>\n",
       "            <td>6735.0</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>80ceebb4ee9b31afb6c6a916a574a1e2</td>\n",
       "            <td>6729.0</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ee27a8f15b1dded4d213a468ba4eb391</td>\n",
       "            <td>6499.0</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>59417c56835dd8e2e72f91f809cd4092</td>\n",
       "            <td>4799.0</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>59417c56835dd8e2e72f91f809cd4092</td>\n",
       "            <td>4690.0</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>c72de06d72748d1a0dfb2125be43ba63</td>\n",
       "            <td>4590.0</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>b37c4c02bda3161a7546a4e6d222d5b2</td>\n",
       "            <td>4475.0</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>512d298ac2a96d1931b6bd30aa21f61d</td>\n",
       "            <td>4399.87</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>0873d9f8f36123f8d910f4760e788cfb</td>\n",
       "            <td>4170.0</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('b37c4c02bda3161a7546a4e6d222d5b2', 7560.0, 1),\n",
       " ('e3b4998c7a498169dc7bce44e6bb6277', 6735.0, 2),\n",
       " ('80ceebb4ee9b31afb6c6a916a574a1e2', 6729.0, 3),\n",
       " ('ee27a8f15b1dded4d213a468ba4eb391', 6499.0, 4),\n",
       " ('59417c56835dd8e2e72f91f809cd4092', 4799.0, 5),\n",
       " ('59417c56835dd8e2e72f91f809cd4092', 4690.0, 6),\n",
       " ('c72de06d72748d1a0dfb2125be43ba63', 4590.0, 7),\n",
       " ('b37c4c02bda3161a7546a4e6d222d5b2', 4475.0, 8),\n",
       " ('512d298ac2a96d1931b6bd30aa21f61d', 4399.87, 9),\n",
       " ('0873d9f8f36123f8d910f4760e788cfb', 4170.0, 10)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT seller_id,\n",
    "       avg_order_rev,\n",
    "       RANK() OVER(ORDER BY avg_order_rev DESC) AS rank\n",
    "FROM(WITH order_unit AS(SELECT seller_id,\n",
    "                               price,\n",
    "                               COUNT(order_id) AS total_orders,\n",
    "                               SUM(order_item_id) AS total_units\n",
    "                        FROM sellers\n",
    "                        JOIN order_items USING(seller_id)\n",
    "                        GROUP BY seller_id, price),\n",
    "    seller_unique AS(SELECT seller_id,\n",
    "                            (price*total_units)::real AS revenue,\n",
    "                            total_orders\n",
    "                     FROM order_unit)\n",
    "\n",
    "    SELECT seller_id,\n",
    "           (revenue/total_orders)::real AS avg_order_rev\n",
    "    FROM seller_unique) AS seller_avg\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5b24c4e-ba11-4f02-929a-f16ec17b27c8",
   "metadata": {},
   "source": [
    "### **Top 5 sellers top 3 markets**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6f1e40e3-575c-400c-a138-0e2385739f0c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/olist\n",
      "15 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>seller_id</th>\n",
       "            <th>customer_state</th>\n",
       "            <th>state_revenue</th>\n",
       "            <th>rank</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4869f7a5dfa277a7dca6462dcf3b52b2</td>\n",
       "            <td>SP</td>\n",
       "            <td>91269.52</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4869f7a5dfa277a7dca6462dcf3b52b2</td>\n",
       "            <td>RJ</td>\n",
       "            <td>39960.938</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4869f7a5dfa277a7dca6462dcf3b52b2</td>\n",
       "            <td>MG</td>\n",
       "            <td>24398.865</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4a3ca9315b744ce9f8e9374361493884</td>\n",
       "            <td>SP</td>\n",
       "            <td>97923.86</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4a3ca9315b744ce9f8e9374361493884</td>\n",
       "            <td>RJ</td>\n",
       "            <td>34929.03</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4a3ca9315b744ce9f8e9374361493884</td>\n",
       "            <td>MG</td>\n",
       "            <td>28982.508</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>53243585a1d6dc2643021fd1853d8905</td>\n",
       "            <td>SP</td>\n",
       "            <td>58100.15</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>53243585a1d6dc2643021fd1853d8905</td>\n",
       "            <td>RJ</td>\n",
       "            <td>30554.172</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>53243585a1d6dc2643021fd1853d8905</td>\n",
       "            <td>BA</td>\n",
       "            <td>26564.33</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "            <td>SP</td>\n",
       "            <td>115870.59</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "            <td>RJ</td>\n",
       "            <td>61318.992</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "            <td>BA</td>\n",
       "            <td>20978.963</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>da8622b14eb17ae2831f4ac5b9dab84a</td>\n",
       "            <td>SP</td>\n",
       "            <td>93375.36</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>da8622b14eb17ae2831f4ac5b9dab84a</td>\n",
       "            <td>MG</td>\n",
       "            <td>28836.824</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>da8622b14eb17ae2831f4ac5b9dab84a</td>\n",
       "            <td>RJ</td>\n",
       "            <td>28352.11</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('4869f7a5dfa277a7dca6462dcf3b52b2', 'SP', 91269.52, 1),\n",
       " ('4869f7a5dfa277a7dca6462dcf3b52b2', 'RJ', 39960.938, 2),\n",
       " ('4869f7a5dfa277a7dca6462dcf3b52b2', 'MG', 24398.865, 3),\n",
       " ('4a3ca9315b744ce9f8e9374361493884', 'SP', 97923.86, 1),\n",
       " ('4a3ca9315b744ce9f8e9374361493884', 'RJ', 34929.03, 2),\n",
       " ('4a3ca9315b744ce9f8e9374361493884', 'MG', 28982.508, 3),\n",
       " ('53243585a1d6dc2643021fd1853d8905', 'SP', 58100.15, 1),\n",
       " ('53243585a1d6dc2643021fd1853d8905', 'RJ', 30554.172, 2),\n",
       " ('53243585a1d6dc2643021fd1853d8905', 'BA', 26564.33, 3),\n",
       " ('7c67e1448b00f6e969d365cea6b010ab', 'SP', 115870.59, 1),\n",
       " ('7c67e1448b00f6e969d365cea6b010ab', 'RJ', 61318.992, 2),\n",
       " ('7c67e1448b00f6e969d365cea6b010ab', 'BA', 20978.963, 3),\n",
       " ('da8622b14eb17ae2831f4ac5b9dab84a', 'SP', 93375.36, 1),\n",
       " ('da8622b14eb17ae2831f4ac5b9dab84a', 'MG', 28836.824, 2),\n",
       " ('da8622b14eb17ae2831f4ac5b9dab84a', 'RJ', 28352.11, 3)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM(WITH seller_market AS(SELECT seller_id,\n",
    "                             customer_state,\n",
    "                             SUM(revenue) AS state_revenue\n",
    "                      FROM(SELECT seller_id, \n",
    "                                  (price * order_item_id)::real AS revenue,\n",
    "                                  customer_state\n",
    "                           FROM sellers\n",
    "                           JOIN order_items USING(seller_id)\n",
    "                           JOIN orders USING(order_id)\n",
    "                           JOIN customers USING(customer_id)) AS revenue_per_state\n",
    "                           GROUP BY seller_id, customer_state)\n",
    "SELECT seller_id, \n",
    "       customer_state,\n",
    "       state_revenue,\n",
    "       RANK() OVER(PARTITION BY seller_id ORDER BY state_revenue DESC)\n",
    "FROM seller_market\n",
    "WHERE seller_id IN(SELECT seller_id\n",
    "                   FROM(SELECT seller_id,\n",
    "                               SUM(revenue) AS total_rev\n",
    "                        FROM(SELECT seller_id, \n",
    "                                    (order_item_id * price)::real AS revenue\n",
    "                              FROM sellers\n",
    "                              JOIN order_items USING(seller_id)) AS rev\n",
    "                              GROUP BY seller_id\n",
    "                              ORDER BY total_rev DESC) AS seller_rev\n",
    "                              LIMIT 5)) AS rank_market_revenue\n",
    "WHERE rank < 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "753c1065-ff5c-4026-9d98-17958c3e2556",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}