В этой статье разберу 4 задачи, которые решала на данных, приближенных к реальному ритейлу. Каждая задача сложнее предыдущей, и каждая дает конкретный бизнес-результат.

Данные для всех задач

Для всех примеров используем структуру аптечной сети, вот данные, которые можно скопировать и вставить для проверки:

-- Таблица аптек
CREATE TABLE pharmacies (
    pharmacy_id INT PRIMARY KEY,
    pharmacy_name VARCHAR(100),
    city VARCHAR(50),
    open_date DATE
);

-- Таблица товаров
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200),
    category VARCHAR(100),
    price DECIMAL(10,2)
);

-- Таблица продаж
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    pharmacy_id INT,
    product_id INT,
    sale_date DATE,
    quantity INT,
    amount DECIMAL(10,2),
    customer_id INT
);

-- Заполняем данными
INSERT INTO pharmacies VALUES
(1, 'Столичная', 'Москва', '2020-01-15'),
(2, 'Доктор Айболит', 'Москва', '2020-03-20'),
(3, 'Здравсити', 'СПб', '2020-02-10'),
(4, 'Горздрав', 'СПб', '2020-04-05'),
(5, 'Аптека 24', 'Казань', '2020-05-12');

INSERT INTO products VALUES
(101, 'Ибупрофен 200мг', 'Обезболивающие', 150.00),
(102, 'Цитрамон П', 'Обезболивающие', 80.00),
(103, 'Аквафорте', 'Витамины', 450.00),
(104, 'Мезим форте', 'Пищеварение', 220.00),
(105, 'Но-шпа', 'Спазмолитики', 180.00),
(106, 'Парацетамол', 'Жаропонижающие', 50.00),
(107, 'Супрастин', 'Антигистаминные', 200.00),
(108, 'Кагоцел', 'Противовирусные', 300.00);

-- Генерируем продажи (результат выполнения этого запроса будет ниже)
-- Для полноты данных я сразу покажу результат

Данные по продажам (упрощенно):

Задача 1. Топ-3 аптеки по выручке (базовый GROUP BY)

Категорийному менеджеру нужно понять, какие аптеки приносят основную выручку, это база для инвестиций и масштабирования.

Простой запрос

SELECT 
    p.pharmacy_name,
    SUM(s.amount) AS total_revenue
FROM sales s
JOIN pharmacies p ON s.pharmacy_id = p.pharmacy_id
GROUP BY p.pharmacy_name
ORDER BY total_revenue DESC
LIMIT 3;

Результат:

Пример вывода по данным: Вывели три аптеки-лидера по выручке, их стоит рассматривать как пилотные площадки для новых продуктов и маркетинговых экспериментов.

Усложненный вариант: с долей в общей выручке и ранжированием

Но простой топ не показывает картину целиком, добавим оконные функции:

WITH pharmacy_revenue AS (
    SELECT 
        p.pharmacy_name,
        SUM(s.amount) AS revenue,
        SUM(SUM(s.amount)) OVER() AS total_revenue
    FROM sales s
    JOIN pharmacies p ON s.pharmacy_id = p.pharmacy_id
    GROUP BY p.pharmacy_name
)
SELECT 
    pharmacy_name,
    revenue,
    ROUND(100.0 * revenue / total_revenue, 2) AS revenue_share_percent,
    RANK() OVER(ORDER BY revenue DESC) AS rank,
    CASE 
        WHEN revenue > 0.1 * total_revenue THEN 'Ключевая'
        ELSE 'Обычная'
    END AS pharmacy_type
FROM pharmacy_revenue
ORDER BY revenue DESC;

Результат:

Пример вывода по данным: Даже пятая аптека дает >10% выручки – все точки важны, нет явных аутсайдеров.

Задача 2. Товары с высоким и низким спросом (анализ популярности)

Нужно выявить товары-локомотивы и товары-аутсайдеры для управления ассортиментом.

Простой запрос (топ товаров)

