Explain how you use SQL joins and stored procedures to transform data in a pipeline, and describe how you validate the results end to end.
Use the provided PostgreSQL tables to implement a refresh procedure for the case summary table. Include all source cases, calculate event metrics, resolve the latest assignment and event, and account for cases without matching records.
case_id, customer_ref, team, priority, event_count, total_handle_minutes, latest_status, and first_response_minutes.Unassigned where the current team is unavailable, and order by total_handle_minutes descending, then case_id ascending.| Column | Type | Description |
|---|---|---|
| case_idPK | INT | Unique case identifier |
| customer_ref | VARCHAR(10) | Customer reference |
| priority | VARCHAR(10) | Case priority |
| opened_at | TIMESTAMP | Timestamp when the case was opened |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| case_id | INT | Referenced case |
| status | VARCHAR(20) | Status after the event |
| event_ts | TIMESTAMP | Event timestamp |
| event_type | VARCHAR(20) | Type of case event |
| handle_minutes | NUMERIC(10,2) | Handling time attributed to the event |
| Column | Type | Description |
|---|---|---|
| assignment_idPK | INT | Unique assignment identifier |
| case_id | INT | Referenced case |
| team | VARCHAR(30) | Assigned Sutherland support team |
| effective_at | TIMESTAMP | Time the assignment became effective |
| Column | Type | Description |
|---|---|---|
| case_idPK | INT | Source case identifier |
| customer_ref | VARCHAR(10) | Customer reference copied from the source |
| team | VARCHAR(30) | Current team or Unassigned |
| priority | VARCHAR(10) | Case priority |
| event_count | INT | Number of events for the case |
| total_handle_minutes | NUMERIC(10,2) | Total event handling time |
| latest_status | VARCHAR(20) | Status from the latest event |
| first_response_minutes | NUMERIC(10,2) | Minutes from opening to first response |