-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.sql
99 lines (87 loc) · 1.68 KB
/
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
-- Задание 1
-- Запрос a
SELECT
monthname(create_date) AS months,
COUNT(index_lead) AS amount
FROM
test_sql
GROUP BY
months;
-- Запрос b
SELECT
monthname(create_date) AS months,
SUM(index_sum) AS total_sum
FROM
test_sql
GROUP BY
months;
-- Запрос c
SELECT
monthname(create_date) AS months,
CONCAT(ROUND((SUM(index_sum) / (SELECT SUM(index_sum) FROM test_sql)) * 100.0, 2), '%') AS share_total_sum
FROM
test_sql
GROUP BY
months;
-- Одним запросом
SELECT
monthname(create_date) AS months,
COUNT(index_lead) AS amount,
SUM(index_sum) AS total_sum,
CONCAT(ROUND((SUM(index_sum) / (SELECT SUM(index_sum) FROM test_sql)) * 100.0, 2), '%') AS share_total_sum
FROM
test_sql
GROUP BY
months;
-- Задание 2
WITH ind (
st_ord, type_product_name, product_infosource1,
create_date, index_lead, index_issue,
index_sum, indicator
) AS (
SELECT
*,
CASE WHEN index_sum > 2000000
AND MONTH(create_date) = 3
AND YEAR(create_date) = 2020 THEN 1 WHEN index_sum < 2000000
AND index_sum > 1000000
AND MONTH(create_date) = 3
AND YEAR(create_date) = 2020 THEN 2 ELSE -1 END
FROM
test_sql
);
SELECT * FROM ind WHERE indicator <> -1;
-- Задание 3
SELECT
t1.product_infosource1
FROM
(
SELECT
product_infosource1,
COUNT(*) AS total_amount
FROM
test_sql
GROUP BY
product_infosource1
) AS t1
ORDER BY
total_amount DESC
LIMIT
1;
-- Задание 4
-- Запрос a
SELECT
t1.numbers
FROM
table_num t1
JOIN table_num t2 ON t1.numbers = t2.numbers
AND t1.numbers > 0;
-- Запрос b*
SELECT
numbers
FROM
table_num
GROUP BY
numbers
HAVING
numbers > 0;