Final_Report_Result_Manmit_Kumar_Mishra.xlsx

	B	C	D
1	1. Which is the most loss making category in the East region?
2
3	CODE:-
4
5	SELECT
6	N.Category, N.Total_Profit
7	FROM
8	(SELECT Category, Profit AS Total_Profit, RANK()OVER(ORDER BY Profit ASC) AS RANKS
9	FROM orders
10	WHERE Region = 'East') AS N
11	WHERE N.RANKS = 1
12
13	RESULT:-
14
15	Category Total_Profit
16	Technology	-6599.978
17	OR
18
19
20	CODE:-
21
22	SELECT
23	N.Category, N.Total_Profit
24	FROM
25	(SELECT
26	Category, ROUND(SUM(Profit), 1) AS Total_Profit, RANK()OVER(ORDER BY Profit ASC) AS RANKS
27	FROM orders
28	WHERE Region = 'East'
29	GROUP BY Category) AS N
30	WHERE N.RANKS = 1
31
32	RESULT:-
33
34	Category Total_Profit
35	Office Supplies 40730.6
36
37
38
39	2. Give me the top 3 product ids by most returns
40	2. Give me the top 3 product ids by most returns
41	CODE:-
42
43	SELECT
44	N.Product_ID
45	FROM
46	(SELECT
47	Product_ID, COUNT(Returned) AS Total_returns, ROW_NUMBER()OVER(ORDER BY COUNT(Returned) DESC) AS ROW_NO
48	FROM orders as O
49	JOIN returns as R ON R.Order_ID = O.Order_ID
50	GROUP BY O.Product_ID
51	ORDER BY COUNT(Returned) DESC) AS N
52	WHERE N.ROW_NO IN(1,2,3)
53
54	RESULT:- PRODUCT_ID
55
56	OFF-PA-10001970
57	OFF-BI-10002215
58	FUR-CH-10003968
59
60
61
62	3. In which city the most number of returns are being recorded
63
64	CODE:-
65
66
67	SELECT
68	N.City
69	FROM
70	(SELECT
71	City, COUNT(Returned) AS Total_returns, RANK()OVER(ORDER BY COUNT(Returned) DESC) AS RANK_NO
72	FROM orders as O
73	JOIN returns as R ON R.Order_ID = O.Order_ID
74	JOIN location AS L ON L.Postal_Code = O.Postal_Code
75	GROUP BY City
76	ORDER BY COUNT(Returned) DESC) AS N
77	WHERE N.RANK_NO = 1
78
79	RESULT :- CITY
80
81	Los Angeles
82
83
84
85	4. Find the relationship between days between order date , ship date and profit
86
87	CODE:-
88	COMMENT :- (DATE IS IN [DD-MM-YYYY] Format)
89	SELECT
90	ABS(Ship_Date - Order_Date) AS DAYS, SUM(Profit) AS Total_profit, RANK()OVER(ORDER BY SUM(Profit) DESC)AS Profit_Ranking
91	FROM orders
92	GROUP BY ABS(Ship_Date - Order_Date)
93	ORDER BY SUM(Profit) desc, ABS(Ship_Date - Order_Date)
94
95	RESULT:-
96
97	Days _Taken	Total_Profit	Profit_Ranking
98	4	60480.6537	1
99	5	52639.399	2
100	2	48498.9591	3