====== 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 дней по организациям ====

<code sql>
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
</code>

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

<code sql>
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
</code>

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

<code sql>
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
</code>

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

<code sql>
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
</code>

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

<code sql>
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
</code>

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

<code sql>
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
</code>

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

<code sql>
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
</code>

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

<code sql>
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
</code>

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

<code sql>
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, "Маркетплейс"
</code>

===== Советы по SQL в Metabase =====

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

----
[[metabase:questions|← 2. Создание вопросов]]    |    [[metabase:dashboards|4. Дашборды →]]