-
Notifications
You must be signed in to change notification settings - Fork 0
/
Intermediate Queries.sql
50 lines (43 loc) · 1.34 KB
/
Intermediate 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
-- 1. Join the necessary tables to find the total quantity of each pizza category ordered.
use pizza_hub;
SELECT
pt.category, SUM(od.quantity) AS total_quantity
FROM
pizzas p
JOIN
order_details od ON p.pizza_id = od.pizza_id
JOIN
pizza_types pt ON p.pizza_type_id = pt.pizza_type_id
GROUP BY pt.category
ORDER BY total_quantity DESC;
-- 2. Determine the distribution of orders by hour of the day.
SELECT
HOUR(time) AS hour, COUNT(order_id) AS order_count
FROM
orders
GROUP BY hour;
-- 3.Join relevant tables to find the category-wise distribution of pizzas.
SELECT category,COUNT(name)
FROM pizza_types
GROUP BY category;
-- Group the orders by date and calculate the average number of pizzas ordered per day.
SELECT
ROUND(AVG(quantity))
FROM
(SELECT
orders.date, SUM(order_details.quantity) AS quantity
FROM
orders
INNER JOIN order_details ON orders.order_id = order_details.order_id
GROUP BY orders.date) AS order_quantity;
-- Determine the top 3 most ordered pizza types based on revenue.
SELECT
pt.name, SUM(p.price * od.quantity) AS revenue
FROM
pizza_types AS pt
INNER JOIN
pizzas AS p ON pt.pizza_type_id = p.pizza_type_id
JOIN
order_details AS od ON p.pizza_id = od.pizza_id
GROUP BY pt.name
ORDER BY revenue DESC LIMIT 3;