Welcome to the SQL screen.
The question is on your right: Supplier Cost Variance Trend Analysis. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |