PrepBro
← Назад к вопросам

SQL: Пользователи с покупками выше среднего за 3 месяца

1.8 Middle🔥 201 комментариев
#SQL и базы данных#Аналитика и метрики

Условие

Даны таблицы:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100),
    registration_date DATE
);

CREATE TABLE purchases (
    purchase_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    purchase_date DATE
);

Задание:

  1. Найдите всех пользователей, которые совершили покупки на сумму выше среднего за последние 3 месяца
  2. Средняя сумма считается по всем пользователям за этот период
  3. Выведите: имя пользователя, общую сумму его покупок, среднюю сумму по всем пользователям
  4. Отсортируйте результат по сумме покупок по убыванию

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение

Анализ требований

Задача: Найти пользователей, чьи покупки за 3 месяца превышают общее среднее значение.

Ключевые моменты:

  1. Период: последние 3 месяца
  2. Средняя сумма: по ВСЕм пользователям (не индивидуальная)
  3. Фильтр: sum(amount по пользователю) > avg(amount по всем)
  4. Вывод: имя, сумма покупок, среднее по всем

Решение 1: С использованием оконной функции AVG (рекомендуется)

WITH last_3_months AS (
    -- Фильтруем покупки за последние 3 месяца
    SELECT
        p.user_id,
        p.amount,
        p.purchase_date
    FROM purchases p
    WHERE p.purchase_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
),
user_purchases AS (
    -- Считаем сумму покупок по каждому пользователю
    SELECT
        u.user_id,
        u.name,
        SUM(p.amount) AS user_total_purchases,
        -- Среднее по всем пользователям в одном окне
        ROUND(AVG(SUM(p.amount)) OVER (), 2) AS avg_all_users
    FROM users u
    LEFT JOIN last_3_months p ON u.user_id = p.user_id
    GROUP BY u.user_id, u.name
)
SELECT
    user_id,
    name,
    ROUND(user_total_purchases, 2) AS user_total_purchases,
    avg_all_users
FROM user_purchases
WHERE user_total_purchases > avg_all_users
ORDER BY user_total_purchases DESC;

Объяснение пошагово:

  1. last_3_months CTE: Фильтруем все покупки за последние 3 месяца
    • DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months' — дата 3 месяца назад
    • Эквивалент для других БД:
     - MySQL: `DATE_SUB(CURDATE(), INTERVAL 3 MONTH)`
     - SQL Server: `DATEADD(month, -3, CAST(GETDATE() AS DATE))`
  1. user_purchases CTE: Агрегация по пользователям
    • LEFT JOIN — для включения пользователей без покупок (их сумма = NULL или 0)
    • SUM(p.amount) — сумма покупок каждого пользователя
    • AVG(SUM(p.amount)) OVER () — среднее по всем пользователям
     - OVER () без partition — вычисляется по всему набору

  1. WHERE user_total_purchases > avg_all_users — фильтруем выше среднего

  2. ORDER BY user_total_purchases DESC — сортируем по убыванию

Решение 2: С подзапросом для среднего значения

WITH last_3_months AS (
    SELECT
        p.user_id,
        p.amount
    FROM purchases p
    WHERE p.purchase_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
),
user_totals AS (
    SELECT
        u.user_id,
        u.name,
        COALESCE(SUM(p.amount), 0) AS user_total_purchases
    FROM users u
    LEFT JOIN last_3_months p ON u.user_id = p.user_id
    GROUP BY u.user_id, u.name
),
average_calc AS (
    -- Подзапрос для расчёта среднего по всем пользователям
    SELECT
        ROUND(AVG(user_total_purchases), 2) AS avg_all_users
    FROM user_totals
)
SELECT
    ut.user_id,
    ut.name,
    ut.user_total_purchases,
    ac.avg_all_users
FROM user_totals ut
CROSS JOIN average_calc ac
WHERE ut.user_total_purchases > ac.avg_all_users
ORDER BY ut.user_total_purchases DESC;

Особенность: CROSS JOIN связывает таблицу результатов с одной строкой среднего.

Решение 3: Полностью в одном запросе (без CTE)

