Условие

В Северсталь Junior.xlsx несколько мини-задач для junior-аналитика производственной компании:

SQL/Excel: дана таблица производства цеха production(date, shift, line_id, output_tons, defect_tons, downtime_min). Посчитать KPI цеха за месяц: производительность тонн/смену, % брака, OEE-прокси.
SQL: топ-10 поставщиков по объёму закупок (ID, общая сумма, доля в общем объёме).
Кейс: цех показывает рост output_tons, но и рост defect_tons — что предложить менеджеру?

Решение

1. KPI цеха

WITH monthly AS (
  SELECT line_id,
         DATE_TRUNC('month', date) AS mth,
         SUM(output_tons)            AS output_t,
         SUM(defect_tons)            AS defect_t,
         SUM(downtime_min)           AS downtime,
         COUNT(*)                    AS shifts
  FROM production
  GROUP BY line_id, DATE_TRUNC('month', date)
)
SELECT line_id, mth,
       output_t,
       output_t * 1.0 / shifts                AS productivity_per_shift,
       defect_t * 100.0 / NULLIF(output_t, 0) AS defect_pct,
       (output_t - defect_t) * 1.0 / output_t AS yield_rate,
       1 - downtime * 1.0 / (shifts * 8 * 60) AS availability,    -- 8h shift
       (output_t - defect_t) * 1.0
         / (shifts * 8 * 60 / 60 * 100)        AS oee_proxy        -- × planned tons/h
FROM monthly
ORDER BY mth, line_id;

OEE = Availability × Performance × Quality. Для прокси без plan_ton: упрощённо.

2. Top-10 поставщиков

WITH supplier_total AS (
  SELECT supplier_id, SUM(amount) AS total_purchase
  FROM purchases
  WHERE purchase_date >= NOW() - INTERVAL '1 year'
  GROUP BY supplier_id
),
grand AS (
  SELECT SUM(total_purchase) AS grand_total FROM supplier_total
)
SELECT s.supplier_id,
       s.total_purchase,
       s.total_purchase * 100.0 / g.grand_total AS share_pct,
       RANK() OVER (ORDER BY s.total_purchase DESC) AS rk
FROM supplier_total s CROSS JOIN grand g
ORDER BY s.total_purchase DESC
LIMIT 10;

3. Кейс: рост output, но рост defect

Декомпозиция:

\text{Defect rate} = \text{Defect tons} / \text{Output tons}

Возможные сценарии:

Defect rate растёт: качество ухудшается. Причины: forced production, уставшие операторы, износ оборудования, сырьё хуже.
Defect rate стабилен: рост defect tons пропорционален росту output → норма. Расследовать не надо.

Если defect rate растёт, диагностика:

По линии (line_id) — где растёт.
По смене — в ночную смену брак выше?
Корреляция с downtime — больше остановок → нестабильность процесса.
Корреляция с raw material — поставщик новой партии?
Сезонность.

Recommendations:

Если конкретная линия — TPM (Total Productive Maintenance).
Если смена — пересмотреть графики, добавить QC-контроль.
Если raw material — пересмотреть spec поставщика.
Долгосрочно: SPC-карты (Shewhart) для мониторинга.

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

Output без defect vs output total: иногда output_tons — это годная продукция, а defect — отдельно. Уточняйте.
NULLIF(output_t, 0) — на ноль не делим.
Standard shift = 8 часов = 480 минут: в производственных компаниях смена может быть 12 часов. Уточняйте.
Plan_tons для OEE Performance — обычно в отдельной таблице production_plan.
Возвраты не вычитаются автоматически — отдельный flow.
Сезонность: летом многие виды металлургии меньше работают (отпуска).

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

(1) GROUP BY (line, month) + базовые KPI; OEE-прокси требует plan для performance-компоненты. (2) GROUP BY supplier + RANK + SHARE через CROSS JOIN с grand total. (3) Сначала проверить defect_rate; если растёт — декомпозировать по линии/смене/материалу. Стандартный root-cause-каркас.

Северсталь Junior — агрегации продаж и расчёт KPI цеха