Task
You are given AVEVA customer support data and need to identify which customers have generated the most support incidents. Write a PostgreSQL query that returns each active customer with its total number of incidents, the number of high-severity incidents, the date of the most recent incident, and a rank based on total incident count. Use a window function so customers with the same incident total receive the same rank, and order the final output by rank, then customer name.
Schema
customers
| column | type | description |
|---|
| customer_id | INT | Primary key for the customer |
| customer_name | VARCHAR(100) | Customer account name |
| account_status | VARCHAR(20) | Current account status |
| industry | VARCHAR(50) | Customer industry |
support_incidents
| column | type | description |
|---|
| incident_id | INT | Primary key for the incident |
| customer_id | INT | Customer tied to the incident |
| product_name | VARCHAR(100) | AVEVA product associated with the case |
| severity | VARCHAR(20) | Incident severity |
| opened_at | DATE | Date the incident was opened |
Sample data
| incident_id | customer_id | product_name | severity | opened_at |
|---|
| 101 | 2 | AVEVA PI System | High | 2024-01-15 |
| 102 | 1 | AVEVA InTouch HMI | Medium | 2024-02-10 |
| 108 | 5 | AVEVA Historian | High | 2024-03-05 |
Expected output
| customer_name | total_incidents | high_severity_incidents | last_incident_date | incident_rank |
|---|
| North Ridge Energy | 3 | 2 | 2024-03-18 | 1 |
| Blue Harbor Foods | 3 | 1 | 2024-03-05 | 1 |
| Summit Water | 2 | 1 | 2024-02-28 | 3 |
