My first SQL Query Capstone as a Work Experience

AgeX Shipping Company Data Analysis using MySQL for SQL Query as a Capstone given by EvolveHQ Data Analytics Certification Program

Overview

AgeX Shipping Company is a global logistics firm that began its operations in February 2020. The company's primary business revolves around shipping packages between the USA, Canada, and Nigeria. This project analyzed AgeX Shipping Data using the collected data on their customers and shipping orders to provide valuable business insights.

Analytics Tool: MySQL

Experience Gained

I conducted end-to-end data analysis on 1,000+ customer and shipping records to uncover business insights for a global logistics company operating in the USA, Canada, and Nigeria. I implemented SQL queries involving joins, aggregations, grouping, and filtering to answer 17 analytics questions, including customer segmentation, sales trends, and route popularity. I delivered actionable recommendations that highlighted top-spending customers, profitable routes, and customer engagement trends, improving decision-making.

Data Source

AgeX Shipping Company Data was given by EvolveHQ

Problem-Solved

Part 1: Foundational Queries

These questions will test your understanding of basic SQL syntax and filtering.

1. Total Customers: Write a query to find the total number of customers registered with AgeX.
   
2. Customers Who Have Shipped: Find the total number of unique customers who have placed at least one shipping order.
   
3. Customers Without Orders: Find the names of all customers who are registered but have not placed any shipping orders.
   
4. Employee Discounts: Find the total amount of money AgeX has given in discounts to its employees.
   

Part 2: Aggregation and Grouping

Use aggregate functions (COUNT, SUM, AVG) and the GROUP BY clause to answer the following questions.
5. Customers by Region: Show the total number of customers located in each country.
6. Sales by Region: Calculate the total sales (final cost) generated by shipments originating from each country.
7. Sales by Destination: Calculate the total sales for packages shipped to each destination country.
8. Sales Over Time: Find the total sales for each year since the company started.
9. Average Shipping Cost: What is the average shipping cost per pound (weight_lbs) for packages from each origin country?

Part 3: Joins, HAVING, and WHERE Clauses

Apply your knowledge of joins and advanced filtering to solve these problems.
10. Most Expensive Order: Find the package_id and the final_cost of the most expensive order in the database.
11. High-Value Customers: Find the customer_id and the total amount they have spent for all customers who have spent more than $500 in total. Order the results from the highest spender to the lowest.

12. Sales from Specific Region: What is the total revenue generated from shipments originating in Nigeria and destined for Canada?
13. Employee's Sales: Find the total sales generated by employee shipments, but only for orders that weigh more than 30 lbs.
14. Popular Routes: Find the top 3 most popular shipping routes (from origin_country to destination_country) and the number of packages sent on each route.

Part 4: Wildcards, Aliases, and Concatenation

These questions require you to use more advanced SQL techniques.
15. Customers with Specific Names: Find all customers whose last name starts with the letter 'O'.
16. Package Tracking: Create a single column that combines the package_id, origin_country, and destination_country into a readable string, for example: Package US-ABCD from USA to Nigeria.
17. Top Spenders by Country: Find the top 5 customers in the USA based on their total spending. Show their full name and the total amount spent.

All these question were solved and answer in the report folder

Results

AgeX Shipping Company has 181 customers in Canada, 446 customers in USA, and 373 in Nigeria which are 1,000 customers in total. 784 customers out of them have placed at least one shipping order while the rest 216 customers have not placed any shipping order.
They employed 50 customers as their employee with a reward of discounted price, and the total sum of discounted price given to all them so far is amount to ($)1,373.36
The total amount of sales is ($) 233,943.49. Its breakdown is as follow:
Total sales by Region are ($)48,704.30 from Canada, ($)83,179.32 from Nigeria, and 102,059.87 from USA.
Also, their total sales by Destination are ($)150,764.17 to Nigeria, ($)40,184.47 to USA, ($)42,994.85 to Canada.
According to total sales in each years, they earned ($)28,520.95 in 2025, ($)48,748.82 in 2024, ($)45,368.96 in 2023, ($)45,603.74 in 2022, ($)45661.94 in 2021, and ($)20,039.08 in 2020.
The average shipping cost per pound (weight lbs) for packages from Canada is ~($)7, Nigeria is ~($)6, and USA is ~($)6 The most expensive Package ID ordered is CA-FJUF with amount of ($)349.65
The top 3 high value customers are as follows: customer with Customer ID - 302 spent ($)1,110.84, Customer ID - 729 spent ($)1,077.37, and Customer ID - 960 spent ($)1,035.66.
The top 3 most popular shipping routes are shipping from USA to Nigeria with total package of 668, shipping from Nigeria to Canada with total package of 284, and shipping from Canada to Nigeria with total package of 277.
They have 114 records of customers that their last name start with ‘O’.
The top 5 customers in the USA based on their total spending are Michael David spent ($)1,035.66, Robert Emily spent ($)931.32, Michael Chris spent ($)861.90, William John spent ($)853.08, and Robert Ashley spent ($)842.88