· 4 years ago · May 26, 2021, 12:22 PM
1SQL:
2```
3SELECT * FROM (
4 SELECT
5 IF (id_user IS NOT NULL, 'User', 'Visitor') AS user_type,
6 flag_converted,
7 IF (rating = 5, 1, 0) AS is_best_rating,
8 COUNT(*) AS count
9 FROM files
10 GROUP BY user_type, flag_converted, is_best_rating
11 HAVING count > 0
12) as r
13ORDER BY count DESC, is_best_rating DESC;
14```
15
16CREATE TABLE IF NOT EXISTS `files` (
17 `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
18 `id_user` INT(11) DEFAULT NULL,
19 `flag_converted` TINYINT(1) DEFAULT '0' COMMENT '0 - not converted, 1 - converted',
20 `flag_error` TINYINT(1) DEFAULT '0' COMMENT '0 - no errors, 1 - error occurred',
21 `rating` INT(11) UNSIGNED DEFAULT NULL COMMENT '1-5',
22 PRIMARY KEY (`id`)
23) DEFAULT CHARSET=utf8;
24
25
26INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 0, 0, NULL);
27INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 1, 0, 5);
28INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 1, 0, 5);
29INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 0, 1, 2);
30INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 0, 0, NULL);
31INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 1, 0, 5);
32INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 0, 1, 2);
33INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (2, 0, 0, NULL);
34INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (3, 1, 0, 5);
35INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (3, 1, 0, 5);
36INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
37INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
38INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
39INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);