Advanced-MySQL-Course/2.2 Using CTEs.sql at main · AnalystBuilder/Advanced-MySQL-Course · GitHub

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
-- Using Common Table Expressions (CTEs)

SELECT *
FROM bakery.customer_orders;


SELECT product_id, SUM(order_total), SUM(tip), COUNT(tip)
FROM bakery.customer_orders
WHERE tip != 0
GROUP BY product_id;


WITH CTE_Example AS
(
SELECT product_id, SUM(order_total), SUM(tip), COUNT(tip)
FROM bakery.customer_orders
WHERE tip != 0
GROUP BY product_id
)


SELECT *
FROM CTE_Example
WHERE `SUM(tip)` > 3;

SELECT *
FROM CTE_Example
WHERE `SUM(tip)` > 3;


WITH CTE_Example(product_id, sum_order_total,sum_tip,count_tip) AS
(
SELECT product_id, SUM(order_total), SUM(tip), COUNT(tip)
FROM bakery.customer_orders
WHERE tip != 0
GROUP BY product_id
)

SELECT product_id, ROUND(sum_tip/count_tip,2)
FROM CTE_Example;

SELECT product_id, ROUND(`SUM(tip)`/`COUNT(tip)`,2)
FROM (SELECT product_id, SUM(order_total), SUM(tip), COUNT(tip)
FROM bakery.customer_orders
WHERE tip != 0
GROUP BY product_id) Subquery_Example
GROUP BY product_id;


WITH CTE_Example(product_id, sum_order_total,sum_tip,count_tip) AS
(
SELECT product_id, SUM(order_total), SUM(tip), COUNT(tip)
FROM bakery.customer_orders
WHERE tip != 0
GROUP BY product_id
),
CTE_Example2 AS (
SELECT product_id, SUM(order_total), SUM(tip), COUNT(tip)
FROM bakery.customer_orders
GROUP BY product_id
)
SELECT *
FROM CTE_Example cte1
RIGHT JOIN CTE_Example2 cte2
	ON cte1.product_id = cte2.product_id;