Trust Coding : Programming: E-Commerce SQL Subquery Exercises with Analysis

SQL Subquery Exercise: E-Commerce Database

Consider an e-commerce database with the following relations:

Customers(CustomerID, Name, City)
Orders(OrderID, CustomerID, OrderDate)
Products(ProductID, ProductName, Price)
OrderDetails(OrderID, ProductID, Quantity)

Using subqueries, write SQL queries to answer the following questions. Provide a brief analysis or justification for each result.

Tasks

Most Expensive Product Orders:
- Find the customer(s) who ordered the most expensive product.
- Explain why this information might be useful for marketing or promotions.
High-Value Customers:
- Find the names of customers who have placed orders with total amounts greater than the average order amount.
- Analyze what this indicates about customer purchasing behavior.
Regional Product Orders:
- List the products ordered by customers from Dhaka.
- Discuss what insights this could provide for regional sales analysis.
City with Maximum Customers:
- Identify the city that has the maximum number of customers.
- Explain how this information can help in strategic business decisions, such as logistics or targeted marketing.
Customers Who Ordered All Products:
- Find the customers who ordered all products listed in the Products table.
- Analyze what this implies about customer loyalty or engagement.

Instructions

Use subqueries wherever applicable.
Include a brief justification or analysis for each query result.
Present queries in a clear and structured format for readability.
Highlight how each query can help in business decision-making or provide customer insights.

E-Commerce SQL Subquery Exercises with Analysis

SQL Subquery Solutions: E-Commerce Database

Consider the e-commerce database with the following relations: Customers(CustomerID, Name, City), Orders(OrderID, CustomerID, OrderDate), Products(ProductID, ProductName, Price), OrderDetails(OrderID, ProductID, Quantity). Below are solutions using subqueries along with brief analyses.

1. Customers Who Ordered the Most Expensive Product

SELECT C.Name
FROM Customers C
WHERE C.CustomerID IN (
    SELECT O.CustomerID
    FROM Orders O
    JOIN OrderDetails OD ON O.OrderID = OD.OrderID
    WHERE OD.ProductID = (
        SELECT ProductID 
        FROM Products 
        WHERE Price = (SELECT MAX(Price) FROM Products)
    )
);

Analysis: Identifying customers who purchased the most expensive product helps target premium promotions and marketing campaigns.

2. Customers With Orders Above Average Total Amount

SELECT C.Name
FROM Customers C
WHERE C.CustomerID IN (
    SELECT O.CustomerID
    FROM Orders O
    WHERE (SELECT SUM(P.Price * OD.Quantity)
           FROM OrderDetails OD
           JOIN Products P ON OD.ProductID = P.ProductID
           WHERE OD.OrderID = O.OrderID) > 
          (SELECT AVG(TotalAmount) 
           FROM (
              SELECT SUM(P.Price * OD.Quantity) AS TotalAmount
              FROM Orders O2
              JOIN OrderDetails OD ON O2.OrderID = OD.OrderID
              JOIN Products P ON OD.ProductID = P.ProductID
              GROUP BY O2.OrderID
           ))
);

Analysis: Customers who spend above average are high-value customers, useful for loyalty programs and premium marketing.

3. Products Ordered by Customers from Dhaka

SELECT DISTINCT P.ProductName
FROM Products P
WHERE P.ProductID IN (
    SELECT OD.ProductID
    FROM OrderDetails OD
    JOIN Orders O ON OD.OrderID = O.OrderID
    JOIN Customers C ON O.CustomerID = C.CustomerID
    WHERE C.City = 'Dhaka'
);

Analysis: Regional product analysis helps optimize inventory, marketing campaigns, and promotions targeted to Dhaka customers.

4. City With Maximum Number of Customers

SELECT City
FROM Customers
WHERE (SELECT COUNT(*) FROM Customers C2 WHERE C2.City = Customers.City) = 
      (SELECT MAX(CityCount) 
       FROM (SELECT COUNT(*) AS CityCount FROM Customers GROUP BY City));

Analysis: Identifying the city with the most customers helps in strategic decisions like warehouse placement, regional marketing, and delivery logistics.

5. Customers Who Ordered All Products

SELECT C.Name
FROM Customers C
WHERE NOT EXISTS (
    SELECT P.ProductID
    FROM Products P
    WHERE NOT EXISTS (
        SELECT OD.ProductID
        FROM Orders O
        JOIN OrderDetails OD ON O.OrderID = OD.OrderID
        WHERE O.CustomerID = C.CustomerID
          AND OD.ProductID = P.ProductID
    )
);

Analysis: Customers who ordered all products demonstrate high engagement and loyalty, useful for VIP marketing campaigns and product feedback.

Trust Coding : Programming