-- Создание таблицы Users
CREATE TABLE Users (
userId INT PRIMARY KEY,
date_of_birth DATE
);
-- Создание таблицы Items
CREATE TABLE Items (
itemId INT PRIMARY KEY,
price DECIMAL(10, 2)
);
-- Создание таблицы Purchases
CREATE TABLE Purchases (
purchaseId INT PRIMARY KEY,
userId INT,
itemId INT,
date DATE,
FOREIGN KEY (userId) REFERENCES Users(userId),
FOREIGN KEY (itemId) REFERENCES Items(itemId)
);
INSERT INTO Users (userId, date_of_birth)
VALUES
(1, '1982-10-28'),
(2, '1986-10-28'),
(3, '1978-10-28'),
(4, '2007-10-28'),
(5, '2002-10-28');
-- Заполнение таблицы Items данными
INSERT INTO Items (itemId, price)
VALUES
(101, 10.99),
(102, 15.99),
(103, 20.49),
(104, 5.99),
(105, 12.79);
-- Заполнение таблицы Purchases данными
INSERT INTO Purchases (purchaseId, userId, itemId, date)
VALUES
(201, 1, 101, '2023-01-15'),
(202, 2, 102, '2023-01-16'),
(203, 3, 103, '2023-01-17'),
(204, 4, 104, '2023-01-18'),
(205, 5, 105, '2023-01-19');
SELECT year, month, MAX(monthly_revenue) AS max_revenue
FROM (
SELECT
YEAR(P.date) AS year,
MONTH(P.date) AS month,
U.userId,
SUM(I.price) AS monthly_revenue
FROM Purchases P
JOIN Users U ON P.userId = U.userId
JOIN Items I ON P.itemId = I.itemId
WHERE (YEAR(CURRENT_DATE) - YEAR(U.date_of_birth)) >= 35
GROUP BY YEAR(P.date), MONTH(P.date), U.userId
) AS UserMonthlyPurchases
GROUP BY year, month
ORDER BY max_revenue DESC
LIMIT 1;
select * from Purchases;
select * from Items;
Telegram: DB developers channel