Точка заказа по артикулам на основе скорости продаж

SQL, по которому посчитано

WITH params AS (
  SELECT
    date_trunc('month', max(date)) - INTERVAL '11 months' AS start_date,
    max(date) AS end_date
  FROM sales
),
calendar AS (
  SELECT CAST(unnest(generate_series(start_date, end_date, INTERVAL '1 day')) AS DATE) AS date
  FROM params
),
sku_list AS (
  SELECT DISTINCT sku
  FROM sales
  WHERE date >= (SELECT start_date FROM params)
),
daily_sales AS (
  SELECT
    c.date,
    s.sku,
    COALESCE(SUM(s2.units), 0) AS units
  FROM calendar c
  CROSS JOIN sku_list s
  LEFT JOIN sales s2
    ON s2.sku = s.sku
   AND s2.date = c.date
  GROUP BY c.date, s.sku
),
sales_stats AS (
  SELECT
    sku,
    AVG(units) AS avg_daily_units,
    COALESCE(STDDEV_SAMP(units), 0) AS stddev_daily_units
  FROM daily_sales
  GROUP BY sku
),
stock_latest AS (
  SELECT sku, category, units AS stock_qty
  FROM stock
  WHERE date = (SELECT max(date) FROM stock)
),
calc AS (
  SELECT
    st.sku,
    sl.category,
    sl.stock_qty,
    st.avg_daily_units,
    1.65 * st.stddev_daily_units * SQRT(30) AS safety_stock_units,
    st.avg_daily_units * 30 + 1.65 * st.stddev_daily_units * SQRT(30) AS reorder_point_units
  FROM sales_stats st
  JOIN stock_latest sl ON sl.sku = st.sku
)
SELECT
  sku,
  category,
  CAST(ROUND(stock_qty, 0) AS INTEGER) AS stock_qty,
  ROUND(avg_daily_units, 2) AS avg_daily_units,
  CAST(ROUND(safety_stock_units, 0) AS INTEGER) AS safety_stock_units,
  CAST(ROUND(reorder_point_units, 0) AS INTEGER) AS reorder_point_units,
  CAST(ROUND(reorder_point_units - stock_qty, 0) AS INTEGER) AS deficit_units
FROM calc
WHERE stock_qty < reorder_point_units
ORDER BY deficit_units DESC
LIMIT 30