Repository navigation
Expand file tree
/
Copy pathPizza_Sales_SQL_Project_Final_File.sql
More file actions
188 lines (159 loc) · 4.71 KB
/
Copy pathPizza_Sales_SQL_Project_Final_File.sql
File metadata and controls
188 lines (159 loc) · 4.71 KB
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
-- SQL peoject for pizza_sales--
SELECT * FROM pizzahut;
create table orders (
order_id int not null,
order_date date not null,
order_time time not null,
primary key(order_id) );
select * from pizzahut.orders;
create table order_details (
order_details_id int not null,
order_id int not null,
pizza_id text not null,
quantity int not null,
primary key(order_details_id) );
select * from pizzahut.order_details;
-- Solve the Questions.
--(Basic)
-- 1. Retrieve the total number of orders placed.
select count(order_id) as total_order from orders;
-- 2. Calculate the total revenue generated from pizza sales. --join
SELECT
ROUND(sum(order_details.quantity * pizzas.price),
2) AS total_sales
FROM
order_details
JOIN
pizzas ON pizzas.pizza_id = order_details.pizza_id
-- 3. Identify the highest-priced pizza.
SELECT
pizza_types.name, pizzas.price
FROM
pizza_types
JOIN
pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id
ORDER BY pizzas.price DESC
LIMIT 1;
-- 4. Identify the most common pizza size ordered.
SELECT
pizzas.size,
COUNT(order_details.order_details_id) AS order_count
FROM
pizzas
JOIN
order_details ON pizzas.pizza_id = order_details.pizza_id
GROUP BY pizzas.size
ORDER BY order_count DESC;
-- 5. List the top 5 most ordered pizza types along with their quantities.
SELECT
pizza_types.name, SUM(order_details.quantity) AS quantity
FROM
pizza_types
JOIN
pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id
JOIN
order_details ON order_details.pizza_id = pizzas.pizza_id
GROUP BY pizza_types.name
ORDER BY quantity DESC
LIMIT 5;
--(Intermediate)
-- 1. Join the necessary tables to find the total quantity of each pizza category ordered.
SELECT
pizza_types.category,
SUM(order_details.quantity) AS quantity
FROM
pizza_types
JOIN
pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id
JOIN
order_details ON order_details.pizza_id = pizzas.pizza_id
GROUP BY pizza_types.category
ORDER BY quantity DESC;
-- 2. Determine the distribution of orders by hour of the day.
SELECT
HOUR(order_time), COUNT(order_id) AS order_count
FROM
orders
GROUP BY HOUR(order_time);
-- 3. Join relevant tables to find the category-wise distribution of pizzas.
SELECT
category, COUNT(name)
FROM
pizza_types
GROUP BY category
-- 4. Group the orders by date and calculate the average number of pizzas ordered per day.
-- Subquery based
SELECT
ROUND(AVG(quantity), 0) as avg_pizza_ordered_per_day
FROM
(SELECT
orders.order_date, SUM(order_details.quantity) AS quantity
FROM
orders
JOIN order_details ON orders.order_id = order_details.order_id
GROUP BY orders.order_date) AS order_quantity;
-- 5. Determine the top 3 most ordered pizza types based on revenue.
SELECT
pizza_types.name,
SUM(order_details.quantity * pizzas.price) AS revenue
FROM
pizza_types
JOIN
pizzas ON pizzas.pizza_type_id = pizza_types.pizza_type_id
JOIN
order_details ON order_details.pizza_id = pizzas.pizza_id
GROUP BY pizza_types.name
ORDER BY revenue DESC
LIMIT 3;
-- (Advanced)
-- 1. Calculate the percentage contribution of each pizza type to total revenue.
SELECT
pizza_types.category,
ROUND(
(SUM(order_details.quantity * pizzas.price) /
(
SELECT SUM(order_details.quantity * pizzas.price)
FROM order_details
JOIN pizzas ON pizzas.pizza_id = order_details.pizza_id
)) * 100,
2) AS revenue_percentage
FROM
pizza_types
JOIN
pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id
JOIN
order_details ON order_details.pizza_id = pizzas.pizza_id
GROUP BY
pizza_types.category
ORDER BY
revenue_percentage DESC;
-- 2. Analyze the cumulative revenue generated over time.
select order_date,
sum(revanue) over(order by order_date) as cum_revanue
from
(select orders.order_date,
sum(order_details.quantity*pizzas.price) as revanue
from order_details join pizzas
on order_details.pizza_id = pizzas.pizza_id
join orders
on orders.order_id = order_details.order_id
group by orders.order_date) as sales;
-- 3. Determine the top 3 most ordered pizza types based on revenue for each pizza category.
select name, revenue from
(select category, name, revenue,
rank() over(partition by category order by revenue desc) as rn
from
(SELECT
pizza_types.category,
pizza_types.name,
SUM(order_details.quantity * pizzas.price) AS revenue
FROM
pizza_types
JOIN
pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id
JOIN
order_details ON order_details.pizza_id = pizzas.pizza_id
GROUP BY
pizza_types.category,
pizza_types.name) as a) as b
where rn<=3;