-
Notifications
You must be signed in to change notification settings - Fork 1
/
superstore_queries.sql
96 lines (86 loc) · 2.82 KB
/
superstore_queries.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
-- TOTAL SALES, QUANTITY SOLD, PROFIT BY CUSTOMER_SEGMENT
SELECT CUSTOMER_SEGMENT,
ROUND(SUM(SALES),2) AS TOTAL_SALES,
SUM(QUANTITY) AS TOTAL_QUANTITY_SOLD,
ROUND(SUM(PROFIT),2) AS TOTAL_PROFIT
FROM SALES_FACT F INNER JOIN CUSTOMERS_DIM C
ON C.CUSTOMER_ID = F.CUSTOMER_ID
INNER JOIN ORDERS_DIM O ON O.ORDER_ID = F.ORDER_ID
WHERE O.RETURNED = 'No'
GROUP BY CUSTOMER_SEGMENT;
-- TOTAL SALES, QUANTITY SOLD, PROFIT BY CATEGORY
SELECT CATEGORY,
ROUND(SUM(SALES),2) AS TOTAL_SALES,
SUM(QUANTITY) AS TOTAL_QUANTITY_SOLD,
ROUND(SUM(PROFIT),2) AS TOTAL_PROFIT
FROM SALES_FACT F INNER JOIN PRODUCTS_DIM P
ON P.PRODUCT_ID = F.PRODUCT_ID
INNER JOIN ORDERS_DIM O ON O.ORDER_ID = F.ORDER_ID
WHERE O.RETURNED = 'No'
GROUP BY CATEGORY;
-- TOTAL SALES, QUANTITY SOLD, PROFIT BY SUB_CATEGORY
SELECT SUB_CATEGORY,
ROUND(SUM(SALES),2) AS TOTAL_SALES,
SUM(QUANTITY) AS TOTAL_QUANTITY_SOLD,
ROUND(SUM(PROFIT),2) AS TOTAL_PROFIT
FROM SALES_FACT F INNER JOIN PRODUCTS_DIM P
ON P.PRODUCT_ID = F.PRODUCT_ID
INNER JOIN ORDERS_DIM O ON O.ORDER_ID = F.ORDER_ID
WHERE O.RETURNED = 'No'
GROUP BY SUB_CATEGORY;
-- UNPROFITABLE SUBCATEGORIES
SELECT SUB_CATEGORY,
ROUND(SUM(PROFIT),2) AS TOTAL_PROFIT
FROM SALES_FACT F INNER JOIN PRODUCTS_DIM P
ON P.PRODUCT_ID = F.PRODUCT_ID
INNER JOIN ORDERS_DIM O ON O.ORDER_ID = F.ORDER_ID
WHERE O.RETURNED = 'No'
GROUP BY SUB_CATEGORY
HAVING SUM(PROFIT) < 0
ORDER BY 2;
-- TOP 10 SELLING PRODUCTS
SELECT PRODUCT_NAME,
SUM(QUANTITY) AS TOTAL_QUANTITY
FROM SALES_FACT F INNER JOIN PRODUCTS_DIM P
ON P.PRODUCT_ID = F.PRODUCT_ID
INNER JOIN ORDERS_DIM O ON O.ORDER_ID = F.ORDER_ID
WHERE O.RETURNED = 'No'
GROUP BY PRODUCT_NAME
ORDER BY 2 DESC
LIMIT 10;
-- MOST PREFERRED SHIPPING MODE
SELECT SHIP_MODE, COUNT(ORDER_ID) AS NO_OF_ORDERS
FROM ORDERS_DIM
GROUP BY SHIP_MODE
ORDER BY 2 DESC;
-- SAME-DAY ORDERS THAT WERE NOT SHIPPED ON THE SAME DAY, i.e., LATE SAME-DAY ORDERS
SELECT ORDER_ID, ORDER_DATE, SHIP_DATE, SHIP_MODE
FROM ORDERS_DIM
WHERE ORDER_DATE <> SHIP_DATE
and SHIP_MODE = 'Same Day';
-- STATES WITH THE MOST SALES
SELECT C.STATE, ROUND(SUM(F.SALES),2) AS TOTAL_SALES
FROM CUSTOMERS_DIM C INNER JOIN SALES_FACT F
ON C.CUSTOMER_ID = F.CUSTOMER_ID
GROUP BY C.STATE
ORDER BY 2 DESC;
-- CITIES WITH THE MOST SALES
SELECT C.CITY, ROUND(SUM(F.SALES),2) AS TOTAL_SALES
FROM CUSTOMERS_DIM C INNER JOIN SALES_FACT F
ON C.CUSTOMER_ID = F.CUSTOMER_ID
GROUP BY C.CITY
ORDER BY 2 DESC
LIMIT 10;
-- TOP 10 DAYS WITH THE MOST SALES
SELECT O.ORDER_DATE, ROUND(SUM(F.SALES),2) AS TOTAL_SALES
FROM ORDERS_DIM O INNER JOIN SALES_FACT F
ON F.ORDER_ID = O.ORDER_ID
GROUP BY O.ORDER_DATE
ORDER BY 2 DESC
LIMIT 10;
-- SALES ON WEEKDAYS
SELECT DAYNAME(O.ORDER_DATE) AS DAY, ROUND(SUM(F.SALES),2) AS TOTAL_SALES
FROM ORDERS_DIM O INNER JOIN SALES_FACT F
ON F.ORDER_ID = O.ORDER_ID
GROUP BY DAY
ORDER BY 2 DESC;