--books--
DROP TABLE IF EXISTS books cascade;
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO books (name) VALUES
('Война и мир'),
('Преступление и наказание'),
('Мастер и Маргарита'),
('Тихий дон'),
('Бойцовский Клуб');

--book_reg--
DROP TABLE IF EXISTS book_reg cascade;
CREATE TABLE book_reg (
    id INT PRIMARY KEY,
    DATE DATE NOT NULL
);

INSERT INTO book_reg (id, DATE) VALUES
(1, TO_DATE('01.05.2006', 'DD.MM.YYYY')),
(3, TO_DATE('05.07.2004', 'DD.MM.YYYY')),
(5, TO_DATE('30.09.2015', 'DD.MM.YYYY'));

--genres--
DROP TABLE IF EXISTS genres cascade;
CREATE TABLE genres (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO genres (name) VALUES
('Русская классика'),
('Зарубежные романы'),
('Драма');

--book_genres--
DROP TABLE IF EXISTS book_genres cascade;
CREATE TABLE book_genres (
    book_id INT NOT NULL,
    genre_id INT NOT NULL,
    UNIQUE (book_id, genre_id),
    FOREIGN KEY (book_id) REFERENCES books (id),
    FOREIGN KEY (genre_id) REFERENCES genres (id)
);

INSERT INTO book_genres (book_id, genre_id) VALUES
(1, 1),
(1, 3),
(2, 1),
(3, 1),
(4, 1),
(5, 2),
(5, 3);

--проверки
SELECT * FROM books;
SELECT * FROM book_reg;
SELECT * FROM genres;
SELECT * FROM book_genres;

--запрос
SELECT g.name AS genre_name, COUNT(DISTINCT b.id) AS book_count
FROM book_reg b_r
JOIN books b ON b_r.id = b.id
JOIN book_genres b_g ON b.id = b_g.book_id
JOIN genres g ON b_g.genre_id = g.id
WHERE b_r.DATE > TO_DATE('12.04.2003', 'DD.MM.YYYY')
GROUP BY g.name
ORDER BY book_count DESC;