Sitemap
Kelvin Kipsang

Kelvin Kipsang

Enthusiast of drawing insights from the data

SQL Project

7 min readFeb 25, 2023

--

In this SQL project I am using the Mysql workbench for data analysis . I source the adventure dataset with 6 tables from kaggle.I then export the data to mysqlbench and named as adventure database/schema.

Find the link for the dataset below.

https://drive.google.com/drive/folders/1TwTFj3V5XV4zL-fljnnX05nRGX4seg7h?usp=sharing

The database contain the following tables:

i) customer table — with customer information.

ii) sales tables — with sales information.

ii)products tables — with product information.

iii)region/territory tables — with region or territory information

iv) product Category tables — with product category information.

v) product sub category table tables — with product sub category information.

Let’s start by identifying the trend of sales over time period.

There is a lot to explore in this data but I will only partition into four parts

PART1,PART 2,PART 3 and PART 4

PART 1

i) First we want to know the earliest date and latest date of the birth of the customers. The youngest customer was born on 29/03/1964 and the eldest one was born on 01/01/1939.

ii) We also want to know the earliest and latest date of the product purchase.

The first time product was purchased was on 31/12/2003 and the last purchase date was on 01/01/2002. But, this may be incorrect as there is some information missing since some customers were not willing to provide such details.

Press enter or click to view image in full size

iii) Now we want to identify the yearly sales trend from year 2001 to 2004

Press enter or click to view image in full size

As time goes by from the year 2001 to 2004 the sales amount reduces, this is a worrying trend. Therefore I can confirm this by scripting with an alternative query below to find the average of sales amount yearly.

We can confidently conclude that as the years goes by the sales amount drops.

iv) We now want to know the date which customers are mostly likely to buy the products.

The date 30th of the month is the most preferred day by the customers. Of course, this is the end month when most customers received their monthly salary.

v) Next, we want to know the cause of the sales sluggishness. Personally, I am suspecting the rise in tax might be the reason. Let’s see how the two correlate.

Press enter or click to view image in full size

It’s a big NO , the sales amount and tax amount has positive correlation.

PART 2

i ) We want to know if the majority of our customers are married or not-married. Obviously, the youth takes lead but with a slight difference.

ii) We then want to compare the gender of customers.

Males take front-line but with a small margin.

iii) Next we want to know the source of the income of our customers.

Actually, I didn’t expect this result, since the most Proffessional, skilled manual and managers group owned the cars .However, the reasons could be because of fit-exercise, competition or for fun purpose. After all, the major customers are youth group.

iv) Still, I am not convinced, now I want to confirm that the occupation of the most customers are highly paid group.

I am now convinced , the same youth groups are the ones taking the lead.

v) Now let’s find , the distribution of the number of the cars among the customers. Few people don’t owned cars.

The above result, could be seem as biased, let’s find the percentage of those with cars and those without.

Wow, the above result give a lot of confidence, 77% are group with cars and the remaining percentage 22.9% are the ones without.

Get Kelvin Kipsang’s stories in your inbox

Join Medium for free to get updates from this writer.

vi) Now we want to aggregate the sales amount averages for the different regions .

Australia is the region with the highest average sales amount and the smallest average is the central region.

vii) Let’s query the customers names and region names that have high sales amount.

Frankln HE from United Kingdom is the customer who spend high amount to buy the product.

PART 3

i ) Now we want to know the most expensive product

ii) Next , let’s count all the product names with black color and label the rest as others.

Press enter or click to view image in full size

The number of product name which are black in color are 8 and ‘others’ are 243

iii) We want to know the most preferred colors of the product in United Kingdom region.

Black is the most preferred color of the product in United Kingdom.

iv) Now we want to know the weight of product that take longer more (days) to manufacture.

2.3 kgs product take more days to manufacture.

PART 4

i) Ranking by dense method the income between the male and female customers.

Press enter or click to view image in full size

The female customers earn more than male customers.

ii) Now , let’s dive deeper to provide the name of the customer in each region with the largest amount of total sales.

SELECT s3.Customer_Name, s3.Region_Name, s3.total_sales
FROM(SELECT region_name, MAX(total_sales) total_sales
FROM(SELECT c.Name Customer_Name, r.Region Region_Name, SUM(s.SalesAmount) total_sales
FROM adventure.sales s
JOIN adventure.customer c
ON s.CustomerKey = c.CustomerKey
JOIN adventure.region r
ON s.SalesTerritoryKey = r.TerritoryKey
GROUP BY 1, 2) s1
GROUP BY 1) s2
JOIN (SELECT c.Name Customer_name, r.Region Region_Name, SUM(s.SalesAmount) total_sales
FROM adventure.sales s
JOIN adventure.customer c
ON s.CustomerKey = c.CustomerKey
JOIN adventure.region r
ON s.SalesTerritoryKey = r.TerritoryKey
GROUP BY 1,2
ORDER BY 3 DESC) s3
ON s3.Region_Name = s2.Region_Name AND s3.total_sales = s2.total_sales;

