Explore The Database
1.Can you identify the top 10 models that are in demand? Our first question pertains to identifying items that are in high demand in most orders and highly profitable to us. Stocking up on these items will ensure that we can fulfil the market needs and simultaneously make some extra cash.
The customer has provided formulas by which we are expected to identify items that are currently in low stock and to identify items that exhibit great selling power.
WITH
--#Ten most popular products
get_lowstock AS(
SELECT productCode,
--#Use low stock formula
(SUM(od.quantityOrdered)*1.0)/
(SELECT quantityInStock
FROM products
WHERE od.productCode = productCode) AS low_stock
FROM shipped_order_details od
GROUP BY productCode
ORDER BY low_stock DESC
LIMIT 10
)
SELECT products.productName AS Model_Name,
products.quantityInStock AS Quantity_on_Hand,
COUNT(*) AS No_of_Orders,
SUM(shipped_order_details.quantityOrdered) AS Total_Quantity_Ordered,
--#Use product performance formula
ROUND(SUM(shipped_order_details.quantityOrdered*shipped_order_details.priceEach),2) AS Total_Sales,
products.productLine AS Prouct_Line
FROM shipped_order_details
JOIN products
ON products.productCode = shipped_order_details.productCode
WHERE shipped_order_details.productCode IN (SELECT productCode
FROM get_lowstock)
GROUP BY products.productCode
ORDER BY Total_Sales DESC
--LIMIT 10;
Clearly the 2001 Ferrari Enzo is the model which is most in demand and less in quantity. Its potential sales value is also quite promising. Besides the Ferrari model, there are a number of other models that demand ordering more of based on their potential sales value.
Another revealation from the above analysis is that the Classic Cars model seems to be the most popular model. We shall analyse this more as we go further.
The next task placed before us is to identify customers who have been most loyal. Vehicle Models has 122 customers. We shall define the top 5 customers as those who have reaped the most profit for the organisation through their shipped orders.
CREATE VIEW customer_profit_report AS
SELECT shipped_orders.customerNumber AS customer_id,
ROUND(SUM(shipped_order_details.quantityOrdered * (shipped_order_details.priceEach - products.buyPrice)),2) AS profit,
COUNT(*) AS no_of_orders
FROM shipped_orders
JOIN shipped_order_details
ON shipped_order_details.orderNumber=shipped_orders.orderNumber
JOIN products
ON shipped_order_details.productCode=products.productCode
GROUP BY shipped_orders.customerNumber;
SELECT customers.contactLastName||", "||customers.contactFirstName AS Name,
customers.city AS City,
customers.country AS Country,
customer_profit_report.profit AS Profit_Earned
FROM customers
JOIN customer_profit_report
ON customers.customerNumber = customer_profit_report.customer_id
WHERE customer_profit_report.customer_id IN (SELECT customer_id
FROM customer_profit_report
ORDER BY profit DESC
LIMIT 5)
ORDER BY customer_profit_report.profit DESC;
3. In which countries should we target our marketing for maximum return?
This analysis should help to decide which countries to focus marketing on so as to increase profits.
SELECT customers.country AS Country,
COUNT(*) AS No_of_Customers,
ROUND(SUM(customer_profit_report.profit),2) AS Profit_Earned,
ROUND(SUM(customer_profit_report.profit)/COUNT(*),2) AS Profit_Per_Customer,
ROUND((SUM(customer_profit_report.profit) * 100.0 /
(SELECT SUM(profit)
FROM customer_profit_report)), 2) AS Profit_Percentage
FROM customers
JOIN customer_profit_report
ON customers.customerNumber = customer_profit_report.customer_id
GROUP BY customers.country
ORDER BY Profit_Earned DESC;
Insights
4. Identify the best and worst categories for each country
There is a lack of clarity with regards to the parameter to judge the best and worst of product lines. Since we are allowed to make assumptions we shall go with the quantity of the product line being ordered. Based on the representation of the product lines in the order details we shall find out which product lines are the best and worst in each country.
WITH
--#Join products and orderdetails
order_category AS(
SELECT orderdetails.orderNumber,
products.productLine
FROM products
JOIN orderdetails
ON products.productCode=orderdetails.productCode
),
--#Join customers and orders
country_order AS(
SELECT customers.country,
orders.orderNumber
FROM orders
JOIN customers
ON orders.customerNumber=customers.customerNumber
),
--#Combine the above joins
country_category AS(
SELECT country_order.country AS Country,
order_category.productLine AS Category
FROM country_order
JOIN order_category
ON country_order.orderNumber=order_category.orderNumber
),
--#List all categories and their counts along with each country
country_category_count AS(
SELECT Country,
Category,
COUNT(Category) AS category_count
FROM country_category
GROUP BY Country,Category
)
--#Select the category with the most and least count
SELECT Country,
(SELECT Category
FROM country_category_count
GROUP BY Country
HAVING Country=c.Country AND category_count=MAX(category_count)) AS Best_Category,
(SELECT Category
FROM country_category_count
GROUP BY Country
HAVING Country=c.Country AND category_count=MIN(category_count)) AS Worst_Category
FROM country_category_count c
GROUP BY Country
/*SELECT MAX(category_count)
FROM (SELECT COUNT(Category) AS category_count
FROM country_category
GROUP BY Category
HAVING country = 'USA')*/
/*SELECT Country,
Category,
COUNT(Category) AS category_count
FROM country_category
GROUP BY Country,Category*/
Insights:
5. We want to expand our sales team, however we need to know the performance of our current sales team.
Vehicle Models have a plan to expand their sales staff, however they are keen to find out the performance of their current staff. They have recently noticed a slump in new customers and believe that the performance of the sales team might have something to do with it.
SELECT employees.lastName||', '||employees.firstName AS Sales_Rep_Name,
offices.territory,
offices.city,
SUM(customer_profit_report.no_of_orders) AS No_of_Orders,
ROUND(SUM(customer_profit_report.profit)/SUM(customer_profit_report.no_of_orders),2) AS Profit_per_Order,
ROUND(SUM(customer_profit_report.profit),2) AS Profit_Earned,
ROUND((SUM(customer_profit_report.profit) * 100.0 /
(SELECT SUM(profit)
FROM customer_profit_report)), 2) AS Percentage
FROM customers
JOIN customer_profit_report
ON customers.customerNumber = customer_profit_report.customer_id
JOIN employees
ON customers.salesRepEmployeeNumber = employees.employeeNumber
JOIN offices
ON employees.officeCode=offices.officeCode
GROUP BY employees.employeeNumber
ORDER BY Profit_Earned DESC;
Insights
6. Should we focus on getting new customers or engaging our existing customers to increase our sales?
Analysing whether budget must be allocated to gain new customers
WITH
--#Set date to year_month format in payments
payment_year_month AS(
SELECT *,
CAST(SUBSTR(paymentDate, 1,4) AS INTEGER)*100 + CAST(SUBSTR(paymentDate, 6,7) AS INTEGER) AS year_month
FROM payments
),
--#Count number of customers for each month and the total monthly sales
monthly_payment_report AS(
SELECT year_month,
COUNT(DISTINCT customerNumber) AS number_of_customers,
SUM(amount) AS monthly_total
FROM payment_year_month
GROUP BY year_month
),
--#Count number of new customers and the sales total generated by them
new_customers_report AS(
SELECT year_month AS Month,
COUNT(DISTINCT customerNumber) AS number_of_new_customers,
SUM(amount) AS new_customer_monthly_total,
(SELECT number_of_customers
FROM monthly_payment_report
WHERE year_month = p1.year_month) AS total_customers_that_paid,
(SELECT monthly_total
FROM monthly_payment_report
WHERE year_month = p1.year_month) AS total_amount_paid
FROM payment_year_month p1
WHERE customerNumber NOT IN (SELECT customerNumber
FROM payment_year_month p2
WHERE p2.year_month < p1.year_month)
GROUP BY year_month
)
--#Calculate the new customers and their total sales contributions by percentage for each month
SELECT Month,
ROUND(number_of_new_customers*100/total_customers_that_paid) AS Percentage_New_Customers,
ROUND(new_customer_monthly_total*100/total_amount_paid) AS Percentage_Paid_by_New_Customers
FROM new_customers_report
Insights
Deadstock are products that have never been sold for a long time. They tend to be a cost because of the space they take up and could end up being a loss if they are dealt with because of the damage they may incur. This may cause it go completely unsold.
We shall identify items that have not been sold for one year and the cost incurred by the same.
WITH
orders_year_month AS(
SELECT *,
CAST(SUBSTR(orderDate,1,4) AS INTEGER)*100 + CAST(SUBSTR(orderDate,6,7) AS INTEGER) AS year_month
FROM orders
),
last_six_months_orders AS(
SELECT DISTINCT orderdetails.productCode
FROM orderdetails
JOIN orders_year_month
ON orderdetails.orderNumber = orders_year_month.orderNumber
WHERE orders_year_month.year_month BETWEEN 200405 AND 200505
)
SELECT products.productName AS Product,
products.quantityinStock AS Stock_Quantity,
ROUND(products.buyPrice * products.quantityinStock,2) AS Cost
FROM products
WHERE productCode NOT IN (SELECT productCode
FROM last_six_months_orders);
Insights