-
Notifications
You must be signed in to change notification settings - Fork 0
/
Supply chain project.sql
166 lines (132 loc) · 5.83 KB
/
Supply chain project.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
CREATE DATABASE supplychainDB;
USE supplychainDB;
select * from supply_chain_data;
-- 1. Calculate total revenue for each product type
SELECT Product_type, ROUND(SUM(Revenue_generated), 4) AS Total_Revenue
FROM supply_chain_data
GROUP BY Product_type;
-- 2: How many products were sold for each product type
SELECT Product_type, SUM( Number_of_products_sold) AS Total_sold
FROM supply_chain_data
GROUP BY Product_type;
-- 3. What is the average price of products for each product type
SELECT Product_type, ROUND(AVG(Price),4) AS AVG_price
FROM supply_chain_data
GROUP BY Product_type;
-- 4. How many products are currently available for each product type
SELECT Product_type, SUM(Availability) AS Total_available_products
FROM supply_chain_data
GROUP BY Product_type;
-- 5. What is the total revenue generated by each supplier
SELECT Supplier_name, ROUND(SUM(Revenue_generated),2) AS Total_Revenue
FROM supply_chain_data
GROUP BY Supplier_name;
-- 6. What is the unique Customer Demographics with Products Sold Count
SELECT Customer_demographics, COUNT(Number_of_products_sold) AS Total_Products_Sold
FROM supply_chain_data
GROUP BY Customer_demographics
ORDER BY Total_Products_Sold DESC;
-- 7. What is the average stock level for all products
SELECT Product_type, CAST(AVG(Stock_levels)AS UNSIGNED) AS AVG_stock_level
FROM supply_chain_data
GROUP BY Product_type;
-- 8. How many total products have been sold in each location
SELECT Location, SUM(Number_of_products_sold) AS Total_Products_Sold_by_location
FROM supply_chain_data
GROUP BY Location;
-- 9. What is the total revenue generated from each location
SELECT Location, ROUND(SUM(Revenue_generated),2) AS Total_Revenue_by_location
FROM supply_chain_data
GROUP BY Location
ORDER BY Total_Revenue_by_location DESC;
-- 10. What is the average manufacturing cost for each product type
SELECT Product_type, ROUND(AVG(Manufacturing_costs),2) AS Average_Manufacturing_Cost
FROM supply_chain_data
GROUP BY Product_type
ORDER BY Average_Manufacturing_Cost DESC;
-- 11. What is the average shipping cost for each product type
SELECT Product_type, ROUND(AVG(Shipping_costs), 2) AS Average_Shipping_Cost
FROM supply_chain_data
GROUP BY Product_type;
-- 12. Which suppliers have the shortest lead times
SELECT Supplier_name, MIN(Lead_time) AS Shortest_Lead_Time
FROM supply_chain_data
GROUP BY Supplier_name
ORDER BY Shortest_Lead_Time ASC;
-- 13. What is the average defect rate for each product type
SELECT Product_type, ROUND(AVG(Defect_rates), 2) AS Average_Defect_Rate
FROM supply_chain_data
GROUP BY Product_type;
-- 14. Which transportation mode is used most frequently for each product type
SELECT Product_type, Transportation_modes, COUNT(*) AS Transport_Mode_Frequency
FROM supply_chain_data
GROUP BY Product_type, Transportation_modes
ORDER BY Transport_Mode_Frequency DESC;
-- 15. What is the average shipping time for each product type
SELECT Product_type, ROUND(AVG(Shipping_times), 2) AS Average_Shipping_Time_in_hrs
FROM supply_chain_data
GROUP BY Product_type;
-- 16. Which routes are used most frequently for shipping products
SELECT Routes, COUNT(*) AS Route_Frequency
FROM supply_chain_data
GROUP BY Routes
ORDER BY Route_Frequency DESC;
-- 17. What is the total number of products sold by each supplier
SELECT Supplier_name, SUM(Number_of_products_sold) AS Total_Products_Sold
FROM supply_chain_data
GROUP BY Supplier_name
ORDER BY Total_Products_Sold DESC;
-- 18. What is the average production volume for each product type
SELECT Product_type, ROUND(AVG(Production_volumes), 2) AS Average_Production_Volume
FROM supply_chain_data
GROUP BY Product_type
ORDER BY Average_Production_Volume DESC;
-- 19. What is the total number of defects reported for each product type
SELECT Product_type, CAST(SUM(Defect_rates)AS UNSIGNED) AS Total_Defects
FROM supply_chain_data
GROUP BY Product_type
ORDER BY Total_Defects DESC;
-- 20. Which locations have the shortest average lead times
SELECT Location, ROUND(AVG(Lead_time), 2) AS Average_Lead_Time
FROM supply_chain_data
GROUP BY Location
ORDER BY Average_Lead_Time ASC;
-- 21. Which product types experience the most defects based on inspection results
SELECT Product_type, COUNT(*) AS Defect_Count
FROM supply_chain_data
WHERE Inspection_results = 'Fail'
GROUP BY Product_type
ORDER BY Defect_Count DESC;
-- 22. What is the product margin for each product type
SELECT Product_type, ROUND((SUM(Revenue_generated) - SUM(Manufacturing_costs)) * 100.0 / SUM(Revenue_generated), 2) AS Profit_Margin_Percentage
FROM supply_chain_data
GROUP BY Product_type;
-- 23. What is the average order quantity by each supplier
SELECT Supplier_name, ROUND(AVG(Order_quantities), 2) AS Avg_Order_Quantity
FROM supply_chain_data
GROUP BY Supplier_name;
-- 24. What is the average Lead Time for Products Sold Above a Certain Threshold
SELECT Product_type, ROUND(AVG(Lead_time), 2) AS Avg_Lead_Time
FROM supply_chain_data
WHERE Number_of_products_sold > 500
GROUP BY Product_type;
-- 25. What is the ratio of Products Sold to Stock Levels
SELECT Product_type, ROUND(SUM(Number_of_products_sold) / SUM(Stock_levels), 2) AS Sold_to_Stock_Ratio
FROM supply_chain_data
GROUP BY Product_type;
-- 26. What is the revenue contribution by Transportation Mode
SELECT Transportation_modes, ROUND(SUM(Revenue_generated) * 100.0 / (SELECT SUM(Revenue_generated) FROM supply_chain_data), 2) AS Revenue_Percentage
FROM supply_chain_data
GROUP BY Transportation_modes
ORDER BY Revenue_Percentage DESC;
-- 27. What is the Customer Segmentation Based on Revenue Contribution
SELECT Customer_demographics,
SUM(Revenue_generated) AS Total_Revenue,
CASE
WHEN SUM(Revenue_generated) > 150000 THEN 'High-Value'
WHEN SUM(Revenue_generated) BETWEEN 100000 AND 150000 THEN 'Medium-Value'
ELSE 'Low-Value'
END AS Customer_Segment
FROM supply_chain_data
GROUP BY Customer_demographics
ORDER BY Total_Revenue DESC;