SELECT
    u.user_id,
    u.name,
    ROUND(SUM(p.amount), 2) AS user_total_purchases,
    ROUND(
        AVG(SUM(p.amount)) OVER (
            PARTITION BY NULL  -- По всему набору
        ),
        2
    ) AS avg_all_users
FROM users u
LEFT JOIN purchases p ON u.user_id = p.user_id
    AND p.purchase_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
GROUP BY u.user_id, u.name
HAVING SUM(p.amount) > AVG(SUM(p.amount)) OVER (PARTITION BY NULL)
ORDER BY user_total_purchases DESC;

Примечание: Некоторые БД не поддерживают оконные функции в HAVING. Используй Решение 1 в таких случаях.

Решение 4: Для разных БД

PostgreSQL:

WITH last_3_months AS (
    SELECT user_id, amount
    FROM purchases
    WHERE purchase_date >= CURRENT_DATE - INTERVAL '3 months'
)
SELECT
    u.user_id,
    u.name,
    ROUND(COALESCE(SUM(p.amount), 0)::NUMERIC, 2) AS user_total_purchases,
    ROUND(AVG(COALESCE(SUM(p.amount), 0)) OVER (), 2) AS avg_all_users
FROM users u
LEFT JOIN last_3_months p ON u.user_id = p.user_id
GROUP BY u.user_id, u.name
HAVING COALESCE(SUM(p.amount), 0) > AVG(COALESCE(SUM(p.amount), 0)) OVER ()
ORDER BY user_total_purchases DESC;

MySQL:

WITH last_3_months AS (
    SELECT user_id, amount
    FROM purchases
    WHERE purchase_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
)
SELECT
    u.user_id,
    u.name,
    ROUND(COALESCE(SUM(p.amount), 0), 2) AS user_total_purchases,
    ROUND(AVG(COALESCE(SUM(p.amount), 0)) OVER (), 2) AS avg_all_users
FROM users u
LEFT JOIN last_3_months p ON u.user_id = p.user_id
GROUP BY u.user_id, u.name
HAVING COALESCE(SUM(p.amount), 0) > AVG(COALESCE(SUM(p.amount), 0)) OVER ()
ORDER BY user_total_purchases DESC;

SQL Server:

WITH last_3_months AS (
    SELECT user_id, amount
    FROM purchases
    WHERE purchase_date >= DATEADD(MONTH, -3, CAST(GETDATE() AS DATE))
),
user_purchases AS (
    SELECT
        u.user_id,
        u.name,
        SUM(p.amount) AS user_total_purchases
    FROM users u
    LEFT JOIN last_3_months p ON u.user_id = p.user_id
    GROUP BY u.user_id, u.name
)
SELECT
    user_id,
    name,
    ROUND(user_total_purchases, 2) AS user_total_purchases,
    ROUND(AVG(user_total_purchases) OVER (), 2) AS avg_all_users
FROM user_purchases
WHERE user_total_purchases > (SELECT AVG(user_total_purchases) FROM user_purchases)
ORDER BY user_total_purchases DESC;

Решение 5: С дополнительной статистикой

WITH last_3_months AS (
    SELECT user_id, amount, purchase_date
    FROM purchases
    WHERE purchase_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
),
user_stats AS (
    SELECT
        u.user_id,
        u.name,
        u.registration_date,
        COUNT(DISTINCT p.purchase_id) AS purchase_count,
        SUM(p.amount) AS user_total_purchases,
        AVG(p.amount) AS avg_purchase_amount,
        MIN(p.amount) AS min_purchase,
        MAX(p.amount) AS max_purchase,
        ROUND(AVG(SUM(p.amount)) OVER (), 2) AS avg_all_users
    FROM users u
    LEFT JOIN last_3_months p ON u.user_id = p.user_id
    GROUP BY u.user_id, u.name, u.registration_date
)
SELECT
    user_id,
    name,
    registration_date,
    purchase_count,
    ROUND(user_total_purchases, 2) AS user_total_purchases,
    ROUND(avg_purchase_amount, 2) AS avg_purchase_amount,
    ROUND(min_purchase, 2) AS min_purchase,
    ROUND(max_purchase, 2) AS max_purchase,
    avg_all_users,
    ROUND(((user_total_purchases / avg_all_users - 1) * 100), 1) AS percent_above_avg
