SQL: Найти товары с падением продаж

Решение: SQL-запрос для анализа падения продаж

Постановка задачи

Необходимо найти товары, у которых еженедельные продажи упали более чем на 30% между двумя последовательными неделями. Это критический метрика для выявления проблемных товаров, требующих немедленного внимания.

Подход

Используем оконные функции (Window Functions) для сравнения данных разных недель. Этот подход позволяет вычислить суммы продаж за каждую неделю и сравнить их без сложных JOIN-ов.

SQL-запрос

WITH weekly_sales AS (
    -- Вычисляем еженедельные продажи по товарам
    SELECT 
        product_id,
        DATE_TRUNC('week', date) AS week_start,
        SUM(units_sold) AS weekly_units,
        SUM(revenue) AS weekly_revenue
    FROM daily_sales
    GROUP BY product_id, DATE_TRUNC('week', date)
),
weekly_with_lag AS (
    -- Добавляем данные предыдущей недели через LAG
    SELECT 
        product_id,
        week_start,
        weekly_units,
        weekly_revenue,
        LAG(weekly_units) OVER (PARTITION BY product_id ORDER BY week_start) AS prev_week_units,
        LAG(weekly_revenue) OVER (PARTITION BY product_id ORDER BY week_start) AS prev_week_revenue
    FROM weekly_sales
),
calculated_change AS (
    -- Вычисляем процент изменения продаж
    SELECT 
        product_id,
        week_start,
        weekly_units AS last_week_sales,
        prev_week_units AS prev_week_sales,
        ROUND(
            ((weekly_units - prev_week_units) / NULLIF(prev_week_units, 0) * 100)::numeric, 
            2
        ) AS change_pct
    FROM weekly_with_lag
    WHERE prev_week_units IS NOT NULL
)
SELECT 
    product_id,
    last_week_sales,
    prev_week_sales,
    change_pct
FROM calculated_change
WHERE change_pct <= -30
ORDER BY product_id, change_pct;

Альтернативный вариант (если даты не регулярные)

Если в данных пропуски дат, используем номер недели последних 14 дней:

WITH two_weeks AS (
    -- Берём последние две недели данных
    SELECT 
        product_id,
        CASE 
            WHEN date >= CURRENT_DATE - INTERVAL '1 week' 
            THEN 'last_week' 
            ELSE 'prev_week' 
        END AS week_period,
        SUM(units_sold) AS total_units,
        SUM(revenue) AS total_revenue
    FROM daily_sales
    WHERE date >= CURRENT_DATE - INTERVAL '2 weeks'
    GROUP BY product_id, week_period
)
SELECT 
    product_id,
    MAX(CASE WHEN week_period = 'last_week' THEN total_units END) AS last_week_sales,
    MAX(CASE WHEN week_period = 'prev_week' THEN total_units END) AS prev_week_sales,
    ROUND(
        ((MAX(CASE WHEN week_period = 'last_week' THEN total_units END) - 
          MAX(CASE WHEN week_period = 'prev_week' THEN total_units END)) / 
         NULLIF(MAX(CASE WHEN week_period = 'prev_week' THEN total_units END), 0) * 100)::numeric,
        2
    ) AS change_pct
FROM two_weeks
GROUP BY product_id
HAVING MAX(CASE WHEN week_period = 'last_week' THEN total_units END) IS NOT NULL
    AND MAX(CASE WHEN week_period = 'prev_week' THEN total_units END) IS NOT NULL
    AND ((MAX(CASE WHEN week_period = 'last_week' THEN total_units END) - 
          MAX(CASE WHEN week_period = 'prev_week' THEN total_units END)) / 
         NULLIF(MAX(CASE WHEN week_period = 'prev_week' THEN total_units END), 0) * 100) <= -30
ORDER BY change_pct;

Объяснение ключевых элементов

CTEs (Common Table Expressions)

• weekly_sales — агрегирует дневные данные в еженедельные суммы
• weekly_with_lag — добавляет данные предыдущей недели через оконную функцию LAG()
• calculated_change — вычисляет процент падения

Оконные функции

• PARTITION BY product_id — разделяем данные по товарам
• ORDER BY week_start — упорядочиваем недели для корректной работы LAG()

Обработка NULL

• NULLIF(prev_week_units, 0) — избегаем деления на ноль
• WHERE prev_week_units IS NOT NULL — исключаем товары без истории

Примеры результатов

Оптимизация для больших данных

1. Индексы — создать индекс на (product_id, date) для ускорения GROUP BY
2. Партиционирование — если таблица огромна, разделить на партиции по дате
3. Инкрементальные вычисления — обновлять результаты только для последней недели

Этот запрос можно интегрировать в отчёты аналитики для мониторинга здоровья продуктового портфеля.