Introduction

This SQL project involves analyzing the sales and customer data of H+ Sport, a fictitious company that sells nutritional products and active lifestyle clothing. The focus is on the mineral water department, with the goal of improving sales and maintaining data accuracy. The project showcases a variety of SQL queries designed to manage, analyze, and interpret data, making it a valuable addition to a data science portfolio.

Company Background

H+ Sport is dedicated to creating eco-friendly, high-quality, nutrient-rich nutritional products that enhance active lifestyles. The company also offers functional, stylish, comfortable, and durable activewear. H+ Sport was founded in 2006 by Henry Tool and sells products like supplements, energy bars, rehydration solutions, and mineral water. The company’s mineral water comes in six flavors: strawberry, raspberry, peach, orange, lemon lime, and blueberry.

Database Description

The database used for this project is named hplussport and contains five tables:

• Customer: Stores customer information.
• OrderItem: Contains details of individual items in each order.
• Orders: Records customer orders.
• Product: Lists the products available for sale.
• Salesperson: Contains information about salespeople.

SQL Queries and Analysis

1. Inserting a New Product

Objective: Add a new grape flavor to the product table.

INSERT INTO Product (
    ProductID,
    ProductCode,
    ProductName,
    Size,
    Variety,
    Price,
    Status
)
VALUES (
    17,
    'MWPRA20',
    'Mineral Water',
    20,
    'Grape',
    '1.79',
    'ACTIVE'
);

2. Sorting Orders by Date

Objective: Sort orders by their creation date in descending order.

SELECT *
FROM Orders
ORDER BY CreationDate DESC;

3. Handling Null Values in Customer Table

Objective: Identify and remove entries with null values in essential columns.

SELECT *
FROM Customer
WHERE FirstName IS NULL OR LastName IS NULL OR Email IS NULL OR Phone IS NULL;

SELECT FirstName, LastName, Email, Phone
FROM Customer
WHERE Email IS NOT NULL AND Phone IS NOT NULL;

4. Analyzing Sales by Month and Year

Objective: Determine the total orders, quantities, and amount by month and year.

SELECT
    MONTHNAME(CreationDate) AS MonthName,
    YEAR(CreationDate) AS OrderYear,
    MONTH(CreationDate) AS OrderMonth,
    COUNT(Orders.OrderID) AS TotalOrders,
    SUM(Quantity) AS TotalQuantity,
    SUM(TotalDue) AS TotalAmount
FROM Orders
LEFT OUTER JOIN OrderItem
ON Orders.OrderID = OrderItem.OrderID
GROUP BY MonthName, OrderYear, OrderMonth
ORDER BY OrderYear, OrderMonth;

5. Identifying Discontinued Products

Objective: Find products that are no longer active.

SELECT *
FROM Product
WHERE Status = "DISCONTINUED";

6. Top Products and Sizes Sold

Objective: Determine the top product sizes sold and the top three items based on quantity.

SELECT Size, SUM(Quantity) AS TotalQuantity
FROM OrderItem
LEFT OUTER JOIN Product
ON OrderItem.ProductID = Product.ProductID
GROUP BY Size
ORDER BY TotalQuantity DESC;

SELECT Variety, Size, SUM(Quantity) AS TotalQuantity
FROM OrderItem
LEFT OUTER JOIN Product
ON OrderItem.ProductID = Product.ProductID
GROUP BY Product.ProductID
ORDER BY TotalQuantity DESC
LIMIT 3;

7. Identifying Top Customers and Salespeople

Objective: Identify the top customers and salespeople who made no sales.

SELECT FirstName, LastName, COUNT(DISTINCT Orders.OrderID) AS TotalOrders, SUM(Quantity) AS TotalQuantity, SUM(TotalDue) AS TotalAmount
FROM Orders
LEFT OUTER JOIN OrderItem
ON Orders.OrderID = OrderItem.OrderID
LEFT OUTER JOIN Customer
ON Orders.CustomerID = Customer.CustomerID
GROUP BY Customer.CustomerID
ORDER BY TotalAmount DESC;

SELECT Salesperson.SalespersonID, FirstName, LastName
FROM Salesperson
LEFT OUTER JOIN Orders
ON Salesperson.SalespersonID = Orders.SalespersonID 
WHERE Orders.SalespersonID IS NULL;

8. Calculating Repeat Customer Rate

Objective: Calculate the percentage of customers who made more than one purchase.

WITH Repeat_Customers AS (
  SELECT CustomerID AS Repeat_Cus
  FROM Orders
  GROUP BY CustomerID
  HAVING COUNT(OrderID) > 1
)
SELECT 
(COUNT(DISTINCT Repeat_Cus) / COUNT(DISTINCT CustomerID)) * 100 AS CustomerRepeatRate
FROM Orders
LEFT OUTER JOIN Repeat_Customers
ON Orders.CustomerID = Repeat_Customers.Repeat_Cus;

Conclusion

This SQL project provides comprehensive insights into H+ Sport’s customer and sales data, focusing on enhancing the mineral water department’s performance. By executing various SQL queries, the project highlights key data management and analysis skills, making it a valuable component of a data science portfolio.

Recommendations

• Continue to monitor sales trends and customer behaviors.
• Utilize the insights from the SQL queries to make data-driven decisions.
• Expand the analysis to other departments for a holistic view of company performance.

• 2025 1
• 2023 14