Sunlight — топ-3 филиала по выполнению плана с агрегацией продаж

Условие

Дана таблица T1(Fil, DO, Sales, Plan_pct) с продажами и выполнением плана по дополнительным офисам. Например:

Нужно определить топ-3 филиала (Fil) по выполнению плана и вывести общую сумму продаж по каждому из них.

Решение

Подход

«Выполнение плана» по филиалу — обычно среднее или взвешенное среднее Plan, % по его офисам. Уточняем у заказчика, но безопаснее — взвешенное по Sales (большие офисы важнее).

WITH branch_perf AS (
  SELECT Fil,
         SUM(Sales)                                 AS total_sales,
         SUM(Sales * Plan_pct) / SUM(Sales)         AS weighted_plan_pct,
         AVG(Plan_pct)                              AS simple_avg_plan_pct
  FROM T1
  GROUP BY Fil
),
ranked AS (
  SELECT Fil,
         total_sales,
         weighted_plan_pct,
         RANK() OVER (ORDER BY weighted_plan_pct DESC) AS rk
  FROM branch_perf
)
SELECT rk AS Place,
       Fil,
       total_sales AS Sales,
       weighted_plan_pct AS plan_pct
FROM ranked
WHERE rk <= 3
ORDER BY rk;

Альтернатива: по простому среднему

Если бизнес-логика «среднее без весов»:

SELECT Fil, SUM(Sales) AS Sales, AVG(Plan_pct) AS plan_pct
FROM T1
GROUP BY Fil
ORDER BY plan_pct DESC
LIMIT 3;

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

1. Что значит «лучший филиал по выполнению плана» — взвешенное или простое среднее. Зависит от бизнес-смысла. Уточняйте.
2. RANK vs DENSE_RANK vs ROW_NUMBER: при ничьих важно, что нужно.
   • RANK — пропускает места (1, 2, 2, 4).
   • DENSE_RANK — без пропусков (1, 2, 2, 3).
   • ROW_NUMBER — всегда уникальные номера (1, 2, 3, 4).
3. Plan_pct как строка '120%' — нужно конвертировать. В реальной БД хранится как numeric(5,2).
4. Отрицательные продажи / возвраты: исключать или нет — бизнес-вопрос.
5. SUM(Sales * Plan_pct) / SUM(Sales) правильно считает weighted average, обращая plan_pct в численный.

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

GROUP BY Fil → агрегация SUM(Sales) и weighted average Plan, % → RANK() OVER (ORDER BY plan_pct DESC) → отфильтровать rank <= 3.