SQL Monthly Demand History Query Generator for Forecasting

WITH months AS (
  SELECT generate_series(DATE '2024-01-01', DATE '2026-10-01' - INTERVAL '1 month', INTERVAL '1 month')::date AS month_start
),
items AS (
  SELECT DISTINCT item_code AS item
  FROM sales_order_lines
  WHERE item_code LIKE 'BRK-%'
),
monthly AS (
  SELECT item_code AS item,
         DATE_TRUNC('month', ship_date)::date AS month_start,
         SUM(qty_shipped) AS qty
  FROM sales_order_lines
  WHERE ship_date >= DATE '2024-01-01'
    AND ship_date < DATE '2026-10-01'
    AND item_code LIKE 'BRK-%'
  GROUP BY item_code, DATE_TRUNC('month', ship_date)
)
SELECT i.item, m.month_start, COALESCE(d.qty, 0) AS qty
FROM items i
CROSS JOIN months m
LEFT JOIN monthly d ON d.item = i.item AND d.month_start = m.month_start
ORDER BY i.item, m.month_start;

SELECT item_code AS item,
       DATEFROMPARTS(YEAR(ship_date), MONTH(ship_date), 1) AS month_start,
       SUM(qty_shipped) AS qty
FROM sales_order_lines
WHERE ship_date >= '2024-01-01'
  AND ship_date < '2026-10-01'
  AND item_code LIKE 'BRK-%'
GROUP BY item_code, DATEFROMPARTS(YEAR(ship_date), MONTH(ship_date), 1)
ORDER BY item_code, month_start;

-- Wrap the first query as monthly_full(item, month_start, qty)
SELECT item,
       COUNT(*) AS total_periods,
       COUNT(*) FILTER (WHERE qty > 0) AS nonzero_periods,
       AVG(qty) FILTER (WHERE qty > 0) AS mean_size,
       STDDEV_SAMP(qty) FILTER (WHERE qty > 0) AS sd_size
FROM monthly_full
GROUP BY item;