· 4 years ago · Aug 30, 2021, 12:52 PM
1CREATE TABLE IF NOT EXISTS `files` (
2 `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
3 `id_user` INT(11) DEFAULT NULL,
4 `flag_converted` TINYINT(1) DEFAULT '0' COMMENT '0 - not converted, 1 - converted',
5 `flag_error` TINYINT(1) DEFAULT '0' COMMENT '0 - no errors, 1 - error occurred',
6 `rating` INT(11) UNSIGNED DEFAULT NULL COMMENT '1-5',
7 PRIMARY KEY (`id`)
8) DEFAULT CHARSET=utf8;
9
10
11INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 0, 0, NULL);
12INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 1, 0, 5);
13INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 1, 0, 5);
14INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 0, 1, 2);
15INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 0, 0, NULL);
16INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 1, 0, 5);
17INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 0, 1, 2);
18INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (2, 0, 0, NULL);
19INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (3, 1, 0, 5);
20INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (3, 1, 0, 5);
21INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
22INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
23INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
24INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
25
26
27
28# 4. Необходимо сформировать SQL запрос к таблице files (schema: https://pastebin.com/j4hyDwhh),
29# который выводит количество файлов по успешности конвертирования и наличию хорошей оценки у
30# зарегистрированных/незарегистрированных пользователей. Формат вывода: `user_type` ('Visitor', если id_user = NULL;
31# 'User' в противном случае), `flag_converted`, `is_best_rating` ('1' - если rating = 5; '0' в противном случае), `count`
32# (количество записей). Результаты нужно отсортировать по `count` и `is_best_rating` в обратном порядке.
33# И выводить только группы, где `count` больше 1.
34
35SELECT IF(id_user IS NULL, 'Visitor', 'User') user_type,
36 flag_converted,
37 count(id) count,
38 sum(IF(files.rating = 5, 1, 0)) is_best_rating
39FROM files
40GROUP BY user_type, flag_converted
41ORDER BY count desc, is_best_rating desc;