SELECT 
    pr.product_name,
    pr.category,
    SUM(s.quantity) AS total_units,
    SUM(s.amount) AS total_revenue
FROM sales s
JOIN products pr ON s.product_id = pr.product_id
GROUP BY pr.product_name, pr.category
ORDER BY total_units DESC
LIMIT 5;

Результат:

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

Усложненный вариант: ABC-анализ по выручке

Добавим оконные функции для ABC-классификации:

WITH product_sales AS (
    SELECT 
        pr.product_id,
        pr.product_name,
        pr.category,
        SUM(s.amount) AS revenue,
        SUM(SUM(s.amount)) OVER() AS total_revenue,
        ROW_NUMBER() OVER(ORDER BY SUM(s.amount) DESC) AS revenue_rank
    FROM sales s
    JOIN products pr ON s.product_id = pr.product_id
    GROUP BY pr.product_id, pr.product_name, pr.category
),
cumulative_calc AS (
    SELECT 
        product_id,
        product_name,
        category,
        revenue,
        revenue_rank,
        SUM(revenue) OVER(ORDER BY revenue_rank) AS cumulative_revenue,
        total_revenue
    FROM product_sales
)
SELECT 
    product_name,
    category,
    revenue,
    ROUND(100.0 * revenue / total_revenue, 2) AS revenue_share,
    ROUND(100.0 * cumulative_revenue / total_revenue, 2) AS cumulative_share,
    CASE 
        WHEN cumulative_revenue <= 0.7 * total_revenue THEN 'A'
        WHEN cumulative_revenue <= 0.9 * total_revenue THEN 'B'
        ELSE 'C'
    END AS abc_class
FROM cumulative_calc
ORDER BY revenue_rank;

Результат (первые строки):

Пример вывода по данным: Топ-5 товаров дают 60% выручки (класс A), это золотой фонд, который должен быть на полке всегда.

Задача 3. Доля категории в общем обороте (структурный анализ)

Руководство хочет понимать, какие категории товаров приносят основную выручку.

Простой запрос

SELECT 
    pr.category,
    SUM(s.amount) AS category_revenue,
    COUNT(DISTINCT s.product_id) AS products_in_category
FROM sales s
JOIN products pr ON s.product_id = pr.product_id
GROUP BY pr.category
ORDER BY category_revenue DESC;

Результат:

Усложненный вариант: доля с накоплением и сравнение с количеством товаров

Добавим оконные функции для кумулятивной доли и нормализацию по числу товаров:

WITH category_stats AS (
    SELECT 
        pr.category,
        SUM(s.amount) AS revenue,
        COUNT(DISTINCT s.product_id) AS products_count,
        SUM(SUM(s.amount)) OVER() AS total_revenue,
        SUM(COUNT(DISTINCT s.product_id)) OVER() AS total_products
    FROM sales s
    JOIN products pr ON s.product_id = pr.product_id
    GROUP BY pr.category
),
cumulative_calc AS (
    SELECT 
        category,
        revenue,
        products_count,
        ROUND(100.0 * revenue / total_revenue, 2) AS revenue_share,
        ROUND(100.0 * products_count / total_products, 2) AS products_share,
        SUM(revenue) OVER(ORDER BY revenue DESC) / total_revenue * 100 AS cumulative_share
    FROM category_stats
)
SELECT 
    category,
    revenue,
    revenue_share,
    products_count,
    products_share,
    ROUND(cumulative_share, 2) AS cumulative_share,
    CASE 
        WHEN revenue_share > products_share * 2 THEN 'Сверхэффективная'
        WHEN revenue_share > products_share THEN 'Эффективная'
        ELSE 'Неэффективная'
    END AS efficiency
FROM cumulative_calc
ORDER BY revenue DESC;

Результат:

Пример вывода по данным: Категория Пищеварение дает меньшую долю выручки, чем должна по количеству товаров – возможно, проблемы с ценообразованием или выкладкой .

Задача 4. Кумулятивные продажи с детализацией (динамика во времени)

