Sample Data (E-Commerce System)
customers
| cust_id | name | city |
|---|---|---|
| 1 | Ravi | Delhi |
| 2 | Sita | Chennai |
| 3 | John | Mumbai |
orders
| order_id | cust_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2025-01-05 | 5000 |
| 102 | 1 | 2025-01-10 | 3000 |
| 103 | 2 | 2025-01-08 | 7000 |
| 104 | 3 | 2025-01-12 | 2000 |
Case Study 1: Business wants total sales per customer
SELECT c.name, SUM(o.amount) AS total_sales
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.name;
Ravi → 5000 + 3000 = 8000
Sita → 7000
John → 2000
This shows how much each customer spent.
Sita → 7000
John → 2000
This shows how much each customer spent.
Case Study 2: Find the top spending customer
SELECT c.name, SUM(o.amount) AS total_sales
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.name
ORDER BY total_sales DESC
LIMIT 1;
Ravi spent 8000 which is highest.
So Ravi is the top customer.
So Ravi is the top customer.
Case Study 3: Find customers who placed more than 1 order
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.name
HAVING COUNT(o.order_id) > 1;
Ravi placed 2 orders.
Others placed only 1.
So only Ravi is returned.
Others placed only 1.
So only Ravi is returned.
Case Study 4: Find customers who have not placed any order
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.order_id IS NULL;
LEFT JOIN keeps all customers.
If order is NULL → no purchase.
Current data returns no rows.
If order is NULL → no purchase.
Current data returns no rows.
Case Study 5: Monthly sales report
SELECT MONTH(order_date) AS month, SUM(amount) AS total_sales
FROM orders
GROUP BY MONTH(order_date);
All orders are in January (month 1).
Total sales = 5000+3000+7000+2000 = 17000.
Total sales = 5000+3000+7000+2000 = 17000.
Case Study 6: Find average order value
SELECT AVG(amount) AS avg_order_value FROM orders;
(5000 + 3000 + 7000 + 2000) / 4 = 4250.
Average order value = 4250.
Average order value = 4250.
Case Study 7: Find customers who spent more than average
SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.name
HAVING SUM(o.amount) > (SELECT AVG(amount) FROM orders);
Average order = 4250.
Ravi spent 8000 & Sita spent 7000 → greater than average.
So Ravi and Sita are returned.
Ravi spent 8000 & Sita spent 7000 → greater than average.
So Ravi and Sita are returned.
Case Study 8: Find latest order of each customer
SELECT cust_id, MAX(order_date) AS latest_order
FROM orders
GROUP BY cust_id;
Ravi → 2025-01-10
Sita → 2025-01-08
John → 2025-01-12
Sita → 2025-01-08
John → 2025-01-12
Case Study 9: Rank customers by total spending
SELECT c.name, SUM(o.amount) AS total_spent,
RANK() OVER(ORDER BY SUM(o.amount) DESC) AS rank_no
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.name;
Ravi → Rank 1 (8000)
Sita → Rank 2 (7000)
John → Rank 3 (2000)
Sita → Rank 2 (7000)
John → Rank 3 (2000)
Case Study 10: Business wants to delete old orders before 2025
DELETE FROM orders WHERE order_date < '2025-01-01';
Removes old data for performance & archiving.
Only recent orders are kept.
Only recent orders are kept.
Source: sureshtechlabs.com