Exercise – SQL Part 2 🛢️

💡 Scenario:
You’re analyzing customer purchases at an e-commerce company. The dataset includes order transactions, and your manager wants the following insights using Level 2 SQL techniques:

1. Total spending per customer (Using WINDOW functions instead of GROUP BY)
2. Customers with more than 3 purchases (Using CTEs instead of HAVING)
3. Top 5 highest spending customers (Using Subqueries instead of LIMIT)

---

📌 Database Schema (orders)

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
);

📝 Sample Data for Testing:

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(1, 101, '2024-01-10', 150.00),
(2, 102, '2024-01-11', 200.00),
(3, 101, '2024-01-15', 120.00),
(4, 103, '2024-01-16', 250.00),
(5, 102, '2024-01-18', 300.00),
(6, 101, '2024-02-01', 180.00),
(7, 103, '2024-02-05', 100.00),
(8, 104, '2024-02-10', 500.00),
(9, 101, '2024-02-12', 220.00),
(10, 102, '2024-02-15', 50.00);

---

1️⃣ Total Spending Per Customer (Using Window Functions Instead of GROUP BY)

SELECT DISTINCT customer_id, 
       SUM(amount) OVER (PARTITION BY customer_id) AS total_spent
FROM orders;

🔹 Why?

• Instead of GROUP BY, we use WINDOW FUNCTIONS (SUM() OVER (PARTITION BY customer_id)).
• It allows more flexibility in combining with other aggregations in the same query.

---

2️⃣ Customers with More Than 3 Purchases (Using CTE Instead of HAVING)

WITH customer_orders AS (
    SELECT customer_id, COUNT(order_id) AS total_orders
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_orders
FROM customer_orders
WHERE total_orders > 3;

🔹 Why?

• CTE (Common Table Expression) makes the query more readable and modular.
• Instead of using HAVING COUNT() > 3, we store the grouped results in a CTE and filter using WHERE.

---

3️⃣ Top 5 Highest Spending Customers (Using Subqueries Instead of LIMIT)

SELECT customer_id, total_spent 
FROM (
    SELECT customer_id, SUM(amount) AS total_spent,
           RANK() OVER (ORDER BY SUM(amount) DESC) AS ranking
    FROM orders
    GROUP BY customer_id
) ranked_customers
WHERE ranking <= 5;

🔹 Why?

• RANK() OVER (ORDER BY SUM(amount) DESC) ranks customers by total spending.
• Instead of LIMIT 5, we use a subquery and filter using WHERE ranking <= 5, which allows for more flexibility in reporting.

---

📌 Expected Output Examples

1️⃣ Total Spending Per Customer (Window Function)

customer_id	total_spent
101	670.00
102	550.00
103	350.00
104	500.00

2️⃣ Customers with More Than 3 Purchases (Using CTE)

customer_id	total_orders
101	4

3️⃣ Top 5 Highest Spending Customers (Using Subqueries)

customer_id	total_spent
101	670.00
104	500.00
102	550.00
103	350.00

---

🎯 SQL Techniques Used in Level 2

✅ Window Functions (SUM() OVER()) – Instead of traditional GROUP BY
✅ CTE (Common Table Expressions) (WITH AS) – Instead of HAVING
✅ Subqueries (SELECT FROM (SELECT...)) – Instead of LIMIT

---

📌 Summary: Why This is a Level 2 Exercise?

• Moves away from basic SQL (GROUP BY, HAVING, LIMIT) and introduces advanced techniques.
• Focuses on performance optimization & scalability for real-world use cases.
• Uses modular SQL techniques (CTE, Window Functions, Subqueries) for better query management.

…………

Thank you for your time; sharing is caring! 🌍

…………