Output

iii) Next we want the region with the largest sales total amount and their respective sales amount

 SELECT c.Name, r.region, COUNT(s.SalesAmount) SalesAmount
FROM adventure.customer c
JOIN adventure.sales s
ON c.CustomerKey= s.CustomerKey
JOIN adventure.region r
ON s.SalesTerritoryKey = r.TerritoryKey
GROUP BY r.Region,c.Name
HAVING SUM(s.SalesAmount) = (
SELECT MAX(total_amt)
FROM (SELECT r.region region_name,c.Name, SUM(s.SalesAmount) total_amt
FROM adventure.customer c
JOIN adventure.sales s
ON c.CustomerKey= s.CustomerKey
JOIN adventure.region r
ON s.SalesTerritoryKey = r.TerritoryKey
GROUP BY r.region,c.Name) sub

Output:

iv) How many customers had more number of children than those customers name which has the most total amount of sales product.

SELECT COUNT(*)
FROM (SELECT c.Name
FROM adventure.customer c
JOIN adventure.sales s
ON c.CustomerKey = s.CustomerKey
GROUP BY 1
HAVING SUM(s.SalesAmount) > (SELECT AVG(total )
FROM (SELECT p.Class, SUM(s.SalesAmount) total
FROM adventure.sales s
JOIN adventure.products p
ON p.ProductKey = s.ProductKey
GROUP BY 1
ORDER BY 2
LIMIT 1) inner_tab)
) counter_tab;

SELECT c.Name, AVG(s.SalesAmount),AVG(c.NumberChildrenAtHome) tot_child
FROM adventure.customer c
JOIN adventure.sales s
ON c.CustomerKey = s.CustomerKey
GROUP BY 1
ORDER BY 2 DESC

OUTPUT:

v) Next, we want to find the customer that spent the most sales amount and how many total sales amount spent by the customers.

SELECT c.Occupation Occupation,c.Gender  Gender, SUM(s.SalesAmount) Sales_Amount
FROM adventure.sales s
JOIN adventure.customer c
ON c.CustomerKey = s.CustomerKey AND
s.SalesTerritoryKey = (SELECT SalesTerritoryKey
FROM ( SELECT c.Name,r.Region,SUM(s.SalesAmount)
FROM adventure.sales s
JOIN adventure.customer c
ON s.CustomerKey = c.CustomerKey
JOIN adventure.region r
ON s.SalesTerritoryKey = r.TerritoryKey
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 1
) inner_table)
GROUP BY 1, 2
ORDER BY 3 DESC;

OUTPUT:

SELECT Customer_Name,tot_sales
FROM (
SELECT c.Name Customer_Name, SUM(s.SalesAmount) tot_sales
FROM adventure.sales s
JOIN adventure.customer c
ON s.CustomerKey= c.CustomerKey
GROUP BY 1
ORDER BY 2 DESC
)sub;



SELECT c.Occupation Occupation,c.Gender Gender, SUM(s.SalesAmount) Sales_Amount
FROM adventure.sales s
JOIN adventure.customer c
ON c.CustomerKey = s.CustomerKey AND
s.SalesTerritoryKey = (SELECT SalesTerritoryKey
FROM ( SELECT c.Name,r.Region,SUM(s.SalesAmount)
FROM adventure.sales s
JOIN adventure.customer c
ON s.CustomerKey = ORDER BY 3 DESC;

OUTPUT:

Press enter or click to view image in full size

vii) Region with the highest average total sales amount than the overall average sales amount.

SELECT Region_Name,group_avg
FROM (SELECT r.Region Region_Name , AVG(s.SalesAmount) group_avg
FROM adventure.sales s
JOIN adventure.region r
ON s.SalesTerritoryKey = r.TerritoryKey
GROUP BY 1
HAVING AVG(s.SalesAmount) > (SELECT AVG(s.SalesAmount) avg_overall
FROM adventure.sales s)) temp_table;
GROUP BY 1
ORDER BY 2

I feel like going on, but this is the endless task

Please give me a clap, comment and follow so that we can share.

https://github.com/2022khev

https://www.linkedin.com/in/kelvin-kipsang-14114517a

https://twitter.com/KelvinKipsang3?t=kR-_YdZJdHvWRYYyZ-MJhw&s=09

--

--

Kelvin Kipsang
Kelvin Kipsang

Written by Kelvin Kipsang

Enthusiast of drawing insights from the data