Task
You are given QVC customer complaint data and asked to identify complaint trends over time. Write a PostgreSQL query that returns, for each complaint category and month in 2024, the total number of complaints, the number of unique customers who complained, the share of complaints marked High severity, the month-over-month change in complaint volume, and a rank of categories within each month by complaint count. Return only category-month combinations with at least 2 complaints, and include unresolved complaints even if they have no resolution record.
Schema
complaints
| column | type | description |
|---|
| complaint_id | INT | Primary key for each complaint |
| customer_id | INT | Customer who submitted the complaint |
| order_id | INT | Related order, if any |
| category_id | INT | Complaint category |
| channel | VARCHAR(50) | Intake channel such as QVC.com or phone |
| severity | VARCHAR(20) | Complaint severity |
| status | VARCHAR(20) | Current complaint status |
| created_at | DATE | Complaint creation date |
| | |
complaint_categories
| column | type | description |
|---|
| category_id | INT | Primary key |
| category_name | VARCHAR(100) | Complaint category name |
| | |
complaint_resolutions
| column | type | description |
|---|
| resolution_id | INT | Primary key |
| complaint_id | INT | Complaint being resolved |
| resolved_at | DATE | Resolution date |
| resolution_type | VARCHAR(50) | Resolution outcome |
| | |
Sample data
Representative rows include shipping delays, damaged items, billing issues, and app problems reported through QVC.com, phone, and chat.
Expected output
| complaint_month | category_name | complaint_count | unique_customers | high_severity_pct | mom_change | category_rank |
|---|
| 2024-02 | Shipping Delay | 3 | 3 | 66.67 | 1 | 1 |
| 2024-03 | Billing Issue | 2 | 2 | 50.00 | 1 | 2 |
