Customer and Order Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "93790a90-8d56-4589-974b-d92e34216073",
   "metadata": {},
   "source": [
    "### SQL Task Questions\n",
    "\n",
    "1. **Customer Overview**\n",
    "   - What is the total number of customers in the database?\n",
    "   - List all customers who registered in the last month.\n",
    "\n",
    "2. **Order Analysis**\n",
    "   - What is the total amount spent by each customer?\n",
    "   - Count the number of orders placed by each customer.\n",
    "   - Find the average order amount for all orders.\n",
    "\n",
    "3. **Order Status**\n",
    "   - What is the distribution of order statuses (e.g., Shipped, Delivered, Cancelled, Pending)?\n",
    "   - How many orders are currently in the 'Pending' status?\n",
    "\n",
    "4. **Customer Engagement**\n",
    "   - Identify customers who have not placed any orders.\n",
    "   - Find the customer with the highest total spending and provide their details.\n",
    "\n",
    "5. **Date Analysis**\n",
    "   - Retrieve all orders placed in the last week.\n",
    "   - Determine the number of orders placed each month.\n",
    "\n",
    "6. **Customer Segmentation**\n",
    "   - Segment customers into high spenders (total spent over $200), medium spenders ($100 - $200), and low spenders (under $100).\n",
    "\n",
    "These questions will help you analyze and derive insights from the data in the **Customer** and **Orders** tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "80e1868c-4071-4047-bdbd-b69ee492cb49",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b9bb1731-4f49-409a-b2ab-2327e6d4b932",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: root@sales'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql mysql+pymysql://root:password@localhost:3306/sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "036cd5a5-5ed6-4496-b9ec-dff966f52cd8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Tables_in_sales</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>customer</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>orders</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('customer',), ('orders',)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SHOW TABLES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c351a7a9-86dd-445b-b7f8-ff62a2eee263",
   "metadata": {},
   "source": [
    "Customer Overview\n",
    "\n",
    "What is the total number of customers in the database?\n",
    "List all customers who registered in the last month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "510a0b93-c87d-4676-8dd4-1d7752cc9d5a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_customers</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>100</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(100,)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    COUNT(*) total_customers \n",
    "FROM customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "ceee6d16-0727-49a6-9035-fa85d3e9ed84",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>customer_id</th>\n",
       "            <th>first_name</th>\n",
       "            <th>last_name</th>\n",
       "            <th>email</th>\n",
       "            <th>phone</th>\n",
       "            <th>registration_date</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM Customer\n",
    "WHERE registration_date >= NOW() - INTERVAL 1 MONTH;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "d17cdbdb-da53-4725-b0d5-0726fb0f24e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>customer_id</th>\n",
       "            <th>first_name</th>\n",
       "            <th>last_name</th>\n",
       "            <th>email</th>\n",
       "            <th>phone</th>\n",
       "            <th>registration_date</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>CUST001</td>\n",
       "            <td>John</td>\n",
       "            <td>Doe</td>\n",
       "            <td>john.doe@example.com</td>\n",
       "            <td>123-456-7890</td>\n",
       "            <td>2023-01-01 10:00:00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CUST001', 'John', 'Doe', 'john.doe@example.com', '123-456-7890', datetime.datetime(2023, 1, 1, 10, 0))]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM customer LIMIT 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "4173d564-9600-4325-9e45-dafff7487590",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "100 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>first_name</th>\n",
       "            <th>last_name</th>\n",
       "            <th>total_amount</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Kyle</td>\n",
       "            <td>Russell</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Laura</td>\n",
       "            <td>Cole</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jasmine</td>\n",
       "            <td>Cunningham</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Yasmin</td>\n",
       "            <td>Vasquez</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Wendy</td>\n",
       "            <td>Harrison</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Frank</td>\n",
       "            <td>Wilson</td>\n",
       "            <td>450.60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Zane</td>\n",
       "            <td>Lee</td>\n",
       "            <td>450.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Delilah</td>\n",
       "            <td>Harrison</td>\n",
       "            <td>430.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gabriel</td>\n",
       "            <td>Hodge</td>\n",
       "            <td>430.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oscar</td>\n",
       "            <td>Morris</td>\n",
       "            <td>430.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ursula</td>\n",
       "            <td>Mendez</td>\n",
       "            <td>415.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sophia</td>\n",
       "            <td>Adams</td>\n",
       "            <td>410.90</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hannah</td>\n",
       "            <td>Sanchez</td>\n",
       "            <td>410.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mia</td>\n",
       "            <td>Young</td>\n",
       "            <td>410.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nora</td>\n",
       "            <td>Cooper</td>\n",
       "            <td>400.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Aaron</td>\n",
       "            <td>Hawkins</td>\n",
       "            <td>375.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>George</td>\n",
       "            <td>Carter</td>\n",
       "            <td>370.60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kylie</td>\n",
       "            <td>Hunt</td>\n",
       "            <td>370.60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ivy</td>\n",
       "            <td>Clark</td>\n",
       "            <td>340.40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Liam</td>\n",
       "            <td>Morgan</td>\n",
       "            <td>340.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Elijah</td>\n",
       "            <td>Burns</td>\n",
       "            <td>330.30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quincy</td>\n",
       "            <td>Stevens</td>\n",
       "            <td>330.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hazel</td>\n",
       "            <td>Watson</td>\n",
       "            <td>320.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kevin</td>\n",
       "            <td>Woods</td>\n",
       "            <td>320.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Riley</td>\n",
       "            <td>Green</td>\n",
       "            <td>320.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Olivia</td>\n",
       "            <td>King</td>\n",
       "            <td>310.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Noah</td>\n",
       "            <td>Ferguson</td>\n",
       "            <td>300.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Victor</td>\n",
       "            <td>Bishop</td>\n",
       "            <td>300.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Willa</td>\n",
       "            <td>Hernandez</td>\n",
       "            <td>300.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Alice</td>\n",
       "            <td>Johnson</td>\n",
       "            <td>300.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Brooke</td>\n",
       "            <td>Cruz</td>\n",
       "            <td>300.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ryan</td>\n",
       "            <td>Bishop</td>\n",
       "            <td>300.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Penny</td>\n",
       "            <td>Powell</td>\n",
       "            <td>300.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tina</td>\n",
       "            <td>Freeman</td>\n",
       "            <td>300.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Xander</td>\n",
       "            <td>Hayes</td>\n",
       "            <td>290.40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bella</td>\n",
       "            <td>Kim</td>\n",
       "            <td>280.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sam</td>\n",
       "            <td>Phillips</td>\n",
       "            <td>280.30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jasmine</td>\n",
       "            <td>Ross</td>\n",
       "            <td>280.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Noah</td>\n",
       "            <td>Allen</td>\n",
       "            <td>275.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Emmett</td>\n",
       "            <td>Kelley</td>\n",
       "            <td>270.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rita</td>\n",
       "            <td>Bryant</td>\n",
       "            <td>260.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mark</td>\n",
       "            <td>Haynes</td>\n",
       "            <td>260.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Parker</td>\n",
       "            <td>Luna</td>\n",
       "            <td>260.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gavin</td>\n",
       "            <td>Higgins</td>\n",
       "            <td>250.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eve</td>\n",
       "            <td>White</td>\n",
       "            <td>250.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Zach</td>\n",
       "            <td>Fowler</td>\n",
       "            <td>250.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Felicity</td>\n",
       "            <td>Caldwell</td>\n",
       "            <td>250.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Diana</td>\n",
       "            <td>Roberts</td>\n",
       "            <td>230.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Isaac</td>\n",
       "            <td>Murray</td>\n",
       "            <td>225.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quinn</td>\n",
       "            <td>Hayden</td>\n",
       "            <td>225.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tyler</td>\n",
       "            <td>Baker</td>\n",
       "            <td>220.30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Zoe</td>\n",
       "            <td>Floyd</td>\n",
       "            <td>210.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jane</td>\n",
       "            <td>Smith</td>\n",
       "            <td>200.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Xena</td>\n",
       "            <td>Burns</td>\n",
       "            <td>200.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oscar</td>\n",
       "            <td>Perry</td>\n",
       "            <td>200.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim</td>\n",
       "            <td>Hall</td>\n",
       "            <td>200.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Isaac</td>\n",
       "            <td>Garcia</td>\n",
       "            <td>190.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hank</td>\n",
       "            <td>Martin</td>\n",
       "            <td>175.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Will</td>\n",
       "            <td>Richardson</td>\n",
       "            <td>160.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Olivia</td>\n",
       "            <td>Hicks</td>\n",
       "            <td>150.90</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mia</td>\n",
       "            <td>Cook</td>\n",
       "            <td>150.90</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cody</td>\n",
       "            <td>Wood</td>\n",
       "            <td>150.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cameron</td>\n",
       "            <td>Wells</td>\n",
       "            <td>150.40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Liam</td>\n",
       "            <td>Gilbert</td>\n",
       "            <td>150.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>John</td>\n",
       "            <td>Doe</td>\n",
       "            <td>150.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hailey</td>\n",
       "            <td>Rogers</td>\n",
       "            <td>145.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eli</td>\n",
       "            <td>James</td>\n",
       "            <td>140.30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tina</td>\n",
       "            <td>Cruz</td>\n",
       "            <td>140.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Julia</td>\n",
       "            <td>Khan</td>\n",
       "            <td>140.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Liam</td>\n",
       "            <td>Walker</td>\n",
       "            <td>135.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Yara</td>\n",
       "            <td>Patel</td>\n",
       "            <td>130.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Thomas</td>\n",
       "            <td>Wright</td>\n",
       "            <td>125.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rachel</td>\n",
       "            <td>Turner</td>\n",
       "            <td>120.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quinn</td>\n",
       "            <td>Scott</td>\n",
       "            <td>120.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bob</td>\n",
       "            <td>Brown</td>\n",
       "            <td>120.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sam</td>\n",
       "            <td>Freeman</td>\n",
       "            <td>120.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nina</td>\n",
       "            <td>Price</td>\n",
       "            <td>110.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Yvonne</td>\n",
       "            <td>Gordon</td>\n",
       "            <td>110.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Carter</td>\n",
       "            <td>Mitchell</td>\n",
       "            <td>100.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Pam</td>\n",
       "            <td>Parker</td>\n",
       "            <td>90.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Grace</td>\n",
       "            <td>Lee</td>\n",
       "            <td>90.99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Samantha</td>\n",
       "            <td>Harmon</td>\n",
       "            <td>90.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Fiona</td>\n",
       "            <td>Nelson</td>\n",
       "            <td>90.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quinn</td>\n",
       "            <td>Hughes</td>\n",
       "            <td>90.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Aiden</td>\n",
       "            <td>Richardson</td>\n",
       "            <td>90.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Danielle</td>\n",
       "            <td>Snyder</td>\n",
       "            <td>90.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Peter</td>\n",
       "            <td>Wright</td>\n",
       "            <td>89.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Victor</td>\n",
       "            <td>Griffin</td>\n",
       "            <td>85.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Brooke</td>\n",
       "            <td>Bennett</td>\n",
       "            <td>85.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Xander</td>\n",
       "            <td>Harrison</td>\n",
       "            <td>80.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Fiona</td>\n",
       "            <td>Higgins</td>\n",
       "            <td>80.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Maya</td>\n",
       "            <td>Lloyd</td>\n",
       "            <td>80.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Charlie</td>\n",
       "            <td>Davis</td>\n",
       "            <td>80.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ursula</td>\n",
       "            <td>Freeman</td>\n",
       "            <td>80.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ian</td>\n",
       "            <td>Ramirez</td>\n",
       "            <td>75.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Uma</td>\n",
       "            <td>Nash</td>\n",
       "            <td>70.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Alice</td>\n",
       "            <td>Nguyen</td>\n",
       "            <td>60.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jack</td>\n",
       "            <td>Lewis</td>\n",
       "            <td>60.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Victor</td>\n",
       "            <td>Graham</td>\n",
       "            <td>60.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ursula</td>\n",
       "            <td>Reyes</td>\n",
       "            <td>50.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Kyle', 'Russell', Decimal('500.00')),\n",
       " ('Laura', 'Cole', Decimal('500.00')),\n",
       " ('Jasmine', 'Cunningham', Decimal('500.00')),\n",
       " ('Yasmin', 'Vasquez', Decimal('500.00')),\n",
       " ('Wendy', 'Harrison', Decimal('500.00')),\n",
       " ('Frank', 'Wilson', Decimal('450.60')),\n",
       " ('Zane', 'Lee', Decimal('450.00')),\n",
       " ('Delilah', 'Harrison', Decimal('430.00')),\n",
       " ('Gabriel', 'Hodge', Decimal('430.00')),\n",
       " ('Oscar', 'Morris', Decimal('430.00')),\n",
       " ('Ursula', 'Mendez', Decimal('415.25')),\n",
       " ('Sophia', 'Adams', Decimal('410.90')),\n",
       " ('Hannah', 'Sanchez', Decimal('410.75')),\n",
       " ('Mia', 'Young', Decimal('410.00')),\n",
       " ('Nora', 'Cooper', Decimal('400.00')),\n",
       " ('Aaron', 'Hawkins', Decimal('375.00')),\n",
       " ('George', 'Carter', Decimal('370.60')),\n",
       " ('Kylie', 'Hunt', Decimal('370.60')),\n",
       " ('Ivy', 'Clark', Decimal('340.40')),\n",
       " ('Liam', 'Morgan', Decimal('340.00')),\n",
       " ('Elijah', 'Burns', Decimal('330.30')),\n",
       " ('Quincy', 'Stevens', Decimal('330.20')),\n",
       " ('Hazel', 'Watson', Decimal('320.50')),\n",
       " ('Kevin', 'Woods', Decimal('320.50')),\n",
       " ('Riley', 'Green', Decimal('320.00')),\n",
       " ('Olivia', 'King', Decimal('310.25')),\n",
       " ('Noah', 'Ferguson', Decimal('300.75')),\n",
       " ('Victor', 'Bishop', Decimal('300.25')),\n",
       " ('Willa', 'Hernandez', Decimal('300.00')),\n",
       " ('Alice', 'Johnson', Decimal('300.00')),\n",
       " ('Brooke', 'Cruz', Decimal('300.00')),\n",
       " ('Ryan', 'Bishop', Decimal('300.00')),\n",
       " ('Penny', 'Powell', Decimal('300.00')),\n",
       " ('Tina', 'Freeman', Decimal('300.00')),\n",
       " ('Xander', 'Hayes', Decimal('290.40')),\n",
       " ('Bella', 'Kim', Decimal('280.50')),\n",
       " ('Sam', 'Phillips', Decimal('280.30')),\n",
       " ('Jasmine', 'Ross', Decimal('280.25')),\n",
       " ('Noah', 'Allen', Decimal('275.50')),\n",
       " ('Emmett', 'Kelley', Decimal('270.00')),\n",
       " ('Rita', 'Bryant', Decimal('260.50')),\n",
       " ('Mark', 'Haynes', Decimal('260.00')),\n",
       " ('Parker', 'Luna', Decimal('260.00')),\n",
       " ('Gavin', 'Higgins', Decimal('250.75')),\n",
       " ('Eve', 'White', Decimal('250.75')),\n",
       " ('Zach', 'Fowler', Decimal('250.75')),\n",
       " ('Felicity', 'Caldwell', Decimal('250.00')),\n",
       " ('Diana', 'Roberts', Decimal('230.00')),\n",
       " ('Isaac', 'Murray', Decimal('225.00')),\n",
       " ('Quinn', 'Hayden', Decimal('225.00')),\n",
       " ('Tyler', 'Baker', Decimal('220.30')),\n",
       " ('Zoe', 'Floyd', Decimal('210.50')),\n",
       " ('Jane', 'Smith', Decimal('200.50')),\n",
       " ('Xena', 'Burns', Decimal('200.00')),\n",
       " ('Oscar', 'Perry', Decimal('200.00')),\n",
       " ('Kim', 'Hall', Decimal('200.00')),\n",
       " ('Isaac', 'Garcia', Decimal('190.00')),\n",
       " ('Hank', 'Martin', Decimal('175.00')),\n",
       " ('Will', 'Richardson', Decimal('160.75')),\n",
       " ('Olivia', 'Hicks', Decimal('150.90')),\n",
       " ('Mia', 'Cook', Decimal('150.90')),\n",
       " ('Cody', 'Wood', Decimal('150.50')),\n",
       " ('Cameron', 'Wells', Decimal('150.40')),\n",
       " ('Liam', 'Gilbert', Decimal('150.25')),\n",
       " ('John', 'Doe', Decimal('150.00')),\n",
       " ('Hailey', 'Rogers', Decimal('145.75')),\n",
       " ('Eli', 'James', Decimal('140.30')),\n",
       " ('Tina', 'Cruz', Decimal('140.00')),\n",
       " ('Julia', 'Khan', Decimal('140.00')),\n",
       " ('Liam', 'Walker', Decimal('135.99')),\n",
       " ('Yara', 'Patel', Decimal('130.00')),\n",
       " ('Thomas', 'Wright', Decimal('125.00')),\n",
       " ('Rachel', 'Turner', Decimal('120.50')),\n",
       " ('Quinn', 'Scott', Decimal('120.50')),\n",
       " ('Bob', 'Brown', Decimal('120.25')),\n",
       " ('Sam', 'Freeman', Decimal('120.00')),\n",
       " ('Nina', 'Price', Decimal('110.75')),\n",
       " ('Yvonne', 'Gordon', Decimal('110.50')),\n",
       " ('Carter', 'Mitchell', Decimal('100.99')),\n",
       " ('Pam', 'Parker', Decimal('90.99')),\n",
       " ('Grace', 'Lee', Decimal('90.99')),\n",
       " ('Samantha', 'Harmon', Decimal('90.50')),\n",
       " ('Fiona', 'Nelson', Decimal('90.25')),\n",
       " ('Quinn', 'Hughes', Decimal('90.25')),\n",
       " ('Aiden', 'Richardson', Decimal('90.00')),\n",
       " ('Danielle', 'Snyder', Decimal('90.00')),\n",
       " ('Peter', 'Wright', Decimal('89.00')),\n",
       " ('Victor', 'Griffin', Decimal('85.25')),\n",
       " ('Brooke', 'Bennett', Decimal('85.20')),\n",
       " ('Xander', 'Harrison', Decimal('80.75')),\n",
       " ('Fiona', 'Higgins', Decimal('80.25')),\n",
       " ('Maya', 'Lloyd', Decimal('80.00')),\n",
       " ('Charlie', 'Davis', Decimal('80.00')),\n",
       " ('Ursula', 'Freeman', Decimal('80.00')),\n",
       " ('Ian', 'Ramirez', Decimal('75.00')),\n",
       " ('Uma', 'Nash', Decimal('70.75')),\n",
       " ('Alice', 'Nguyen', Decimal('60.00')),\n",
       " ('Jack', 'Lewis', Decimal('60.00')),\n",
       " ('Victor', 'Graham', Decimal('60.00')),\n",
       " ('Ursula', 'Reyes', Decimal('50.00'))]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    first_name, last_name, sum(total_amount) total_amount\n",
    "FROM customer\n",
    "JOIN orders ON orders.customer_id = customer.customer_id\n",
    "GROUP BY first_name, last_name\n",
    "ORDER BY total_amount DESC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "758425b8-82cd-4e70-9ca5-7499562703b2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "100 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>first_name</th>\n",
       "            <th>last_name</th>\n",
       "            <th>number_orders</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>John</td>\n",
       "            <td>Doe</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jane</td>\n",
       "            <td>Smith</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Alice</td>\n",
       "            <td>Johnson</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bob</td>\n",
       "            <td>Brown</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Charlie</td>\n",
       "            <td>Davis</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eve</td>\n",
       "            <td>White</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Frank</td>\n",
       "            <td>Wilson</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Grace</td>\n",
       "            <td>Lee</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hank</td>\n",
       "            <td>Martin</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ivy</td>\n",
       "            <td>Clark</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jack</td>\n",
       "            <td>Lewis</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim</td>\n",
       "            <td>Hall</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Liam</td>\n",
       "            <td>Walker</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mia</td>\n",
       "            <td>Young</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Noah</td>\n",
       "            <td>Allen</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Olivia</td>\n",
       "            <td>King</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Peter</td>\n",
       "            <td>Wright</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quinn</td>\n",
       "            <td>Scott</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Riley</td>\n",
       "            <td>Green</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sophia</td>\n",
       "            <td>Adams</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tyler</td>\n",
       "            <td>Baker</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Uma</td>\n",
       "            <td>Nash</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Victor</td>\n",
       "            <td>Griffin</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Wendy</td>\n",
       "            <td>Harrison</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Xander</td>\n",
       "            <td>Hayes</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Yara</td>\n",
       "            <td>Patel</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Zane</td>\n",
       "            <td>Lee</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Alice</td>\n",
       "            <td>Nguyen</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Bella</td>\n",
       "            <td>Kim</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Carter</td>\n",
       "            <td>Mitchell</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Diana</td>\n",
       "            <td>Roberts</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eli</td>\n",
       "            <td>James</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Fiona</td>\n",
       "            <td>Nelson</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>George</td>\n",
       "            <td>Carter</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hannah</td>\n",
       "            <td>Sanchez</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ian</td>\n",
       "            <td>Ramirez</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Julia</td>\n",
       "            <td>Khan</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kevin</td>\n",
       "            <td>Woods</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Laura</td>\n",
       "            <td>Cole</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mark</td>\n",
       "            <td>Haynes</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nina</td>\n",
       "            <td>Price</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oscar</td>\n",
       "            <td>Morris</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Pam</td>\n",
       "            <td>Parker</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quincy</td>\n",
       "            <td>Stevens</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rachel</td>\n",
       "            <td>Turner</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sam</td>\n",
       "            <td>Phillips</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tina</td>\n",
       "            <td>Cruz</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ursula</td>\n",
       "            <td>Reyes</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Victor</td>\n",
       "            <td>Bishop</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Will</td>\n",
       "            <td>Richardson</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Xena</td>\n",
       "            <td>Burns</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Yvonne</td>\n",
       "            <td>Gordon</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Zach</td>\n",
       "            <td>Fowler</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Aaron</td>\n",
       "            <td>Hawkins</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Brooke</td>\n",
       "            <td>Bennett</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cameron</td>\n",
       "            <td>Wells</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Danielle</td>\n",
       "            <td>Snyder</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Elijah</td>\n",
       "            <td>Burns</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Felicity</td>\n",
       "            <td>Caldwell</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gabriel</td>\n",
       "            <td>Hodge</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hailey</td>\n",
       "            <td>Rogers</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Isaac</td>\n",
       "            <td>Murray</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jasmine</td>\n",
       "            <td>Cunningham</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kylie</td>\n",
       "            <td>Hunt</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Liam</td>\n",
       "            <td>Gilbert</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Maya</td>\n",
       "            <td>Lloyd</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Noah</td>\n",
       "            <td>Ferguson</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Olivia</td>\n",
       "            <td>Hicks</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Parker</td>\n",
       "            <td>Luna</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quinn</td>\n",
       "            <td>Hayden</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ryan</td>\n",
       "            <td>Bishop</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Samantha</td>\n",
       "            <td>Harmon</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Thomas</td>\n",
       "            <td>Wright</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ursula</td>\n",
       "            <td>Mendez</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Victor</td>\n",
       "            <td>Graham</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Willa</td>\n",
       "            <td>Hernandez</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Xander</td>\n",
       "            <td>Harrison</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Yasmin</td>\n",
       "            <td>Vasquez</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Zoe</td>\n",
       "            <td>Floyd</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Aiden</td>\n",
       "            <td>Richardson</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Brooke</td>\n",
       "            <td>Cruz</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cody</td>\n",
       "            <td>Wood</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Delilah</td>\n",
       "            <td>Harrison</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Emmett</td>\n",
       "            <td>Kelley</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Fiona</td>\n",
       "            <td>Higgins</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gavin</td>\n",
       "            <td>Higgins</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Hazel</td>\n",
       "            <td>Watson</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Isaac</td>\n",
       "            <td>Garcia</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jasmine</td>\n",
       "            <td>Ross</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kyle</td>\n",
       "            <td>Russell</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Liam</td>\n",
       "            <td>Morgan</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mia</td>\n",
       "            <td>Cook</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nora</td>\n",
       "            <td>Cooper</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oscar</td>\n",
       "            <td>Perry</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Penny</td>\n",
       "            <td>Powell</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Quinn</td>\n",
       "            <td>Hughes</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rita</td>\n",
       "            <td>Bryant</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sam</td>\n",
       "            <td>Freeman</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tina</td>\n",
       "            <td>Freeman</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ursula</td>\n",
       "            <td>Freeman</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('John', 'Doe', 1),\n",
       " ('Jane', 'Smith', 1),\n",
       " ('Alice', 'Johnson', 1),\n",
       " ('Bob', 'Brown', 1),\n",
       " ('Charlie', 'Davis', 1),\n",
       " ('Eve', 'White', 1),\n",
       " ('Frank', 'Wilson', 1),\n",
       " ('Grace', 'Lee', 1),\n",
       " ('Hank', 'Martin', 1),\n",
       " ('Ivy', 'Clark', 1),\n",
       " ('Jack', 'Lewis', 1),\n",
       " ('Kim', 'Hall', 1),\n",
       " ('Liam', 'Walker', 1),\n",
       " ('Mia', 'Young', 1),\n",
       " ('Noah', 'Allen', 1),\n",
       " ('Olivia', 'King', 1),\n",
       " ('Peter', 'Wright', 1),\n",
       " ('Quinn', 'Scott', 1),\n",
       " ('Riley', 'Green', 1),\n",
       " ('Sophia', 'Adams', 1),\n",
       " ('Tyler', 'Baker', 1),\n",
       " ('Uma', 'Nash', 1),\n",
       " ('Victor', 'Griffin', 1),\n",
       " ('Wendy', 'Harrison', 1),\n",
       " ('Xander', 'Hayes', 1),\n",
       " ('Yara', 'Patel', 1),\n",
       " ('Zane', 'Lee', 1),\n",
       " ('Alice', 'Nguyen', 1),\n",
       " ('Bella', 'Kim', 1),\n",
       " ('Carter', 'Mitchell', 1),\n",
       " ('Diana', 'Roberts', 1),\n",
       " ('Eli', 'James', 1),\n",
       " ('Fiona', 'Nelson', 1),\n",
       " ('George', 'Carter', 1),\n",
       " ('Hannah', 'Sanchez', 1),\n",
       " ('Ian', 'Ramirez', 1),\n",
       " ('Julia', 'Khan', 1),\n",
       " ('Kevin', 'Woods', 1),\n",
       " ('Laura', 'Cole', 1),\n",
       " ('Mark', 'Haynes', 1),\n",
       " ('Nina', 'Price', 1),\n",
       " ('Oscar', 'Morris', 1),\n",
       " ('Pam', 'Parker', 1),\n",
       " ('Quincy', 'Stevens', 1),\n",
       " ('Rachel', 'Turner', 1),\n",
       " ('Sam', 'Phillips', 1),\n",
       " ('Tina', 'Cruz', 1),\n",
       " ('Ursula', 'Reyes', 1),\n",
       " ('Victor', 'Bishop', 1),\n",
       " ('Will', 'Richardson', 1),\n",
       " ('Xena', 'Burns', 1),\n",
       " ('Yvonne', 'Gordon', 1),\n",
       " ('Zach', 'Fowler', 1),\n",
       " ('Aaron', 'Hawkins', 1),\n",
       " ('Brooke', 'Bennett', 1),\n",
       " ('Cameron', 'Wells', 1),\n",
       " ('Danielle', 'Snyder', 1),\n",
       " ('Elijah', 'Burns', 1),\n",
       " ('Felicity', 'Caldwell', 1),\n",
       " ('Gabriel', 'Hodge', 1),\n",
       " ('Hailey', 'Rogers', 1),\n",
       " ('Isaac', 'Murray', 1),\n",
       " ('Jasmine', 'Cunningham', 1),\n",
       " ('Kylie', 'Hunt', 1),\n",
       " ('Liam', 'Gilbert', 1),\n",
       " ('Maya', 'Lloyd', 1),\n",
       " ('Noah', 'Ferguson', 1),\n",
       " ('Olivia', 'Hicks', 1),\n",
       " ('Parker', 'Luna', 1),\n",
       " ('Quinn', 'Hayden', 1),\n",
       " ('Ryan', 'Bishop', 1),\n",
       " ('Samantha', 'Harmon', 1),\n",
       " ('Thomas', 'Wright', 1),\n",
       " ('Ursula', 'Mendez', 1),\n",
       " ('Victor', 'Graham', 1),\n",
       " ('Willa', 'Hernandez', 1),\n",
       " ('Xander', 'Harrison', 1),\n",
       " ('Yasmin', 'Vasquez', 1),\n",
       " ('Zoe', 'Floyd', 1),\n",
       " ('Aiden', 'Richardson', 1),\n",
       " ('Brooke', 'Cruz', 1),\n",
       " ('Cody', 'Wood', 1),\n",
       " ('Delilah', 'Harrison', 1),\n",
       " ('Emmett', 'Kelley', 1),\n",
       " ('Fiona', 'Higgins', 1),\n",
       " ('Gavin', 'Higgins', 1),\n",
       " ('Hazel', 'Watson', 1),\n",
       " ('Isaac', 'Garcia', 1),\n",
       " ('Jasmine', 'Ross', 1),\n",
       " ('Kyle', 'Russell', 1),\n",
       " ('Liam', 'Morgan', 1),\n",
       " ('Mia', 'Cook', 1),\n",
       " ('Nora', 'Cooper', 1),\n",
       " ('Oscar', 'Perry', 1),\n",
       " ('Penny', 'Powell', 1),\n",
       " ('Quinn', 'Hughes', 1),\n",
       " ('Rita', 'Bryant', 1),\n",
       " ('Sam', 'Freeman', 1),\n",
       " ('Tina', 'Freeman', 1),\n",
       " ('Ursula', 'Freeman', 1)]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    first_name, last_name, COUNT(*) number_orders\n",
    "FROM customer\n",
    "JOIN orders ON orders.customer_id = customer.customer_id\n",
    "GROUP BY first_name, last_name\n",
    "ORDER BY number_orders DESC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "96c77381-5645-4d4d-9cd7-99c01bdf0f03",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>average_amount</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>230.962600</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('230.962600'),)]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(total_amount) average_amount FROM orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "3fb711ac-860e-4499-8a95-9eae51e0dd80",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>status</th>\n",
       "            <th>number_of_orders</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Shipped</td>\n",
       "            <td>33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Pending</td>\n",
       "            <td>18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Delivered</td>\n",
       "            <td>32</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Cancelled</td>\n",
       "            <td>17</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shipped', 33), ('Pending', 18), ('Delivered', 32), ('Cancelled', 17)]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    status, COUNT(*) number_of_orders\n",
    "FROM orders\n",
    "GROUP BY status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "e0fd0c3b-fe56-4202-8148-4f469ea5e194",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>first_name</th>\n",
       "            <th>last_name</th>\n",
       "            <th>number_orders</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    first_name, last_name, COUNT(*) number_orders\n",
    "FROM customer\n",
    "LEFT JOIN orders ON orders.customer_id = customer.customer_id\n",
    "GROUP BY first_name, last_name\n",
    "HAVING number_orders = 0\n",
    "ORDER BY number_orders DESC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "ec8f1292-08c1-40cb-be2d-83d7acc6fbc9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>first_name</th>\n",
       "            <th>last_name</th>\n",
       "            <th>total_amount</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Kyle</td>\n",
       "            <td>Russell</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Laura</td>\n",
       "            <td>Cole</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jasmine</td>\n",
       "            <td>Cunningham</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Yasmin</td>\n",
       "            <td>Vasquez</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Wendy</td>\n",
       "            <td>Harrison</td>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Kyle', 'Russell', Decimal('500.00')),\n",
       " ('Laura', 'Cole', Decimal('500.00')),\n",
       " ('Jasmine', 'Cunningham', Decimal('500.00')),\n",
       " ('Yasmin', 'Vasquez', Decimal('500.00')),\n",
       " ('Wendy', 'Harrison', Decimal('500.00'))]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH total_spendings AS(\n",
    "SELECT \n",
    "    first_name, last_name, sum(total_amount) total_amount\n",
    "FROM customer\n",
    "JOIN orders ON orders.customer_id = customer.customer_id\n",
    "GROUP BY first_name, last_name\n",
    "ORDER BY total_amount DESC\n",
    ")\n",
    "SELECT * FROM total_spendings ts\n",
    "WHERE total_amount = (SELECT MAX(total_amount) FROM total_spendings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "bf3b9c93-1108-44af-91e3-70867f87a0e2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/sales\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_amount</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>500.00</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('500.00'),)]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MAX(total_amount) total_amount FROM(\n",
    "        SELECT \n",
    "            first_name, last_name, sum(total_amount) total_amount\n",
    "        FROM customer\n",
    "        JOIN orders ON orders.customer_id = customer.customer_id\n",
    "        GROUP BY first_name, last_name\n",
    "        ORDER BY total_amount DESC\n",
    "        ) max_total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee92ccec-180f-453d-87e9-294606d9a847",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}