Условие

База с тремя таблицами:

Users(userId, age)
Purchases(purchaseId, userId, itemId, date)
Items(itemId, price)

Напишите SQL-запросы:

A. Средняя сумма трат в месяц для:

пользователей 18–25 (включительно);
пользователей 26–35 (включительно).

B. В каком месяце года выручка от пользователей 35+ самая большая?

C. Какой товар даёт наибольший вклад в выручку за последний год?

D. Топ-3 товаров по выручке и их доля в общей выручке за любой год.

Решение

Подготовка: «общий чек на покупку»

Покупка — Purchases строка с одним товаром. Сумма покупки = Items.price (если товар один) — или нужно учесть quantity, но в задаче не указано. Допустим, на одну строку = одна штука.

A. Средняя сумма в месяц по возрастным группам

«Средняя сумма в месяц» — нужно для каждого пользователя посчитать сумму трат по месяцам, потом усреднить:

WITH per_user_month AS (
  SELECT u.userId,
         u.age,
         DATE_TRUNC('month', p.date) AS mth,
         SUM(i.price) AS spent
  FROM Users u
  JOIN Purchases p ON p.userId = u.userId
  JOIN Items     i ON i.itemId = p.itemId
  GROUP BY u.userId, u.age, DATE_TRUNC('month', p.date)
)
SELECT
  CASE
    WHEN age BETWEEN 18 AND 25 THEN '18-25'
    WHEN age BETWEEN 26 AND 35 THEN '26-35'
    ELSE 'other'
  END AS age_group,
  AVG(spent) AS avg_monthly_spend
FROM per_user_month
WHERE age BETWEEN 18 AND 35
GROUP BY age_group;

Тонкость: «средняя» по (user, month) тарелке — это «средний месяц активного пользователя». Если хотят «среднее на пользователя в среднем месяце», корректнее считать SUM(spent) / (n_users × n_months) — уточняйте.

B. Месяц года с максимальной выручкой 35+

SELECT EXTRACT(MONTH FROM p.date) AS month_of_year,
       SUM(i.price)               AS revenue
FROM Users u
JOIN Purchases p ON p.userId = u.userId
JOIN Items     i ON i.itemId = p.itemId
WHERE u.age >= 35
GROUP BY month_of_year
ORDER BY revenue DESC
LIMIT 1;

C. Товар с максимальным вкладом в выручку за последний год

SELECT p.itemId,
       SUM(i.price) AS revenue
FROM Purchases p
JOIN Items i ON i.itemId = p.itemId
WHERE p.date >= NOW() - INTERVAL '1 year'
GROUP BY p.itemId
ORDER BY revenue DESC
LIMIT 1;

D. Топ-3 товаров и их доля от общей выручки

WITH yr AS (
  SELECT p.itemId, SUM(i.price) AS rev
  FROM Purchases p JOIN Items i USING (itemId)
  WHERE EXTRACT(YEAR FROM p.date) = 2024
  GROUP BY p.itemId
),
total AS (
  SELECT SUM(rev) AS total_rev FROM yr
)
SELECT yr.itemId, yr.rev,
       ROUND(yr.rev * 100.0 / total.total_rev, 2) AS share_pct
FROM yr CROSS JOIN total
ORDER BY rev DESC
LIMIT 3;

Проверка / интерпретация

Sanity: сумма всех revenue per item = SUM(price) с join'ом — без NULL.
Месяц с максимумом часто декабрь (предновогодние) или 11 (Black Friday).
Доля топ-3 — индикатор concentration: если > 50% — мало диверсифицированный каталог.

Подводные камни

itemId без покупок — не попадет в результат C/D (нет revenue). Может быть нужно LEFT JOIN, если хотят все товары с 0.
age хранится статически — но возраст меняется! На дату покупки человек мог быть в другой группе. В простой задаче — игнорируется.
Quantity — в задании не указан, считаем 1 штука = 1 строка. Если есть — SUM(i.price * p.quantity).
Цены меняются: если Items.price — текущая, а покупка была год назад с другой ценой, неправильно. Нужны исторические цены.
«Месяц года» vs «месяц-год»: для (B) спрашивают «в каком месяце года» — то есть Jan/Feb/.../Dec, без года. EXTRACT(MONTH).
Average of monthly spend vs total/total: разные определения, проясните с заказчиком.

Эталонный ответ

A. CTE (userId, month, sum) → AVG по группе age. B. GROUP BY EXTRACT(MONTH) → ORDER DESC LIMIT 1. C. GROUP BY itemId за last year → ORDER DESC LIMIT 1. D. CTE yr → CROSS JOIN total → доля → LIMIT 3.

WhoIsBlogger — средние траты по возрастным группам и топ-товары по выручке