Practice 3 - Average review score per seller.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Practice 3 - Average review score per seller\n",
    "This notebook uses three tables in the Olist database which are olist_orders_dataset (renamed to orders), olist_order_items_dataset (renamed to order_items), and olist_order_reviews_dataset (renamed to order_reviews).<br>\n",
    "\n",
    "Main question:<br>\n",
    "\n",
    "**What is the average review score of each seller?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect and load in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: root@olist'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql mysql+mysqlconnector://root:***@localhost/olist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Relational Schema <br>\n",
    "<img src=\"files/photos/P3.png\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL queries\n",
    "### Confusing review data\n",
    "Customers can complete a satisfaction survey sent to them after they receive the order or the deadline is due ([More from Kaggle data description](https://www.kaggle.com/olistbr/brazilian-ecommerce). Since every created order has a deadline (order_estimated_delivery_date) no matter what its status is, customers are able to take the survey even when they don't receive the order. Is that true?<br>First, let's check if there's any null value in review_score column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>review_score_null</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0,)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(review_score) AS review_score_null\n",
    "FROM order_reviews\n",
    "WHERE review_score = 0 OR review_score IS NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Surprisingly, there's no 0 review score and every order has at least one review score. I want to know how many scores (1 to 5) there are in each order status group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>order_status</th>\n",
       "        <th>score_1</th>\n",
       "        <th>score_2</th>\n",
       "        <th>score_3</th>\n",
       "        <th>score_4</th>\n",
       "        <th>score_5</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>approved</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>canceled</td>\n",
       "        <td>436</td>\n",
       "        <td>45</td>\n",
       "        <td>50</td>\n",
       "        <td>27</td>\n",
       "        <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>created</td>\n",
       "        <td>4</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>delivered</td>\n",
       "        <td>9754</td>\n",
       "        <td>3015</td>\n",
       "        <td>8056</td>\n",
       "        <td>19040</td>\n",
       "        <td>57150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>invoiced</td>\n",
       "        <td>235</td>\n",
       "        <td>26</td>\n",
       "        <td>16</td>\n",
       "        <td>15</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>processing</td>\n",
       "        <td>261</td>\n",
       "        <td>19</td>\n",
       "        <td>9</td>\n",
       "        <td>6</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>shipped</td>\n",
       "        <td>693</td>\n",
       "        <td>85</td>\n",
       "        <td>121</td>\n",
       "        <td>90</td>\n",
       "        <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>unavailable</td>\n",
       "        <td>474</td>\n",
       "        <td>45</td>\n",
       "        <td>35</td>\n",
       "        <td>21</td>\n",
       "        <td>36</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('approved', Decimal('1'), Decimal('0'), Decimal('0'), Decimal('1'), Decimal('0')),\n",
       " ('canceled', Decimal('436'), Decimal('45'), Decimal('50'), Decimal('27'), Decimal('71')),\n",
       " ('created', Decimal('4'), Decimal('0'), Decimal('0'), Decimal('0'), Decimal('1')),\n",
       " ('delivered', Decimal('9754'), Decimal('3015'), Decimal('8056'), Decimal('19040'), Decimal('57150')),\n",
       " ('invoiced', Decimal('235'), Decimal('26'), Decimal('16'), Decimal('15'), Decimal('26')),\n",
       " ('processing', Decimal('261'), Decimal('19'), Decimal('9'), Decimal('6'), Decimal('7')),\n",
       " ('shipped', Decimal('693'), Decimal('85'), Decimal('121'), Decimal('90'), Decimal('129')),\n",
       " ('unavailable', Decimal('474'), Decimal('45'), Decimal('35'), Decimal('21'), Decimal('36'))]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT order_status,\n",
    "       SUM(IF(review_score = 1,1,0)) AS score_1,\n",
    "       SUM(IF(review_score = 2,1,0)) AS score_2,\n",
    "       SUM(IF(review_score = 3,1,0)) AS score_3,\n",
    "       SUM(IF(review_score = 4,1,0)) AS score_4,\n",
    "       SUM(IF(review_score = 5,1,0)) AS score_5\n",
    "FROM (SELECT order_status, review_score\n",
    "      FROM orders o JOIN order_reviews re\n",
    "      ON o.order_id = re.order_id) a\n",
    "GROUP BY order_status\n",
    "ORDER BY order_status;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>num_reviewed_orders</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99441</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(99441,)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(DISTINCT order_id) AS num_reviewed_orders\n",
    "FROM order_reviews;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems unrealistic that all customers answered satisfaction surveys for every order. To confirm this, I would need to check when these review scores were assigned. As usual, let's check for null values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>order_estimated_delivery_date_null</th>\n",
       "        <th>review_creation_date_null</th>\n",
       "        <th>review_answer_timestamp_null</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('0'), Decimal('0'), Decimal('0'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(IF(order_estimated_delivery_date = '0000-00-00 00:00:00',1,0))\n",
    "       AS order_estimated_delivery_date_null,\n",
    "       SUM(IF(review_creation_date = '0000-00-00 00:00:00',1,0))\n",
    "       AS review_creation_date_null,\n",
    "       SUM(IF(review_answer_timestamp = '0000-00-00 00:00:00',1,0))\n",
    "       AS review_answer_timestamp_null\n",
    "FROM orders o JOIN order_reviews re\n",
    "ON o.order_id = re.order_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "My question is are these review scores assigned before or after the deadline?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>order_status</th>\n",
       "        <th>after_deadline</th>\n",
       "        <th>before_deadline</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>approved</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>canceled</td>\n",
       "        <td>526</td>\n",
       "        <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>created</td>\n",
       "        <td>5</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>delivered</td>\n",
       "        <td>14660</td>\n",
       "        <td>82355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>invoiced</td>\n",
       "        <td>311</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>processing</td>\n",
       "        <td>299</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>shipped</td>\n",
       "        <td>1054</td>\n",
       "        <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>unavailable</td>\n",
       "        <td>604</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('approved', Decimal('2'), Decimal('0')),\n",
       " ('canceled', Decimal('526'), Decimal('103')),\n",
       " ('created', Decimal('5'), Decimal('0')),\n",
       " ('delivered', Decimal('14660'), Decimal('82355')),\n",
       " ('invoiced', Decimal('311'), Decimal('7')),\n",
       " ('processing', Decimal('299'), Decimal('3')),\n",
       " ('shipped', Decimal('1054'), Decimal('64')),\n",
       " ('unavailable', Decimal('604'), Decimal('7'))]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT order_status,\n",
    "       SUM(IF(TIMESTAMPDIFF(\n",
    "           hour,order_estimated_delivery_date,review_answer_timestamp)\n",
    "              >= 0,1,0)) AS after_deadline,\n",
    "       SUM(IF(TIMESTAMPDIFF(\n",
    "           hour,order_estimated_delivery_date,review_answer_timestamp)\n",
    "              < 0,1,0)) AS before_deadline\n",
    "FROM orders o JOIN order_reviews re\n",
    "ON o.order_id = re.order_id\n",
    "GROUP BY order_status\n",
    "ORDER BY order_status;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we see that customers can somehow review an order even when the deadline is not due. Another test: are these review scores assigned before or after the survey created?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>order_status</th>\n",
       "        <th>after_survey</th>\n",
       "        <th>before_survey</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>approved</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>canceled</td>\n",
       "        <td>629</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>created</td>\n",
       "        <td>5</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>delivered</td>\n",
       "        <td>97015</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>invoiced</td>\n",
       "        <td>318</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>processing</td>\n",
       "        <td>302</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>shipped</td>\n",
       "        <td>1118</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>unavailable</td>\n",
       "        <td>611</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('approved', Decimal('2'), Decimal('0')),\n",
       " ('canceled', Decimal('629'), Decimal('0')),\n",
       " ('created', Decimal('5'), Decimal('0')),\n",
       " ('delivered', Decimal('97015'), Decimal('0')),\n",
       " ('invoiced', Decimal('318'), Decimal('0')),\n",
       " ('processing', Decimal('302'), Decimal('0')),\n",
       " ('shipped', Decimal('1118'), Decimal('0')),\n",
       " ('unavailable', Decimal('611'), Decimal('0'))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT order_status,\n",
    "       SUM(IF(TIMESTAMPDIFF(\n",
    "           hour,review_creation_date,review_answer_timestamp)\n",
    "              >= 0,1,0)) AS after_survey,\n",
    "       SUM(IF(TIMESTAMPDIFF(\n",
    "           hour,review_creation_date,review_answer_timestamp)\n",
    "              < 0,1,0)) AS before_survey\n",
    "FROM orders o JOIN order_reviews re\n",
    "ON o.order_id = re.order_id\n",
    "GROUP BY order_status\n",
    "ORDER BY order_status;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alright, now we're sure that these review scores are collected from satisfaction surveys. And these surveys are not sent out only when the order is delivered or after the deadline is due. Last test: Are these review scores have the review_answer_timestamp values which are the same as review_creation_date values?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>order_status</th>\n",
       "        <th>suprise</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>approved</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>canceled</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>created</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>delivered</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>invoiced</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>processing</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>shipped</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>unavailable</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('approved', Decimal('0')),\n",
       " ('canceled', Decimal('0')),\n",
       " ('created', Decimal('0')),\n",
       " ('delivered', Decimal('0')),\n",
       " ('invoiced', Decimal('0')),\n",
       " ('processing', Decimal('0')),\n",
       " ('shipped', Decimal('0')),\n",
       " ('unavailable', Decimal('0'))]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT order_status,\n",
    "       SUM(IF(TIMESTAMPDIFF(\n",
    "           hour,review_creation_date,review_answer_timestamp)\n",
    "              = 0,1,0)) AS suprise\n",
    "FROM orders o JOIN order_reviews re\n",
    "ON o.order_id = re.order_id\n",
    "GROUP BY order_status\n",
    "ORDER BY order_status;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well, customers really completed these surveys, not some AI as I imagined. I really need more information to understand how customers review their orders though.<br>\n",
    "Let's move forward with what we have now."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Average review score\n",
    "Olist requires merchants to have an average review score at least 4 (on a scale of 0 to 5) to guarantee their performance.[(More)](https://get.olist.help/pt-BR/articles/413344-acordo-de-nivel-de-servico-entre-parceiro-olist-e-lojista)<br>\n",
    "Below query provides number of sellers and those who meet this requirement. This query based on all review scores of all orders."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>num_sellers</th>\n",
       "        <th>num_qualified_sellers</th>\n",
       "        <th>percentage</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3095</td>\n",
       "        <td>1948</td>\n",
       "        <td>62.94</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3095, 1948, Decimal('62.94'))]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT b.num_sellers,\n",
    "       COUNT(a.seller_id) AS num_qualified_sellers,\n",
    "       ROUND(COUNT(a.seller_id)/b.num_sellers*100,2)\n",
    "       AS percentage\n",
    "FROM (SELECT seller_id, AVG(review_score) AS avg_score\n",
    "      FROM order_reviews re JOIN order_items oi\n",
    "      ON re.order_id = oi.order_id\n",
    "      GROUP BY seller_id\n",
    "      HAVING avg_score >= 4) a\n",
    "CROSS JOIN \n",
    "     (SELECT COUNT(DISTINCT seller_id) AS num_sellers\n",
    "      FROM order_items) b;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "62.94% sellers have average review score of 4 or higher. Below is the top 10 of them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@localhost/olist\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>seller_id</th>\n",
       "        <th>avg_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>062c325cd6a2b87845fab56b4ec2eeae</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>296729ffb9b684050dd24836dac4494a</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1cd9e0cc1839d55516843def5600816d</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48b6c3f4c6a93171da04b75313f2130f</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48efc9d94a9834137efd9ea76b065a38</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fa5fdc4e4bb6bd1009ad0e4ac4096562</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>166e8f1381e09651983c38b1f6f91c11</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44717f64ec2a457979cf83c429077666</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>b5bb2b985208834bd5bd86c7a402bbad</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8ec76bb0965af3f007692b26fa9d6623</td>\n",
       "        <td>5.00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('062c325cd6a2b87845fab56b4ec2eeae', Decimal('5.00')),\n",
       " ('296729ffb9b684050dd24836dac4494a', Decimal('5.00')),\n",
       " ('1cd9e0cc1839d55516843def5600816d', Decimal('5.00')),\n",
       " ('48b6c3f4c6a93171da04b75313f2130f', Decimal('5.00')),\n",
       " ('48efc9d94a9834137efd9ea76b065a38', Decimal('5.00')),\n",
       " ('fa5fdc4e4bb6bd1009ad0e4ac4096562', Decimal('5.00')),\n",
       " ('166e8f1381e09651983c38b1f6f91c11', Decimal('5.00')),\n",
       " ('44717f64ec2a457979cf83c429077666', Decimal('5.00')),\n",
       " ('b5bb2b985208834bd5bd86c7a402bbad', Decimal('5.00')),\n",
       " ('8ec76bb0965af3f007692b26fa9d6623', Decimal('5.00'))]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT seller_id, ROUND(AVG(review_score),2) AS avg_score\n",
    "FROM order_reviews re JOIN order_items oi\n",
    "ON re.order_id = oi.order_id\n",
    "GROUP BY seller_id\n",
    "HAVING avg_score >= 4\n",
    "ORDER BY avg_score DESC\n",
    "LIMIT 10;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}