Условие

Таблицы (фрагмент):

order_line(date, warehouse_id, product_id, price, regular_price, cost_price, quantity, paid_amount) — продажи.
lost(date, warehouse_id, product_id, item_id, quantity, amount) — потери (item_id — статья потерь, например 146 = списание по сроку).
product(product_id, name, group1, group2, group3, weight, shelf_life).

За последние 4 недели по компании в целом по каждой группе товаров 2-го уровня рассчитать:

% потерь — отношение суммы потерь к сумме продаж этой группы.
Доля потерь — какую часть в общих потерях компании занимает эта группа.

Учитывать все статьи потерь.

На выходе: group1, group2, loss_pct_of_sales, loss_share.

Решение

Подход

CTE с агрегацией продаж и потерь по (group1, group2) за период → оконка для total потерь компании → проценты.

Реализация

WITH period AS (
  SELECT
    DATE_TRUNC('week', CURRENT_DATE)        AS week_end,
    DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '28 day' AS period_start
),
sales AS (
  SELECT
    p.group1, p.group2,
    SUM(ol.paid_amount) AS sales_sum
  FROM order_line ol
  JOIN product p USING (product_id), period
  WHERE ol.date >= period.period_start
    AND ol.date <  period.week_end
  GROUP BY p.group1, p.group2
),
losses AS (
  SELECT
    p.group1, p.group2,
    SUM(l.amount) AS loss_sum
  FROM lost l
  JOIN product p USING (product_id), period
  WHERE l.date >= period.period_start
    AND l.date <  period.week_end
  GROUP BY p.group1, p.group2
),
joined AS (
  -- Полное соединение, чтобы группа с продажами без потерь и наоборот не выпала
  SELECT
    COALESCE(s.group1, l.group1) AS group1,
    COALESCE(s.group2, l.group2) AS group2,
    COALESCE(s.sales_sum, 0)     AS sales_sum,
    COALESCE(l.loss_sum,  0)     AS loss_sum
  FROM sales s
  FULL OUTER JOIN losses l ON l.group1 = s.group1 AND l.group2 = s.group2
)
SELECT
  group1,
  group2,
  ROUND(100.0 * loss_sum / NULLIF(sales_sum, 0), 2) AS loss_pct_of_sales,
  ROUND(100.0 * loss_sum / NULLIF(SUM(loss_sum) OVER (), 0), 2) AS loss_share
FROM joined
ORDER BY loss_share DESC NULLS LAST;

Что важно

«Последние 4 недели» часто означает «закрытые ISO-недели» (без текущей неполной). DATE_TRUNC('week', CURRENT_DATE) в PostgreSQL даёт понедельник текущей недели; вычитаем 28 дней — старт периода.
FULL OUTER JOIN обязателен: если у группы есть только потери (продаж 0) или только продажи — её не должно «съесть».
NULLIF(sales_sum, 0) — защита от деления на 0 (фрукты-овощи могут попасть в потери, но без продаж — например, испорченная партия).

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

«% потерь от продаж» vs «% потерь от себестоимости» — это разные числа. В рознице обычно от выручки.
«Все статьи» включают как просрочку (146), так и порчу, кражи, переоценку. В каждой статье может быть отрицательная сумма (возврат потери) — это редко, но проверить.
order_line.paid_amount или price * quantity? В данных может быть промо-скидка, лучше paid_amount.
Отрицательные потери. Если есть, аккуратно с NULLIF.
«Доля» суммы по OVER() считает по всем строкам результата — это и нужно.
Часовой пояс / момент среза. «Последние 4 недели» неоднозначно. Желательно явно проговорить «последние 4 ISO-недели от понедельника».

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

CTE для продаж и потерь по (group1, group2) за 4 недели → FULL OUTER JOIN → loss / sales для % и loss / SUM(loss) OVER () для доли. Округление и NULLIF для защиты от 0.

Самокат стажировка SQL — % потерь и доля потерь по группам товаров