Task
You are given customer feedback submitted across DISH touchpoints such as the MyDISH app, chat, phone, and web. Write a PostgreSQL query that returns feedback performance by channel, product_name, and region_name for feedback submitted in Q1 2024. For each combination, show the total number of feedback records, average rating rounded to 2 decimals, counts of positive and negative feedback based on rating, and rank the combinations within each channel by lowest average rating so the most problematic segments appear first. Only include groups with at least 2 feedback records.
Schema
feedback
| column | type | description |
|---|
| feedback_id | INT | Unique feedback record |
| customer_id | INT | Customer identifier |
| product_id | INT | Referenced DISH product |
| submitted_at | DATE | Feedback submission date |
| channel | VARCHAR(50) | Submission channel |
| rating | INT | Rating from 1 to 5 |
| comment_text | TEXT | Free-text comment |
| | |
products
| column | type | description |
|---|
| product_id | INT | Unique product identifier |
| product_name | VARCHAR(100) | Product name |
| | |
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| region_name | VARCHAR(50) | Customer region |
Sample data
| feedback_id | customer_id | product_id | submitted_at | channel | rating |
|---|
| 1 | 101 | 1 | 2024-01-15 | MyDISH App | 2 |
| 2 | 102 | 1 | 2024-02-10 | MyDISH App | 4 |
| 4 | 104 | 2 | 2024-01-20 | Chat | 1 |
| 5 | 105 | 2 | 2024-02-05 | Chat | 2 |
| 7 | 107 | 3 | 2024-03-01 | Phone | 5 |
Expected output
| channel | product_name | region_name | feedback_count | avg_rating | positive_count | negative_count | issue_rank |
|---|
| Chat | DISH TV | South | 2 | 1.50 | 0 | 2 | 1 |
| MyDISH App | Boost Mobile | West | 2 | 2.50 | 1 | 1 | 1 |
