SQLize Online / PHPize Online  /  SQLtest Online

-- create three tables CREATE TABLE IF NOT EXISTS "user"( id bigint PRIMARY KEY, name varchar(255) NOT NULL, regdate timestamp NOT NULL, birthday date NOT NULL ); CREATE TABLE IF NOT EXISTS "book"( id bigint PRIMARY KEY, author varchar(255) NOT NULL, name varchar(255) NOT NULL ); CREATE TABLE IF NOT EXISTS "user_book"( user_id bigint REFERENCES "user", book_id bigint REFERENCES "book", date timestamp NOT NULL, PRIMARY KEY (user_id, book_id) ); -- insert the necessary data into the tables INSERT INTO "user"(id, name, regdate, birthday) VALUES ( '1', 'Григорьев Борис Евсеевич', '2016-06-22 19:10:25-07', '1999-01-08' ), ( '2', 'Силин Аполлон Филиппович', '2016-07-22 19:10:25-07', '1979-10-08' ), ( '3', 'Потапов Иван Васильевич', '2016-08-22 19:10:25-07', '2003-01-08' ), ( '4', 'Тимофеев Евдоким Давидович', '2016-09-22 19:10:25-07', '1969-01-08' ), ( '5', 'Шашкова Анита Станиславовна', '2016-10-22 19:10:25-07', '1989-01-12' ), ( '6', 'Дроздова Анастасия Павловна', '2016-11-22 19:10:25-07', '2000-01-08' ), ( '7', 'Морозова Варвара Матвеевна', '2016-12-22 19:10:25-07', '1954-07-08' ), ( '8', 'Костин Захар Лаврентьевич', '2017-01-22 19:10:25-07', '1976-01-20' ), ( '9', 'Степанов Демьян Дамирович', '2017-02-22 19:10:25-07', '2000-05-08' ); -- show table's content SELECT * FROM "user"; INSERT INTO book(id, author, name) VALUES ('1', 'Михаил Булгаков', 'Мастер и Маргарита'), ('2', 'Рей Брэдбери', '451° по Фаренгейту'), ('3', 'Эрих Мария Ремарк', 'Три товарища'), ('4', 'Даниел Киз', 'Цветы для Элджернона'), ('5', 'Оскар Уайльд', 'Портрет Дориана Грея'), ('6', 'Антуан де Сент-Экзюпери', 'Маленький принц'), ('7', 'Джером Д. Сэлинджер', 'Над пропастью во ржи'), ('8', 'Харпер Ли', 'Убить пересмешника'), ('9', 'Лев Толстой', 'Анна Каренина'); SELECT * FROM "book"; INSERT INTO user_book(user_id, book_id,date) VALUES ('1', '5', '2021-03-22 19:10:25-07'), ('1', '7', '2021-03-01 19:10:25-07'), ('3', '7', '2021-03-15 19:10:25-07'), ('6', '7', '2021-03-30 19:10:25-07'), ('2', '3', '2021-02-05 19:10:25-07'), ('4', '3', '2021-02-10 19:10:25-07'), ('7', '3', '2021-02-11 19:10:25-07'), ('8', '3', '2021-02-14 19:10:25-07'), ('6', '6', '2021-04-10 19:10:25-07'), ('3', '8', '2021-04-14 19:10:25-07'), ('2', '9', '2021-04-18 19:10:25-07'); SELECT * FROM "user_book"; /* FLOOR((DATE(user_book.date)::DATE - "user".birthday::DATE) / 365.25) this expression calculates difference between customer birthday in DATE format and date of the purchase in DATETIME format, "365,25" is used to convert days to years and to account for the leap years */ -- select book name of the specified author (Сэлинджер), purchases for March and average age of the customers SELECT book.name, COUNT(user_book.date) as purchases_qnt, AVG(FLOOR((date(user_book.date)::DATE - "user".birthday::DATE) / 365.25)) as avg_age FROM book JOIN user_book ON user_book.book_id = book.id JOIN "user" ON "user".id = user_book.user_id WHERE book.author like'%Сэлинджер' AND user_book.date<'2021-04-01' GROUP BY book.name; -- select book name of the specified author (Ремарк), purchases for February and average age of the customers SELECT book.name, COUNT(user_book.date) as purchases_qnt, AVG(FLOOR((date(user_book.date)::DATE - "user".birthday::DATE) / 365.25)) as avg_age FROM book JOIN user_book ON user_book.book_id = book.id JOIN "user" ON "user".id = user_book.user_id WHERE book.author like'%Ремарк' AND user_book.date<'2021-03-01' GROUP BY book.name;