ERP Selection Scoring SQL Generator: Weighted Vendor Ranking Query

CREATE SCHEMA erp_selection;

CREATE TABLE erp_selection.vendor (
  vendor_id    INT PRIMARY KEY,
  vendor_name  VARCHAR(120) NOT NULL
);

CREATE TABLE erp_selection.criterion (
  criterion_id   INT PRIMARY KEY,
  category       VARCHAR(60) NOT NULL,
  criterion_name VARCHAR(160) NOT NULL,
  weight         DECIMAL(6,2) NOT NULL CHECK (weight > 0),
  is_mandatory   SMALLINT NOT NULL DEFAULT 0
);

CREATE TABLE erp_selection.score (
  vendor_id    INT NOT NULL REFERENCES erp_selection.vendor (vendor_id),
  criterion_id INT NOT NULL REFERENCES erp_selection.criterion (criterion_id),
  scorer       VARCHAR(80) NOT NULL,
  score        DECIMAL(4,1) NOT NULL CHECK (score >= 0 AND score <= 5),
  comment      VARCHAR(400) NULL,
  PRIMARY KEY (vendor_id, criterion_id, scorer)
);

WITH avg_score AS (
  SELECT vendor_id, criterion_id, AVG(score) AS avg_s
  FROM erp_selection.score
  GROUP BY vendor_id, criterion_id
)
SELECT v.vendor_name,
       ROUND(100.0 * SUM(a.avg_s * c.weight) / (SUM(c.weight) * 5), 1) AS weighted_pct
FROM avg_score a
JOIN erp_selection.criterion c ON c.criterion_id = a.criterion_id
JOIN erp_selection.vendor v ON v.vendor_id = a.vendor_id
GROUP BY v.vendor_name
ORDER BY weighted_pct DESC
FETCH FIRST 5 ROWS ONLY;

-- Vendors that scored below 2 on any mandatory criterion (average across scorers)
SELECT v.vendor_name, c.criterion_name, AVG(s.score) AS avg_s
FROM erp_selection.score s
JOIN erp_selection.criterion c ON c.criterion_id = s.criterion_id
JOIN erp_selection.vendor v ON v.vendor_id = s.vendor_id
WHERE c.is_mandatory = 1
GROUP BY v.vendor_name, c.criterion_name
HAVING AVG(s.score) < 2;

-- Weighted percent by category
SELECT v.vendor_name, c.category,
       ROUND(100.0 * SUM(s.score * c.weight) / (SUM(c.weight) * 5), 1) AS category_pct
FROM erp_selection.score s
JOIN erp_selection.criterion c ON c.criterion_id = s.criterion_id
JOIN erp_selection.vendor v ON v.vendor_id = s.vendor_id
GROUP BY v.vendor_name, c.category
ORDER BY v.vendor_name, c.category;