SQL-запросы — примеры по вашим таблицам

Все примеры написаны для базы Аналитика Дворец. Копируйте и используйте в Metabase.

Основные таблицы

Таблица	Что содержит	Ключевые колонки
wb_daily_sales	Продажи WB по дням	sale_date, entity, wb_article, quantity, total_price
wb_daily_orders	Заказы WB по дням	order_date, entity, wb_article, quantity, total_price
wb_daily_stocks	Остатки WB по дням	date, entity, wb_article, quantity_full
ozon_daily_orders	Заказы Ozon	created_at, entity, sku, price
ym_daily_orders	Заказы Яндекс Маркет	order_date, entity, offer_id, price
fin_wb	Финансовый отчёт WB	sale_date, entity, wb_article, to_seller, retail_price, delivery_cost
gsheet_product_chars	Справочник товаров	wb_article, product_name, category, entity
wb_feedbacks	Отзывы WB	created_date, nm_id, product_valuation, text
wb_questions	Вопросы WB	created_date, nm_id, text, answer

Важно: В таблицах wb_daily_* колонка товара называется wb_article.
В таблицах wb_feedbacks, wb_questions, wb_paid_storage — nm_id (это одно и то же!).

Примеры запросов

Продажи WB за последние 30 дней по организациям

SELECT
    entity AS "Организация",
    SUM(quantity) AS "Продано штук",
    SUM(total_price) AS "Выручка, ₽",
    COUNT(DISTINCT wb_article) AS "Артикулов"
FROM wb_daily_sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY entity
ORDER BY SUM(total_price) DESC

Топ-20 товаров по продажам за месяц

SELECT
    s.wb_article AS "Артикул",
    p.product_name AS "Название",
    SUM(s.quantity) AS "Продано шт",
    SUM(s.total_price) AS "Выручка ₽",
    ROUND(SUM(s.total_price) / NULLIF(SUM(s.quantity), 0), 2) AS "Средняя цена"
FROM wb_daily_sales s
LEFT JOIN gsheet_product_chars p ON p.wb_article = s.wb_article
WHERE s.sale_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY s.wb_article, p.product_name
ORDER BY SUM(s.total_price) DESC
LIMIT 20

Продажи по дням (тренд)

SELECT
    sale_date AS "Дата",
    SUM(quantity) AS "Продано шт",
    SUM(total_price) AS "Выручка ₽"
FROM wb_daily_sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY sale_date
ORDER BY sale_date

Динамика продаж с фильтром по организации

SELECT
    sale_date AS "Дата",
    entity AS "Организация",
    SUM(quantity) AS "Продано шт",
    SUM(total_price) AS "Выручка ₽"
FROM wb_daily_sales
WHERE 1=1
  [[AND entity = {{organization}}]]
  [[AND sale_date >= {{start_date}}::DATE]]
  [[AND sale_date <= {{end_date}}::DATE]]
GROUP BY sale_date, entity
ORDER BY sale_date

Остатки по складам WB на сегодня

SELECT
    wb_article AS "Артикул",
    entity AS "Организация",
    SUM(quantity_full) AS "Остаток шт"
FROM wb_daily_stocks
WHERE DATE = (SELECT MAX(DATE) FROM wb_daily_stocks)
GROUP BY wb_article, entity
HAVING SUM(quantity_full) > 0
ORDER BY SUM(quantity_full) DESC
LIMIT 50

Финансовый итог (P&L) по месяцам

SELECT
    DATE_TRUNC('month', sale_date) AS "Месяц",
    entity AS "Организация",
    SUM(to_seller) AS "Выплата продавцу ₽",
    SUM(retail_price) AS "Розничная цена ₽",
    SUM(delivery_cost) AS "Доставка ₽",
    SUM(storage) AS "Хранение ₽",
    SUM(total_fines) AS "Штрафы ₽"
FROM fin_wb
WHERE 1=1
  [[AND sale_date >= {{start_date}}::DATE]]
  [[AND sale_date <= {{end_date}}::DATE]]
GROUP BY DATE_TRUNC('month', sale_date), entity
ORDER BY DATE_TRUNC('month', sale_date) DESC, entity

Отзывы — рейтинг по товарам

SELECT
    f.nm_id AS "Артикул (nm_id)",
    p.product_name AS "Название",
    COUNT(*) AS "Кол-во отзывов",
    ROUND(AVG(f.product_valuation), 2) AS "Средний рейтинг",
    SUM(CASE WHEN f.product_valuation >= 4 THEN 1 ELSE 0 END) AS "Положительных",
    SUM(CASE WHEN f.product_valuation <= 2 THEN 1 ELSE 0 END) AS "Негативных"
FROM wb_feedbacks f
LEFT JOIN gsheet_product_chars p ON p.wb_article::BIGINT = f.nm_id
GROUP BY f.nm_id, p.product_name
ORDER BY AVG(f.product_valuation) ASC
LIMIT 30

Заказы vs Продажи (процент выкупа)

SELECT
    s.wb_article AS "Артикул",
    SUM(o.quantity) AS "Заказы шт",
    SUM(s.quantity) AS "Продажи шт",
    ROUND(100.0 * SUM(s.quantity) / NULLIF(SUM(o.quantity), 0), 1) AS "Выкуп %"
FROM wb_daily_orders o
LEFT JOIN wb_daily_sales s
    ON s.wb_article = o.wb_article
    AND s.entity = o.entity
    AND s.sale_date BETWEEN o.order_date AND o.order_date + INTERVAL '14 days'
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY s.wb_article
HAVING SUM(o.quantity) > 5
ORDER BY ROUND(100.0 * SUM(s.quantity) / NULLIF(SUM(o.quantity), 0), 1) ASC
LIMIT 30

Продажи по маркетплейсам (WB + Ozon + ЯМ)

SELECT 'WB' AS "Маркетплейс",
    DATE_TRUNC('month', sale_date) AS "Месяц",
    SUM(total_price) AS "Выручка ₽"
FROM wb_daily_sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY DATE_TRUNC('month', sale_date)
 
UNION ALL
 
SELECT 'Ozon',
    DATE_TRUNC('month', created_at::DATE),
    SUM(price)
FROM ozon_daily_orders
WHERE created_at >= (CURRENT_DATE - INTERVAL '90 days')::text
GROUP BY DATE_TRUNC('month', created_at::DATE)
 
UNION ALL
 
SELECT 'ЯМ',
    DATE_TRUNC('month', order_date),
    SUM(price)
FROM ym_daily_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY DATE_TRUNC('month', order_date)
 
ORDER BY "Месяц" DESC, "Маркетплейс"

Советы по SQL в Metabase

Shift+Enter — запустить запрос
Ctrl+/ — закомментировать строку
Используйте LIMIT 1000 при тестировании больших таблиц
DATE_TRUNC('month', дата) — округлить дату до месяца
CURRENT_DATE — сегодняшняя дата
INTERVAL '30 days' — 30 дней назад
NULLIF(x, 0) — защита от деления на ноль
AND ... — необязательный параметр (работает без значения)

← 2. Создание вопросов | 4. Дашборды →