Финансовому директору нужен отчет о накопленной выручке, чтобы видеть, как выполняются планы.

Простой запрос (кумулятивные продажи по аптекам)

SELECT 
    s.pharmacy_id,
    p.pharmacy_name,
    s.sale_date,
    s.amount,
    SUM(s.amount) OVER(PARTITION BY s.pharmacy_id ORDER BY s.sale_date) AS cumulative_revenue
FROM sales s
JOIN pharmacies p ON s.pharmacy_id = p.pharmacy_id
ORDER BY p.pharmacy_name, s.sale_date;

Усложненный вариант: скользящее среднее, сравнение с прошлым периодом и прогноз

Добавим полноценный временной анализ с lag-функциями и скользящим средним:

WITH daily_sales AS (
    -- Агрегируем продажи по дням
    SELECT 
        s.sale_date,
        SUM(s.amount) AS daily_revenue,
        COUNT(DISTINCT s.pharmacy_id) AS active_pharmacies,
        COUNT(DISTINCT s.customer_id) AS unique_customers
    FROM sales s
    GROUP BY s.sale_date
),
sales_with_metrics AS (
    SELECT 
        sale_date,
        daily_revenue,
        active_pharmacies,
        unique_customers,
        -- Скользящее среднее за 3 дня
        AVG(daily_revenue) OVER(ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3d,
        -- Выручка за предыдущий день
        LAG(daily_revenue) OVER(ORDER BY sale_date) AS prev_day_revenue,
        -- Накопленная выручка с начала месяца
        SUM(daily_revenue) OVER(PARTITION BY DATE_TRUNC('month', sale_date) ORDER BY sale_date) AS month_cumulative,
        -- Средний чек в день
        daily_revenue / NULLIF(unique_customers, 0) AS avg_receipt
    FROM daily_sales
),
growth_analysis AS (
    SELECT 
        sale_date,
        daily_revenue,
        ROUND(moving_avg_3d, 2) AS moving_avg_3d,
        ROUND(100.0 * (daily_revenue - prev_day_revenue) / NULLIF(prev_day_revenue, 0), 2) AS daily_growth_percent,
        month_cumulative,
        ROUND(avg_receipt, 2) AS avg_receipt,
        -- Прогноз на следующий день (простое экспоненциальное сглаживание)
        ROUND(0.3 * daily_revenue + 0.7 * moving_avg_3d, 2) AS forecast_next_day
    FROM sales_with_metrics
)
SELECT 
    sale_date,
    daily_revenue,
    moving_avg_3d,
    daily_growth_percent,
    month_cumulative,
    avg_receipt,
    forecast_next_day,
    CASE 
        WHEN daily_growth_percent > 20 THEN '? Резкий рост'
        WHEN daily_growth_percent < -20 THEN '? Резкое падение'
        ELSE '➡️ Стабильно'
    END AS trend
FROM growth_analysis
ORDER BY sale_date;

Результат:

Пример вывода по данным:
3 февраля резкое падение выручки (-35% к предыдущему дню) возможно, выходной или проблемы с поставками. Средний чек при падении вырос (573 руб. против 296) значит, покупателей стало меньше, но те, кто пришел, купили больше. Прогноз на 5 февраля около 957 руб., стоит проверить, сбудется ли на будущих данных по выручке для необходимости корректировки логики расчета прогноза.

Заключение

SQL в аналитике – это не просто достать данные, это инструмент, который помогает собирать уже готовые отчеты, чтобы понять, например:
- структуру бизнеса (кто приносит деньги)
- как лучше управлять ассортиментом (что оставить, что убрать)
- динамику и наличие аномалий (где провалы и взлеты)

Оконные функции и CTE превращают SQL из просто калькулятора в полноценный аналитический инструмент, освоив их, вы сможете отвечать на сложные бизнес-вопросы без выгрузки данных в Excel.

?Еще больше про будни и задачи аналитика данных в моем тг канале ?Таня и Данные?