SQL-запросы для retail-аналитика

Специфика retail-аналитики

Retail — это не только e-commerce. В России огромный сегмент офлайн-ретейла: X5, Магнит, Лента, Ашан, Детский мир, Спортмастер и ещё сотни сетей. Плюс квик-коммерс и доставка еды — Самокат, Яндекс Еда, Delivery Club. Каждая из этих компаний имеет команду аналитиков, а задачи отличаются от классической продуктовой аналитики.

Главное отличие — в retail много физических ограничений. У вас есть магазины с конкретными площадями и stockom, склады с capacity, товары с shelf life, цепочки поставок с задержками. SQL для retail часто работает с более сложными схемами, чем в e-commerce.

Продажи по магазинам и категориям

Стандартный еженедельный отчёт в ретейле — продажи по магазинам в разрезе категорий.

SELECT
    s.store_id,
    s.store_name,
    s.city,
    c.category,
    SUM(sl.revenue) AS revenue,
    COUNT(DISTINCT sl.transaction_id) AS transactions,
    SUM(sl.revenue) / NULLIF(COUNT(DISTINCT sl.transaction_id), 0) AS avg_ticket
FROM sales_lines sl
JOIN stores s USING (store_id)
JOIN products p USING (product_id)
JOIN categories c USING (category_id)
WHERE sl.sale_date >= '2026-04-01'
  AND sl.sale_date < '2026-05-01'
GROUP BY s.store_id, s.store_name, s.city, c.category
ORDER BY revenue DESC;

На этом же запросе можно построить тепловую карту: строки — магазины, столбцы — категории, значение — revenue. Это даёт командам понимание, где какая категория хорошо продаётся, а где — нет.

Like-for-like sales (LFL)

Важнейшая метрика для сетей — сравнение магазинов, открытых минимум год назад. Новые магазины тянут суммарную выручку вверх, но LFL показывает органическую динамику.

WITH store_lfl AS (
    SELECT store_id FROM stores
    WHERE opened_at <= '2025-04-01'  -- минимум 12 месяцев работы
),
periods AS (
    SELECT
        store_id,
        SUM(revenue) FILTER (
            WHERE sale_date BETWEEN '2025-04-01' AND '2025-04-30'
        ) AS revenue_py,
        SUM(revenue) FILTER (
            WHERE sale_date BETWEEN '2026-04-01' AND '2026-04-30'
        ) AS revenue_cy
    FROM sales
    WHERE store_id IN (SELECT store_id FROM store_lfl)
    GROUP BY store_id
)
SELECT
    SUM(revenue_cy) AS total_cy,
    SUM(revenue_py) AS total_py,
    ROUND((SUM(revenue_cy) - SUM(revenue_py)) * 100.0 / SUM(revenue_py), 2) AS lfl_growth_pct
FROM periods;

LFL рост в ретейле меньше 3% считается слабым, 10%+ — отличным.

Тренироваться на таких вопросах можно в Telegram-боте Карьерник — там 1500+ задач с реальных собесов с разборами.

Оборачиваемость запасов (inventory turnover)

Показывает, как быстро продаётся товар из магазинов. Низкая оборачиваемость означает замороженный капитал.

WITH sales_by_product AS (
    SELECT
        product_id,
        SUM(units_sold) AS units_sold_per_month
    FROM sales
    WHERE sale_date >= CURRENT_DATE - INTERVAL '30 day'
    GROUP BY product_id
),
current_stock AS (
    SELECT
        product_id,
        SUM(units_on_hand) AS stock
    FROM inventory
    WHERE snapshot_date = CURRENT_DATE
    GROUP BY product_id
)
SELECT
    p.name,
    s.stock,
    sb.units_sold_per_month,
    s.stock * 30.0 / NULLIF(sb.units_sold_per_month, 0) AS days_of_supply
FROM products p
JOIN current_stock s USING (product_id)
LEFT JOIN sales_by_product sb USING (product_id)
WHERE s.stock > 0
ORDER BY days_of_supply DESC;

Товары с days_of_supply > 90 дней — кандидаты на скидку или снятие с продажи. Товары с days < 7 — на urgent-заказ у поставщика.

Out-of-stock analysis

Ситуация, когда товар заявлен, но физически отсутствует. Теряет продажи.

WITH daily_stock AS (
    SELECT
        store_id,
        product_id,
        snapshot_date,
        units_on_hand
    FROM inventory
    WHERE snapshot_date >= '2026-04-01'
)
SELECT
    s.store_name,
    c.category,
    COUNT(*) FILTER (WHERE ds.units_on_hand = 0) AS oos_days,
    COUNT(*) AS total_days,
    COUNT(*) FILTER (WHERE ds.units_on_hand = 0) * 100.0 / COUNT(*) AS oos_rate_pct
