6n8FcTnf

1USE orders;
2
3/* Задание 1
4 * Даны 2 таблицы:
5Таблица клиентов clients, в которой находятся данные по карточному лимиту каждого клиента
6clients
7id_client (primary key) number,
8limit_sum number
9
10transactions
11id_transaction (primary key) number,
12id_client (foreign key) number,
13transaction_date number,
14transaction_time number,
15transaction_sum number
16	1. Написать текст SQL-запроса, выводящего количество транзакций, сумму транзакций, среднюю сумму транзакции и дату и время первой транзакции для каждого клиента
17	2. Найти id пользователей, кот использовали более 70% карточного лимита
18 */
19
20DROP TABLE IF EXISTS clients;
21CREATE TABLE clients (
22  `id_client` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор сроки',
23  `limit_sum` decimal(10,2) COMMENT 'Размер карточного лимита',
24  PRIMARY KEY (`id_client`)
25)  COMMENT 'Карточные лимиты пользователей';
26
27
28DROP TABLE IF EXISTS transactions;
29CREATE TABLE transactions (
30  `id_transaction` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор сроки',
31  `id_client` int unsigned NOT null COMMENT 'Ссылка на ID пользователя',
32  `transaction_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Время создания строки',
33  `transaction_sum` decimal(10,2) COMMENT 'Сумма транзакции',
34  PRIMARY KEY (`id_transaction`)
35)  COMMENT 'Транзакции пользователей';
36
37-- внешний ключ для таблицы транзакций
38ALTER TABLE transactions DROP CONSTRAINT user_id_fk;
39ALTER TABLE transactions
40  ADD CONSTRAINT user_id_fk FOREIGN KEY (id_client) REFERENCES clients(id_client)
41    ON DELETE NO ACTION ON UPDATE NO ACTION;
42   
43
44-- 1. Написать текст SQL-запроса, выводящего количество транзакций, сумму транзакций, среднюю сумму транзакции и дату и время первой транзакции для каждого клиента
45select 
46id_client,
47count(id_transaction) as num_transacrions,
48sum(transaction_sum) as sum_transatctions,
49avg(transaction_sum) as avg_transatctions,
50min(transaction_date) as first_transaction
51from transactions
52group by id_client;
53
54-- 2. Найти id пользователей, кот использовали более 70% карточного лимита
55select 
56c.id_client,
57sum(t.transaction_sum) as sum_transatctions,
58c.limit_sum
59from clients c
60join transactions t ON c.id_client = t.id_client
61group by c.id_client
62having sum(t.transaction_sum) > c.limit_sum * 0.7;
63
64
65/*Задание 2
66 * GA (в DEMO аккаунте Google) :
671. Топ 3 источник/канал , приносящих трафик на сайт в 2020
682. Топ 3 источник/канал , приносящих деньги на сайт в 2020
693. День недели с самым большим трафиком на сайте в 2020
704. Источник/канал привлечения трафика с максимальным ростом в сравнении с 2019 годом*/
71
72/* 1. Топ 3 источник/канал , приносящих трафик на сайт в 2020
731. google / organic - 214 178
742. (direct) / (none) - 211 394
753. analytics.google.com / referral - 23 871
76 */
77
78/* 2. Топ 3 источник/канал , приносящих деньги на сайт в 2020
791. (direct) / (none) - 82 889,17 $(64,34 %)
802. google / organic - 26 591,71 $(20,64 %)
813. (not set) / (not set) - 9 526,70 $(7,40 %)
82 */
83
84/* 3. День недели с самым большим трафиком на сайте в 2020
85Wednesday	106 665(16,47 %) <--- Среда по отчету за 2020 год - день с самым большим трафиком
86Monday	    103 423(15,97 %)
87Thursday	102 101(15,76 %)
88Friday	    90 925(14,04 %)
89Sunday	    69 912(10,79 %)
90Saturday	67 365(10,40 %)
91*/
92
93
94/* 4. Источник/канал привлечения трафика с максимальным ростом в сравнении с 2019 годом
95В натуральном выражении самый польшой прирост был у источник/канал - (direct) / (none)
9601.01.2020 - 31.12.2020	- 211 394
9701.01.2019 - 31.12.2019	- 99 770
98Рост составил - 111624
99В процентном соотношении сложно оценить. Есть источники/каналы с приростом более 1000%, а также с бесконечным приростом, т.к. в 2019г. от них трафик не поступал.
100*/
101