-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathAdventure_works_task_query.sql
371 lines (271 loc) · 16.4 KB
/
Adventure_works_task_query.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
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
/* ADVENTURE WORKS BUSINESS TASKS*/
USE Adworks22
/* Data preparation*/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Combining the sales table to create 1 overall sales table
CREATE TABLE Overall_sales (OrderDate DATE,
StockDate DATE,
OrderNumber NVARCHAR(50),
ProductKey NVARCHAR(50),
CustomerKey NVARCHAR(50),
TerritoryKey NVARCHAR(50),
OrderLineItem NVARCHAR(50),
OrderQuantity INT)
Insert into Overall_sales
SELECT * FROM [AdventureWorks Sales Data 2020]
UNION ALL
SELECT * FROM [AdventureWorks Sales Data 2021]
UNION ALL
SELECT * FROM [AdventureWorks Sales Data 2022]
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Creating a column for Profit in the product Lookup table
ALTER TABLE [AdventureWorks Product Lookup]
ADD [Profit] FLOAT
UPDATE [AdventureWorks Product Lookup]
SET [Profit] = (ProductPrice - ProductCost)
SELECT *
FROM [AdventureWorks Product Lookup]
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Cleaning up the Customer Lookup table
--removing null values
DELETE
FROM [AdventureWorks Customer Lookup]
WHERE EmailAddress IS NULL
--Creating a column for FullName
ALTER TABLE [AdventureWorks Customer Lookup]
ADD FullName NVARCHAR (50)
UPDATE [AdventureWorks Customer Lookup]
SET FullName = CONCAT(Prefix,' ',FirstName,' ',LastName)
--Creating a column for Age
ALTER TABLE [AdventureWorks Customer Lookup]
ADD Age INT
UPDATE [AdventureWorks Customer Lookup]
SET Age = DATEDIFF(YEAR,Birthdate,'2022-06-30')
--Creating a column for Age_group
ALTER TABLE [AdventureWorks Customer Lookup]
ADD Age_group NVARCHAR (50)
UPDATE [AdventureWorks Customer Lookup]
SET Age_group = CASE WHEN Age < 30 THEN 'Gen Z'
WHEN Age < 44 THEN 'Millennials'
WHEN Age < 59 THEN 'Generation X'
ELSE 'Baby Boomer'
END
--Creating a column for Income_segment
ALTER TABLE [AdventureWorks Customer Lookup]
ADD Income_segment NVARCHAR (50)
UPDATE [AdventureWorks Customer Lookup]
SET Income_segment = CASE WHEN AnnualIncome < 40001 THEN 'Low_Income'
WHEN AnnualIncome < 80001 THEN 'Middle_Income'
WHEN AnnualIncome < 120001 THEN 'High_Income'
ELSE 'High_networth'
END
---------------------------------------------------------------------------------------------------------------------------------------------------------------
--SALES PERFORMANCE ANALYSIS
/*Calculate the year-over-year growth rate of sales revenue.*/
WITH OverallRevenue AS
(SELECT
YEAR(o.OrderDate) AS [Year],
ROUND(SUM(o.OrderQuantity * p.ProductPrice),0) AS Total_revenue,
LAG (ROUND(SUM(o.OrderQuantity * p.ProductPrice),0)) OVER (ORDER BY YEAR(o.OrderDate)) AS Previous_year_revenue
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] P ON o.ProductKey = p.ProductKey
GROUP BY YEAR(o.OrderDate)
)
SELECT [Year],Total_revenue,
CONCAT (ROUND(SUM ((Total_revenue - Previous_year_revenue)/ NULLIF (Previous_year_revenue,0)) * 100,2),'%') AS Growth_rate
FROM OverallRevenue
GROUP BY [Year],Total_revenue
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Calculate the total revenue generated by each product category.*/
SELECT c.CategoryName, ROUND(SUM(o.OrderQuantity * p.ProductPrice),0) AS Total_revenue
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Product Subcategories Lookup] s ON p.ProductSubcategoryKey = s.ProductSubcategoryKey
LEFT JOIN [AdventureWorks Product Categories Lookup] c ON s.ProductCategoryKey = c.ProductCategoryKey
GROUP BY c.CategoryName
ORDER BY 2 DESC
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Determine the top 10 customers based on their total purchase amount.*/
SELECT TOP 10 cl.FullName, ROUND(SUM(o.OrderQuantity * p.ProductPrice),0) AS Total_revenue
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Customer Lookup] cl ON o.CustomerKey = cl.CustomerKey
GROUP BY cl.FullName
ORDER BY 2 DESC
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Identify the 5 most profitable product subcategory.*/
SELECT TOP 5 s.SubcategoryName, ROUND(SUM(o.OrderQuantity * p.Profit),0) AS Total_profit
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Product Subcategories Lookup] s ON p.ProductSubcategoryKey = s.ProductSubcategoryKey
GROUP BY s.SubcategoryName
ORDER BY 2 DESC
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Identify the seasonality of sales by analyzing monthly sales trends.*/
SELECT MONTH(o.OrderDate) AS MonthId, DATENAME(MONTH,o.OrderDate) AS [Month], ROUND(SUM(o.OrderQuantity * p.ProductPrice),0) AS Total_revenue
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
GROUP BY MONTH(o.OrderDate), DATENAME(MONTH,o.OrderDate)
ORDER BY 1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Analyze the trend of sales profit over the years for each product category.*/
SELECT c.CategoryName,
ROUND(SUM(CASE WHEN YEAR(o.OrderDate) = 2020 THEN (o.OrderQuantity * p.Profit) ELSE 0 END),0) AS [2020],
ROUND(SUM(CASE WHEN YEAR(o.OrderDate) = 2021 THEN (o.OrderQuantity * p.Profit) ELSE 0 END),0) AS [2021],
ROUND(SUM(CASE WHEN YEAR(o.OrderDate) = 2022 THEN (o.OrderQuantity * p.Profit) ELSE 0 END),0) AS [2022]
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Product Subcategories Lookup] s ON p.ProductSubcategoryKey = s.ProductSubcategoryKey
LEFT JOIN [AdventureWorks Product Categories Lookup] c ON s.ProductCategoryKey = c.ProductCategoryKey
GROUP BY c.CategoryName
ORDER BY 1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Calculate the total revenue generated by each Continent.*/
SELECT t.Continent, ROUND(SUM(o.OrderQuantity * p.ProductPrice),0) AS Total_revenue
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Territory Lookup] t ON o.TerritoryKey = t.SalesTerritoryKey
GROUP BY t.Continent
ORDER BY 2 DESC
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Analyze the trend of sales revenue over the years for each Country.*/
SELECT t.Country,
ROUND(SUM(CASE WHEN YEAR(o.OrderDate) = 2020 THEN (o.OrderQuantity * p.ProductPrice) ELSE 0 END),0) AS [2020],
ROUND(SUM(CASE WHEN YEAR(o.OrderDate) = 2021 THEN (o.OrderQuantity * p.ProductPrice) ELSE 0 END),0) AS [2021],
ROUND(SUM(CASE WHEN YEAR(o.OrderDate) = 2022 THEN (o.OrderQuantity * p.ProductPrice) ELSE 0 END),0) AS [2022]
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Territory Lookup] t ON o.TerritoryKey = t.SalesTerritoryKey
GROUP BY t.Country
ORDER BY 1
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--PRODUCT PERFORMANCE ANALYSIS
/*Analyze the return rate for each product category.*/
WITH CategoryOrderedquantity AS
(SELECT c.CategoryName, SUM(o.OrderQuantity) AS Total_quantity
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Product Subcategories Lookup] s ON p.ProductSubcategoryKey = s.ProductSubcategoryKey
LEFT JOIN [AdventureWorks Product Categories Lookup] c ON s.ProductCategoryKey = c.ProductCategoryKey
GROUP BY c.CategoryName),
CategoryReturnedquantity AS
(SELECT c.CategoryName,SUM(r.ReturnQuantity) AS Returned_quantity
FROM [AdventureWorks Returns Data] r
LEFT JOIN [AdventureWorks Product Lookup] p ON r.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Product Subcategories Lookup] s ON p.ProductSubcategoryKey = s.ProductSubcategoryKey
LEFT JOIN [AdventureWorks Product Categories Lookup] c ON s.ProductCategoryKey = c.ProductCategoryKey
GROUP BY c.CategoryName)
SELECT co.CategoryName,
CONCAT (CAST (ROUND ((Returned_quantity *1.0)/(Total_quantity)*100,2) AS DECIMAL(10, 2)),'%') AS Return_rate
FROM CategoryOrderedquantity co
JOIN CategoryReturnedquantity cr ON co.CategoryName = cr.CategoryName
ORDER BY 1
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Identify the top-selling products in each product subcategory.*/
WITH ProductRank AS
(SELECT s.SubcategoryName,p.ProductName, ROUND(SUM(o.OrderQuantity * p.ProductPrice),0) AS Total_revenue,
DENSE_RANK () OVER (PARTITION BY SubcategoryName ORDER BY ROUND(SUM(o.OrderQuantity * p.ProductPrice),0) DESC) AS Product_rank
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
LEFT JOIN [AdventureWorks Product Subcategories Lookup] s ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
GROUP BY s.SubcategoryName,p.ProductName)
SELECT SubcategoryName,ProductName, Total_revenue, Product_rank
FROM ProductRank
WHERE Product_rank = 1
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Identify Top 10 cross-selling opportunities by analyzing frequently co-purchased products.*/
WITH Cross_sell AS
(SELECT o1.ProductKey AS Product1,
o2.ProductKey AS Product2,
COUNT (*) AS Frequency
FROM Overall_sales o1
JOIN Overall_sales o2 ON o1.OrderNumber = o2.OrderNumber
AND o1.ProductKey < o2.ProductKey
GROUP BY o1.ProductKey,o2.ProductKey)
SELECT TOP 10 p1.ProductName, p2.ProductName, cs.Frequency
FROM Cross_sell cs
LEFT JOIN [AdventureWorks Product Lookup] p1 ON p1.ProductKey = cs.Product1
LEFT JOIN [AdventureWorks Product Lookup] p2 ON p2.ProductKey = cs.Product2
ORDER BY 3 DESC
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Identify slow-moving products.*/
SELECT TOP 5 p.ProductName, AVG(DATEDIFF(DAY,o.StockDate,o.OrderDate)) AS AvgDaysBeforeSale
FROM Overall_sales o
LEFT JOIN [AdventureWorks Product Lookup] p ON o.ProductKey = p.ProductKey
GROUP BY P.ProductName
ORDER BY 2 DESC
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Analyze the Monthly distribution of order quantities to identify bulk purchase trends.*/
SELECT MONTH(OrderDate) AS MonthId, DATENAME(MONTH,OrderDate) AS [Month],
SUM(OrderQuantity) As Total_quantities
FROM Overall_sales
GROUP BY MONTH(OrderDate), DATENAME(MONTH,OrderDate)
ORDER BY 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--CUSTOMER DEMOGRAPHICS
/*Identify the most profitable customer Income segments based on purchase history.*/
SELECT cl.Income_segment, ROUND(SUM(o.OrderQuantity * p.Profit),0) AS Total_profit
FROM [AdventureWorks Customer Lookup] cl
LEFT JOIN Overall_sales o ON o.CustomerKey = cl.CustomerKey
LEFT JOIN [AdventureWorks Product Lookup] p ON p.ProductKey = o.ProductKey
GROUP BY cl.Income_segment
ORDER BY 2 DESC
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Analyze the distribution of Customer Age groups per region*/
SELECT t.Region,
SUM (CASE WHEN cl.Age_group = 'Gen Z' THEN 1 ELSE 0 END) AS [Gen Z],
SUM (CASE WHEN cl.Age_group = 'Millennials' THEN 1 ELSE 0 END) AS [Millennials],
SUM (CASE WHEN cl.Age_group = 'Baby Boomer' THEN 1 ELSE 0 END) AS [Baby Boomer],
SUM (CASE WHEN cl.Age_group = 'Generation X' THEN 1 ELSE 0 END) AS [Generation X]
FROM [AdventureWorks Customer Lookup] cl
RIGHT JOIN Overall_sales o ON o.CustomerKey = cl.CustomerKey
LEFT JOIN [AdventureWorks Territory Lookup] t ON t.SalesTerritoryKey = o.TerritoryKey
GROUP BY t.Region;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Calculate customer churn analysis distribution.*/
IF OBJECT_ID('tempdb..#CustomerChurnSegments', 'U') IS NOT NULL
BEGIN
DROP TABLE #CustomerChurnSegments;
END
CREATE TABLE #CustomerChurnSegments
(CustomerKey NVARCHAR (50), CustomerName NVARCHAR (50),
LastPurchaseDate DATE,DaysinceLastpurchase INT,Customer_segment NVARCHAR (50));
WITH LastPurchaseDate AS
(SELECT DISTINCT cl.FullName, cl.CustomerKey,
MAX(o.OrderDate) OVER (PARTITION BY cl.FullName) AS LastPurchaseDate
FROM Overall_sales o
RIGHT JOIN [AdventureWorks Customer Lookup] cl ON cl.CustomerKey = o.CustomerKey
GROUP BY cl.FullName,o.OrderDate,cl.CustomerKey
)
INSERT INTO #CustomerChurnSegments (CustomerKey, CustomerName, LastPurchaseDate,DaysinceLastpurchase,Customer_segment)
SELECT CustomerKey, FullName, LastPurchaseDate,
DATEDIFF(DAY,LastPurchaseDate,'2022-06-30'),
CASE
WHEN DATEDIFF(DAY,LastPurchaseDate,'2022-06-30') <= 90 THEN 'Active'
WHEN DATEDIFF(DAY,LastPurchaseDate,'2022-06-30') <= 180 THEN 'Inactive'
WHEN DATEDIFF(DAY,LastPurchaseDate,'2022-06-30') <= 270 THEN 'Churn'
ELSE 'Lost'
END
FROM LastPurchaseDate
SELECT Customer_segment, COUNT (DISTINCT CustomerKey) AS Customers
FROM #CustomerChurnSegments
GROUP BY Customer_segment
ORDER BY 2 DESC
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Identify the Educational level with the highest purchased units.*/
SELECT cl.EducationLevel,SUM(o.OrderQuantity) AS Ordered_quantity
FROM Overall_sales o
LEFT JOIN [AdventureWorks Customer Lookup] cl ON cl.CustomerKey = o.CustomerKey
GROUP BY cl.EducationLevel;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*Calculate the Order distribution per customer occupation per weekday.*/
SELECT CASE WHEN DATEPART(WEEKDAY,o.Orderdate) = 1 THEN 7 ELSE DATEPART(WEEKDAY,o.Orderdate) - 1 END AS DayId,
DATENAME(WEEKDAY, o.OrderDate) AS [Weekday],
SUM(CASE WHEN cl.Occupation = 'Clerical' THEN 1 ELSE 0 END) AS [Clerical],
SUM(CASE WHEN cl.Occupation = 'Management' THEN 1 ELSE 0 END) AS [Management],
SUM(CASE WHEN cl.Occupation = 'Professional' THEN 1 ELSE 0 END) AS [Professional],
SUM(CASE WHEN cl.Occupation = 'Skilled Manual' THEN 1 ELSE 0 END) AS [Skilled Manual]
FROM Overall_sales o
LEFT JOIN [AdventureWorks Customer Lookup] cl ON cl.CustomerKey = o.CustomerKey
GROUP BY DATEPART(WEEKDAY,o.Orderdate), DATENAME(WEEKDAY, o.OrderDate)
ORDER BY 1