Mint-Classics-Model-Car-Database-SQL-Project
Luis Donaldo
2024-10-01
Project Scenario
Mint Classics Company, a retailer of classic model cars and other vehicles, is looking at closing one of their storage facilities.
To support a data-based business decision, they are looking for suggestions and recommendations for reorganizing or reducing inventory, while still maintaining timely service to their customers.
The analysis aims to respond in an enunciative but non-limiting manner to the following three questions:
Where are items stored and if they were rearranged, could a warehouse be eliminated?
How are inventory numbers related to sales figures? Do the inventory counts seem appropriate for each item?
Are we storing items that are not moving? Are any items candidates for being dropped from the product line?
Project Objectives
Explore products currently in inventory.
Determine important factors that may influence inventory reorganization/reduction.
Provide analytic insights and data-driven recommendations.
Data Analysis Strategy
Import: the database was added to the MySQL workbench environment and then you will see how the tables that make it up are related.
Explore: run simple queries that allow us to better understand the database and the business.
Aggregations: run queries to obtain statistics at various grouping levels, making it easier to identify relevant findings.
Answer questions: execute combinations of different queries that allow us to solve the problems initially posed.
1. Basic understanding of business
This is how the model for this business is set up:
Some important statistics on stock levels:
dbGetQuery(conn = car_db,
statement = "
SELECT
min(quantityInStock) AS minimo,
max(quantityInStock) AS maximo,
AVG(quantityInStock) AS media
FROM products;")## minimo maximo media
## 1 15 9997 5046.645This is how the different product lines are distributed across the four warehouses:
dbGetQuery(conn = car_db,
statement = "
SELECT
productLine,
warehouseCode,
COUNT(productName) as products_count
FROM products
GROUP BY productLine, warehouseCode
ORDER BY warehouseCode;")## productLine warehouseCode products_count
## 1 Motorcycles a 13
## 2 Planes a 12
## 3 Classic Cars b 38
## 4 Vintage Cars c 24
## 5 Trucks and Buses d 11
## 6 Ships d 9
## 7 Trains d 3This is the percentage of stock that each of the product lines represents:
dbGetQuery(conn = car_db,
statement = "
SELECT
productLine,
ROUND(SUM(DISTINCT quantityInStock) / (SELECT SUM(quantityInStock) FROM products) *100, 2) AS Inventory
FROM products
GROUP BY productLine
ORDER BY Inventory DESC;")## productLine Inventory
## 1 Classic Cars 39.48
## 2 Vintage Cars 22.50
## 3 Motorcycles 12.50
## 4 Planes 11.22
## 5 Trucks and Buses 6.46
## 6 Ships 4.83
## 7 Trains 3.01This is the percentage of sales that each of the product lines represents:
dbGetQuery(conn = car_db,
statement = "
SELECT
p.productLine,
SUM(o.quantityOrdered * priceEach) AS totalSale,
ROUND(
(SUM(o.quantityOrdered * priceEach) /
(SELECT SUM(quantityOrdered *priceEach) FROM orderdetails)) *100,2) AS salesPerct
FROM orderdetails AS o
LEFT JOIN products AS p ON o.productCode = p.productCode
GROUP BY p.productLine
ORDER BY salesPerct DESC;")## productLine totalSale salesPerct
## 1 Classic Cars 3853922.5 40.13
## 2 Vintage Cars 1797559.6 18.72
## 3 Motorcycles 1121426.1 11.68
## 4 Trucks and Buses 1024113.6 10.66
## 5 Planes 954637.5 9.94
## 6 Ships 663998.3 6.91
## 7 Trains 188532.9 1.96Insights:
There are a total of 110 different products, which are divided into 7 different product lines.
The Classic Cars and Vintage Cars lines have the largest volume of inventory, just between them they account for more than 60% of the total current inventory and more than half of total sales.
Warehouses B and C, that is, East and West, respectively, have all of these two product lines in their inventory.
2. Business Operation
This is the profit margin that each product line returns to the business:
dbGetQuery(conn = car_db,
statement = "
SELECT
p.productLine,
ROUND((SUM(o.priceEach * o.quantityOrdered) - SUM(p.buyPrice * o.quantityOrdered)) / SUM(o.priceEach * o.quantityOrdered) * 100, 2) AS profit
FROM products p
RIGHT JOIN orderdetails o ON o.productCode = p.productCode
GROUP BY p.productLine
ORDER BY profit DESC;")## productLine profit
## 1 Motorcycles 41.84
## 2 Vintage Cars 41.01
## 3 Classic Cars 39.60
## 4 Ships 39.35
## 5 Trucks and Buses 39.11
## 6 Planes 38.34
## 7 Trains 34.66Listed below are the products that have the worst ratio between sales percentage, inventory and profit:
dbGetQuery(conn = car_db,
statement = "
SELECT
m.productName,
p.productLine,
p_sales,
p_inventory,
profit
FROM margin_p_product m
INNER JOIN productos_bajos p ON m.productName = p.productName
WHERE p_sales < 1 AND p_inventory > 1 AND profit < 40;")## productName productLine p_sales p_inventory profit
## 1 1939 Chevrolet Deluxe Coupe Vintage Cars 0.29 1.32 24.61
## 2 1982 Ducati 996 R Motorcycles 0.35 1.66 34.26
## 3 1966 Shelby Cobra 427 S/C Classic Cars 0.44 1.48 36.45
## 4 1982 Ducati 900 Monster Motorcycles 0.60 1.23 23.09
## 5 1940 Ford Delivery Sedan Vintage Cars 0.74 1.19 36.95
## 6 American Airlines: B767-300 Planes 0.77 1.05 38.10
## 7 Collectable Wooden Train Trains 0.86 1.16 24.93
## 8 18th Century Vintage Horse Carriage Vintage Cars 0.89 1.08 35.44
## 9 2002 Chevy Corvette Classic Cars 0.91 1.70 36.47
## 10 1992 Porsche Cayenne Turbo Silver Classic Cars 0.97 1.19 34.93This is the aggregation of percentages that these products represent together for each of the indicators listed above:
dbGetQuery(conn = car_db,
statement = "
SELECT
SUM(p_sales) AS '% sales',
SUM(p_inventory) AS '% inventory',
ROUND(AVG(profit),2) AS profit
FROM peores_productos;")## % sales % inventory profit
## 1 6.82 13.06 32.52A search was conducted to identify which products have not generated any sales:
dbGetQuery(conn = car_db,
statement = "
SELECT
p.productName,
p.quantityInStock as inventory,
sum(o.quantityOrdered) as sales
FROM products p
LEFT JOIN orderdetails o ON p.productCode = o.productCode
GROUP BY productName, quantityInStock
HAVING sales IS NULL
ORDER BY sales;")## productName inventory sales
## 1 1985 Toyota Supra 7733 NAA customer review was conducted to identify the 10 customers with the highest number of orders:
dbGetQuery(conn = car_db,
statement = "
SELECT
c.customerNumber,
c.customerName,
c.country,
COUNT(o.orderNumber) AS n_orders
FROM customers c
RIGHT JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY c.customerNumber, c.customerName, c.country
ORDER BY n_orders DESC
LIMIT 10;")## customerNumber customerName country n_orders
## 1 141 Euro+ Shopping Channel Spain 26
## 2 124 Mini Gifts Distributors Ltd. USA 17
## 3 323 Down Under Souveniers, Inc New Zealand 5
## 4 148 Dragon Souveniers, Ltd. Singapore 5
## 5 114 Australian Collectors, Co. Australia 5
## 6 353 Reims Collectables France 5
## 7 145 Danish Wholesale Imports Denmark 5
## 8 121 Baane Mini Imports Norway 4
## 9 128 Blauer See Auto, Co. Germany 4
## 10 496 Kelly's Gift Shop New Zealand 4These 10 customers alone account for more than a quarter of total sales:
dbGetQuery(conn = car_db,
statement = "
SELECT
ROUND(SUM(od.quantityOrdered * od.priceEach) / (SELECT SUM(quantityOrdered * priceEach) FROM orderdetails)*100, 2) as perc_sale
FROM orderdetails od
LEFT JOIN orders o ON od.orderNumber = o.orderNumber
INNER JOIN top_customers c ON o.customerNumber = c.customerNumber;")## perc_sale
## 1 25.8Payments made by customers were compared against the total purchases of each one and there are some customers who have not yet settled their balance:
dbGetQuery(conn = car_db,
statement =
"SELECT
o.customerNumber,
c.customerName,
SUM(g_orderdet.totalSale) - MIN(g_payments.amount) AS balance
FROM orders o
LEFT JOIN customers c ON o.customerNumber = c.customerNumber
LEFT JOIN (
SELECT
customerNumber,
SUM(amount) AS amount,
MAX(paymentDate) AS lastPayment
FROM payments
GROUP BY customerNumber
) AS g_payments ON o.customerNumber = g_payments.customerNumber
LEFT JOIN (
SELECT
orderNumber,
SUM(priceEach * quantityOrdered) as totalSale
FROM orderdetails
GROUP BY orderNumber
) AS g_orderdet ON o.orderNumber = g_orderdet.orderNumber
GROUP BY o.customerNumber, c.customerName
HAVING balance > 0;")## customerNumber customerName balance
## 1 119 La Rochelle Gifts 41623.44
## 2 124 Mini Gifts Distributors Ltd. 7639.10
## 3 131 Land of Toys Inc. 41445.21
## 4 141 Euro+ Shopping Channel 104950.56
## 5 144 Volvo Model Replicas, Co 23014.17
## 6 145 Danish Wholesale Imports 21638.62
## 7 157 Diecast Classics Inc. 5849.44
## 8 166 Handji Gifts& Co 2326.18
## 9 201 UK Collectables, Ltd. 45443.54
## 10 282 Souveniers And Things Co. 42251.51
## 11 314 Petit Auto 8597.73
## 12 328 Tekni Collectables Inc. 43525.04
## 13 357 GiftsForHim.com 37769.38
## 14 362 Gifts4AllAges.com 50806.85
## 15 382 Salzburg Collectables 52420.07
## 16 386 L'ordine Souveniers 35362.26
## 17 412 Extreme Desk Decorations, Ltd 23627.44
## 18 448 Scandinavian Gift Ideas 44167.09
## 19 450 The Sharp Gifts Warehouse 83984.89
## 20 471 Australian Collectables, Ltd 10945.26
## 21 496 Kelly's Gift Shop 22963.60Regarding the list of previous clients, there are 3 clients who are over their credit limit:
dbGetQuery(conn = car_db,
statement = "
SELECT
o.customerNumber,
c.customerName,
c.creditLimit,
SUM(g_orderdet.totalSale) - MIN(g_payments.amount) AS balance,
CASE
WHEN SUM(g_orderdet.totalSale) - MIN(g_payments.amount) > c.creditLimit THEN 'Above'
ELSE 'Below' END AS evaluation
FROM orders o
LEFT JOIN customers c ON o.customerNumber = c.customerNumber
LEFT JOIN (
SELECT
customerNumber,
SUM(amount) AS amount,
MAX(paymentDate) AS lastPayment
FROM payments
GROUP BY customerNumber
) AS g_payments ON o.customerNumber = g_payments.customerNumber
LEFT JOIN (
SELECT
orderNumber,
SUM(priceEach * quantityOrdered) as totalSale
FROM orderdetails
GROUP BY orderNumber
) AS g_orderdet ON o.orderNumber = g_orderdet.orderNumber
GROUP BY o.customerNumber, c.customerName, c.creditLimit
HAVING balance > 0 AND evaluation = 'Above';")## customerNumber customerName creditLimit balance evaluation
## 1 328 Tekni Collectables Inc. 43000 43525.04 Above
## 2 362 Gifts4AllAges.com 41900 50806.85 Above
## 3 450 The Sharp Gifts Warehouse 77600 83984.89 AboveInsights:
15 of the 20 best-selling products are part of the Classic Cars product line, this product line represents 40% of total revenue.
The Motorcycles product line has the best profit margin, exceeding 40%, while the Classic Cars line ranks third with 39.6%.
There are at least 11 products that have a bad relationship in terms of % sales - % inventory - % profit. These occupy almost 15% of the stock and do not represent even 7% of sales, and their profit margin is just below 30%. 7 of these 11 products are part of the Classic and Vintage Cars lines.
Additionally, the 1985 Toyota Supra from the Classic Cars line has not sold a single unit.
20% of customers experience a delay in their payments.
3. Conclussion
Consideration should be given to destocking or completely removing several products from inventory, as the cost of carrying them is likely to be greater than the profit they return to the company. Twelve products have already been identified as strong candidates, including the 1985 Toyota Supra product line that has not generated sales.
According to the current distribution of inventories, the South warehouse is the one that represents the lowest percentage of sales and stock, so it would be a good idea to redistribute its product lines to the East and West warehouses, once the stock of the products listed above has been removed.
The company’s credit policy seems to work, since although there are some customers who have outstanding balances to settle, only 3 of these are above their credit limit.