You are given purchasing and receipt data exported from an ERP such as BAE Systems USA SAP. Write a PostgreSQL query that returns, for each supplier and month in Q1 2024, the total spend, average cost variance versus standard cost, on-time delivery rate, and a performance flag. Only include supplier-month combinations with at least 2 purchase order lines in that month.
The result should help you spot suppliers with worsening price performance or delivery reliability by summarizing purchasing activity at a monthly level.
| Column | Type | Description |
|---|---|---|
| supplier_idPK | INT | Unique supplier identifier |
| supplier_name | VARCHAR(100) | Supplier name |
| supplier_status | VARCHAR(20) | Supplier lifecycle status |
| Column | Type | Description |
|---|---|---|
| po_idPK | INT | Purchase order line identifier |
| supplier_id | INT | Supplier reference |
| material_code | VARCHAR(30) | Material code from ERP |
| order_date | DATE | Date the PO was created |
| promised_date | DATE | Promised delivery date |
| received_date | DATE | Actual receipt date |
| quantity | INT | Ordered quantity |
| unit_price | DECIMAL(10,2) | Actual unit purchase price |
| Column | Type | Description |
|---|---|---|
| material_codePK | VARCHAR(30) | Material identifier |
| standard_cost | DECIMAL(10,2) | Standard cost used for variance analysis |
| cost_category | VARCHAR(30) | Material cost category |