FROM daily_stock ds
JOIN stores s USING (store_id)
JOIN products p USING (product_id)
JOIN categories c USING (category_id)
GROUP BY s.store_name, c.category
HAVING COUNT(*) FILTER (WHERE ds.units_on_hand = 0) * 100.0 / COUNT(*) > 5
ORDER BY oos_rate_pct DESC;

Out-of-stock rate выше 5% — уже проблема. Магазины и категории с высокой OOS требуют внимания operations.

Вклад категорий (category mix)

Какие категории вносят какую долю в общую выручку.

WITH cat_revenue AS (
    SELECT
        c.category,
        SUM(sl.revenue) AS revenue
    FROM sales_lines sl
    JOIN products p USING (product_id)
    JOIN categories c USING (category_id)
    WHERE sl.sale_date >= CURRENT_DATE - INTERVAL '30 day'
    GROUP BY c.category
)
SELECT
    category,
    revenue,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS share_pct,
    ROUND(revenue * 100.0 /
        LAG(SUM(revenue)) OVER (ORDER BY revenue DESC), 2) AS vs_prev_category_pct
FROM cat_revenue
ORDER BY revenue DESC;

В продуктовых супермаркетах топ-5 категорий обычно дают 60–80% выручки. Если одна категория выше 40%, это концентрация риска.

ABC-анализ товаров

Классификация по вкладу в выручку.

WITH product_revenue AS (
    SELECT
        product_id,
        SUM(revenue) AS revenue
    FROM sales_lines
    WHERE sale_date >= CURRENT_DATE - INTERVAL '90 day'
    GROUP BY product_id
),
with_share AS (
    SELECT
        product_id,
        revenue,
        SUM(revenue) OVER (ORDER BY revenue DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_revenue,
        SUM(revenue) OVER () AS total_revenue
    FROM product_revenue
)
SELECT
    product_id,
    revenue,
    ROUND(cumulative_revenue * 100.0 / total_revenue, 2) AS cumulative_pct,
    CASE
        WHEN cumulative_revenue * 100.0 / total_revenue <= 80 THEN 'A'
        WHEN cumulative_revenue * 100.0 / total_revenue <= 95 THEN 'B'
        ELSE 'C'
    END AS abc_class
FROM with_share
ORDER BY revenue DESC;

Правило Парето работает: обычно 20% товаров дают 80% выручки. Это группа A — фокус внимания. C — кандидаты на снятие с полок.

К слову, набить руку на таких кейсах удобно через тренажёр в Telegram — разбирайте по 10 вопросов в день, через 2 недели тема становится рефлексом.

Средний чек по времени дня

В ресторанах и магазинах важно видеть пиковые часы.

SELECT
    EXTRACT(DOW FROM transaction_at) AS day_of_week,
    EXTRACT(HOUR FROM transaction_at) AS hour,
    COUNT(*) AS transactions,
    AVG(total_amount) AS avg_check,
    SUM(total_amount) AS total_revenue
FROM transactions
WHERE transaction_at >= CURRENT_DATE - INTERVAL '30 day'
GROUP BY 1, 2
ORDER BY 1, 2;

Этот отчёт помогает планировать смены персонала и маркетинговые кампании на непиковые часы.

Сравнение магазинов (benchmarking)

Позволяет видеть outliers — магазины, сильно отличающиеся от норм.

WITH store_metrics AS (
    SELECT
        s.store_id,
        s.store_name,
        s.format,  -- 'small', 'medium', 'large'
        s.sq_meters,
        SUM(sl.revenue) AS revenue,
        SUM(sl.revenue) / s.sq_meters AS revenue_per_sq_meter,
        COUNT(DISTINCT sl.transaction_id) AS transactions
    FROM sales_lines sl
    JOIN stores s USING (store_id)
    WHERE sale_date >= CURRENT_DATE - INTERVAL '30 day'
    GROUP BY s.store_id, s.store_name, s.format, s.sq_meters
)
SELECT *,
    AVG(revenue_per_sq_meter) OVER (PARTITION BY format) AS format_avg,
    revenue_per_sq_meter - AVG(revenue_per_sq_meter) OVER (PARTITION BY format) AS diff_from_format_avg
FROM store_metrics
ORDER BY revenue_per_sq_meter DESC;

Revenue per square meter — ключевая метрика эффективности магазина. Если магазин сильно ниже среднего по формату — investigating причины (плохое расположение, персонал, ассортимент).

Читайте также

• SQL для e-commerce
• Аналитик в маркетплейсе
• RFM-анализ
• ABC анализ

FAQ

Офлайн-ретейл или e-commerce?

Разные области, хотя сливаются в omnichannel. Офлайн имеет больше operations-задач (склады, магазины, логистика).

Сложнее ли SQL в retail?

Часто да. Схемы более сложные, много source tables.

Какие компании нанимают?

X5, Магнит, Лента, Ашан, Детский мир, Леруа Мерлен, Спортмастер, Ozon, Wildberries, Самокат.

Работа с Excel в retail?

Да, очень много. Многие бизнес-процессы идут через Excel: планы, бюджеты, отчёты руководству.