Самокат SQL — топ-10 товаров по продажам и потерям в каждой группе

Условие

За май 2021, по складам в Москве, построить два рейтинга:

1. По сумме продаж на 1 склад в рамках группы товаров 1-го уровня.
2. По сумме потерь на 1 склад в рамках группы 1-го уровня.

Вывести топ-10 товаров по каждому из них в каждой группе.

На выходе: group1, name, sales_per_warehouse, sales_rank, loss_per_warehouse, loss_rank.

Решение

Подход

1. Список московских складов в мае → справочник.
2. Агрегаты продаж и потерь по (group1, product_id) за май.
3. Делим суммы на число складов в группе → «на 1 склад».
4. DENSE_RANK внутри группы.
5. Фильтр rank ≤ 10 в любом из двух рейтингов.

Реализация

WITH moscow_wh AS (
  SELECT warehouse_id
  FROM warehouses
  WHERE city = 'Москва'
    AND (date_close IS NULL OR date_close >= DATE '2021-05-01')
    AND date_open <= DATE '2021-05-31'
),
n_wh AS (
  -- Число активных московских складов
  SELECT COUNT(*) AS cnt FROM moscow_wh
),
sales AS (
  SELECT
    p.group1, p.product_id, p.name,
    SUM(ol.paid_amount) AS sales_total
  FROM order_line ol
  JOIN moscow_wh w ON w.warehouse_id = ol.warehouse_id
  JOIN product   p ON p.product_id   = ol.product_id
  WHERE ol.date >= DATE '2021-05-01' AND ol.date < DATE '2021-06-01'
  GROUP BY p.group1, p.product_id, p.name
),
losses AS (
  SELECT
    p.group1, p.product_id, p.name,
    SUM(l.amount) AS loss_total
  FROM lost l
  JOIN moscow_wh w ON w.warehouse_id = l.warehouse_id
  JOIN product   p ON p.product_id   = l.product_id
  WHERE l.date >= DATE '2021-05-01' AND l.date < DATE '2021-06-01'
  GROUP BY p.group1, p.product_id, p.name
),
joined AS (
  SELECT
    COALESCE(s.group1,     l.group1)     AS group1,
    COALESCE(s.product_id, l.product_id) AS product_id,
    COALESCE(s.name,       l.name)       AS name,
    COALESCE(s.sales_total, 0) / (SELECT cnt FROM n_wh) AS sales_per_wh,
    COALESCE(l.loss_total,  0) / (SELECT cnt FROM n_wh) AS loss_per_wh
  FROM sales s
  FULL OUTER JOIN losses l
    ON l.group1 = s.group1 AND l.product_id = s.product_id
),
ranked AS (
  SELECT *,
    DENSE_RANK() OVER (PARTITION BY group1 ORDER BY sales_per_wh DESC) AS sales_rank,
    DENSE_RANK() OVER (PARTITION BY group1 ORDER BY loss_per_wh  DESC) AS loss_rank
  FROM joined
)
SELECT group1, name, sales_per_wh, sales_rank, loss_per_wh, loss_rank
FROM ranked
WHERE sales_rank <= 10 OR loss_rank <= 10
ORDER BY group1, LEAST(sales_rank, loss_rank);  -- сначала лучшие по ЛЮБОЙ из метрик

Что важно

• «На 1 склад» = сумма / число активных складов в Москве в мае. Если склад открылся 15 мая, он работает половину месяца — для точности можно делить на «склад-дни» (SUM(active_days)), но в задании это упрощение.
• DENSE_RANK vs RANK. DENSE_RANK без пропусков (1, 2, 2, 3); RANK с пропусками (1, 2, 2, 4). Для топ-10 чаще выбирают DENSE_RANK.
• FULL OUTER JOIN включает товары только-с-продажами и только-с-потерями.

Подводные камни

1. Что значит «активный склад в мае». Закрытые до 1 мая не считаем; открывшиеся в мае — считаем (но они работали меньше).
2. Дубль товара. Один product_id в продажах и потерях — это один товар, не два. Без FULL OUTER JOIN теряются позиции.
3. DENSE_RANK при равных суммах — несколько товаров получат одинаковый ранг 1; топ-10 может быть из 15 строк.
4. Группа 1-го уровня может быть пустая. Если в product group1 IS NULL, попадёт в свою группу. Решить: либо отфильтровать, либо сгруппировать в 'unknown'.
5. Производительность. На большом ритейле выгоднее предагрегировать в материализованные витрины (sales_by_day_wh_product).

Эталонный ответ

CTE: московские склады мая → агрегаты продаж и потерь по (group1, product_id) → FULL OUTER JOIN → деление на число складов → DENSE_RANK() OVER (PARTITION BY group1) для каждого рейтинга → фильтр top-10 в любом из двух.