FROM user_stats
WHERE user_total_purchases > avg_all_users
ORDER BY user_total_purchases DESC;

Дополнительные колонки:

  • purchase_count — количество покупок
  • avg_purchase_amount — средняя сумма покупки
  • percent_above_avg — на сколько % выше среднего

Обработка NULL и граничные случаи

WITH last_3_months AS (
    SELECT user_id, amount
    FROM purchases
    WHERE purchase_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
        AND amount > 0  -- Исключаем отрицательные суммы (возвраты)
),
user_purchases AS (
    SELECT
        u.user_id,
        u.name,
        -- Если нет покупок за 3 месяца, используем 0
        COALESCE(SUM(p.amount), 0) AS user_total_purchases
    FROM users u
    LEFT JOIN last_3_months p ON u.user_id = p.user_id
    -- Фильтруем новых пользователей (регистрация после периода)
    WHERE u.registration_date <= (DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months')
    GROUP BY u.user_id, u.name
)
SELECT
    user_id,
    name,
    ROUND(user_total_purchases, 2) AS user_total_purchases,
    ROUND(AVG(user_total_purchases) OVER (), 2) AS avg_all_users
FROM user_purchases
WHERE user_total_purchases > AVG(user_total_purchases) OVER ()
ORDER BY user_total_purchases DESC;

Performance Optimization

-- 1. Индексы для быстрого поиска
CREATE INDEX idx_purchases_user_date 
    ON purchases(user_id, purchase_date);

CREATE INDEX idx_users_id 
    ON users(user_id);

-- 2. Материализованное представление
CREATE MATERIALIZED VIEW user_purchases_3m AS
WITH last_3_months AS (
    SELECT user_id, amount
    FROM purchases
    WHERE purchase_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
)
SELECT
    u.user_id,
    u.name,
    COALESCE(SUM(p.amount), 0) AS user_total_purchases
FROM users u
LEFT JOIN last_3_months p ON u.user_id = p.user_id
GROUP BY u.user_id, u.name;

-- 3. Запрос становится проще
SELECT
    user_id,
    name,
    user_total_purchases,
    ROUND(AVG(user_total_purchases) OVER (), 2) AS avg_all_users
FROM user_purchases_3m
WHERE user_total_purchases > AVG(user_total_purchases) OVER ()
ORDER BY user_total_purchases DESC;

Рекомендуемое решение для production

WITH last_3_months AS (
    SELECT user_id, amount
    FROM purchases
    WHERE purchase_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
        AND amount > 0
),
user_purchases AS (
    SELECT
        u.user_id,
        u.name,
        COALESCE(SUM(p.amount), 0) AS user_total_purchases,
        ROUND(AVG(COALESCE(SUM(p.amount), 0)) OVER (), 2) AS avg_all_users
    FROM users u
    LEFT JOIN last_3_months p ON u.user_id = p.user_id
    WHERE u.registration_date <= (DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months')
    GROUP BY u.user_id, u.name
)
SELECT
    user_id,
    name,
    ROUND(user_total_purchases, 2) AS user_total_purchases,
    avg_all_users
FROM user_purchases
WHERE user_total_purchases > avg_all_users
ORDER BY user_total_purchases DESC;

Итоговый чеклист

  • OVER () без PARTITION BY — среднее по всему набору
  • LEFT JOIN — чтобы не потерять пользователей без покупок
  • COALESCE(..., 0) — обработка NULL для пользователей без покупок
  • DATE_TRUNC(...) - INTERVAL — гибкий расчёт периодов
  • HAVING с оконными функциями работает не везде (используй подзапрос)
  • Фильтруй отрицательные суммы и новых пользователей
  • На больших данных используй индексы и materialized views
  • ROUND(..., 2) для финансовых данных
SQL: Пользователи с покупками выше среднего за 3 месяца | PrepBro