Your question is Supplier Cost Variance Trend Analysis. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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 |