Test task for Lucky.Online

books.sql

-- Создаём таблицы

SET foreign_key_checks = 0;

DROP TABLE IF EXISTS `book`;

CREATE TABLE `book` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `title` VARCHAR(255) NOT NULL,
        `copies` INT(11) NOT NULL,
        `cover` TINYINT NOT NULL COMMENT '1 - твёрдая обложка, 2 - мягкая обложка',
        PRIMARY KEY (`id`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4;

DROP TABLE IF EXISTS `category`;

CREATE TABLE `category` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL,
        PRIMARY KEY (`id`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4;

DROP TABLE IF EXISTS `book_has_category`;

CREATE TABLE `book_has_category` (
         `book_id` INT(11) NOT NULL,
         `category_id` INT(11) NOT NULL,
         PRIMARY KEY (`book_id`, `category_id`),
         CONSTRAINT fk_book_has_category_book
             FOREIGN KEY (book_id) references book (id),
         CONSTRAINT fk_book_has_category_category
             FOREIGN KEY (category_id) references category (id))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4;

CREATE INDEX fk_book_has_category_book_idx
    ON book_has_category (book_id);

CREATE INDEX fk_book_has_category_category_idx
    ON book_has_category (category_id);

-- SQL 1: Вернуть книги, выпущенные в твёрдой обложке, тиражом 5000 экз., которые относятся больше чем к трём категориям
SELECT book.*
FROM book
INNER JOIN book_has_category bhc on book.id = bhc.book_id
WHERE (cover = 1) AND (copies = 5000)
GROUP BY book.id
HAVING COUNT(bhc.category_id) > 3;

-- SQL 2: Вернуть пары "книга — книга" и количество общих категорий для этих двух книг, если количество общих категорий больше или равно 10.
SELECT bhc1.book_id AS book1, bhc2.book_id AS book2, COUNT(bhc1.category_id) AS category_count
FROM book_has_category bhc1
INNER JOIN book_has_category bhc2 ON (bhc1.book_id < bhc2.book_id) AND (bhc1.category_id = bhc2.category_id)
GROUP BY bhc1.book_id, bhc2.book_id
HAVING category_count >= 10;

visits.sql

-- Создаём таблицу visit

DROP TABLE IF EXISTS `visit`;

CREATE TABLE `visit` (
        `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
        `visited_at` DATETIME NOT NULL,
        `status` TINYINT NOT NULL COMMENT '1 - пришли на сайт, 2 - ушли с сайта')
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4;

-- Заполняем таблицу visit
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:26', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:26', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:27', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:28', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:29', 2);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:30', 2);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:31', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:32', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:33', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:34', 2);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:35', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:36', 1);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:37', 2);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:38', 2);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:39', 2);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:40', 2);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:41', 2);
INSERT INTO visit (visited_at, status) VALUES ('2020-05-09 16:00:41', 2);

-- Делаем процедуру для вычисления количества посетителей
DROP PROCEDURE IF EXISTS GetMaxVisitors;

DELIMITER $$
CREATE PROCEDURE GetMaxVisitors(
IN from_time VARCHAR(255),
IN to_time VARCHAR(255)
)
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
	DECLARE min_level INT DEFAULT 0;
	DECLARE max_level INT DEFAULT 0;
    DECLARE delta INT DEFAULT 0;
    DECLARE level INT DEFAULT 0;

	-- declare cursor for seconds
	DEClARE curSecond
		CURSOR FOR
			SELECT SUM(CASE
                WHEN status = 1 THEN 1
                ELSE -1
            END) AS movement
            FROM visit
            WHERE
                  (visited_at <= CAST(to_time AS DATETIME))
                AND (visited_at >= CAST(from_time AS DATETIME))
            GROUP BY visited_at;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

	OPEN curSecond;

	secondsLoop: LOOP
		FETCH curSecond INTO delta;
		IF finished = 1 THEN
			LEAVE secondsLoop;
		END IF;

		SET level = level + delta;

		IF level < min_level THEN
			SET min_level = level;
		END IF;

		IF level > max_level THEN
			SET max_level = level;
		END IF;
	END LOOP secondsLoop;
	CLOSE curSecond;

    SELECT max_level - min_level AS max_visitors;
END$$
DELIMITER ;

-- Проверяем результат
CALL GetMaxVisitors('2020-05-09 16:00:26', '2020-05-09 16:00:41');
CALL GetMaxVisitors('2020-05-09 16:00:27', '2020-05-09 16:00:40');