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.
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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| feedback_idPK | INT | Unique feedback record identifier |
| customer_id | INT | Customer who submitted the feedback |
| product_id | INT | Referenced product identifier |
| submitted_at | DATE | Date the feedback was submitted |
| channel | VARCHAR(50) | Feedback channel such as MyDISH App, Chat, Phone, or Web |
| rating | INT | Customer rating on a 1 to 5 scale |
| comment_text | TEXT | Optional free-text feedback comment |
| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Unique product identifier |
| product_name | VARCHAR(100) | DISH product or service name |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| region_name | VARCHAR(50) | Customer region |
| feedback_id | customer_id | product_id | submitted_at | channel | rating | comment_text |
|---|---|---|---|---|---|---|
| 1 | 101 | 1 | 2024-01-15 | MyDISH App | 2 | App login keeps failing |
| 2 | 102 | 1 | 2024-02-10 | MyDISH App | 4 | Billing page worked fine |
| 3 | 103 | 1 | 2024-04-03 | MyDISH App | 1 | Outside quarter |
| 4 | 104 | 2 | 2024-01-20 | Chat | 1 | Long wait time |
| 5 | 105 | 2 | 2024-02-05 | Chat | 2 | Issue not resolved |
| 6 | 106 | 2 | 2024-02-18 | Web | 3 | Average experience |
| 7 | 107 | 3 | 2024-03-01 | Phone | 5 | Agent was helpful |
| 8 | 108 | 3 | 2024-03-12 | Phone | 4 | Quick setup support |
| 9 | 109 | 3 | 2024-03-15 | Phone | No rating left | |
| 10 | 110 | 4 | 2024-01-25 | Web | 1 | Service outage complaint |
| 11 | 111 | 4 | 2024-02-14 | Web | 2 | Streaming quality poor |
| 12 | 999 | 2 | 2024-03-22 | Chat | 5 | Customer missing from dimension |
| 1 | 12 | 5 | 2024-02-22 | Support Ticket | 73 | Activation took too long |
| 2 | 60 | 81 | 2024-03-22 | IVR | 59 | Representative was knowledgeable |
| 3 | 91 | 54 | 2024-01-22 | Installation Visit | 37 | Agent was helpful |
| 4 | 35 | 37 | 2024-03-24 | Store Pickup | 33 | Streaming quality poor |
| 5 | 37 | 99 | 2024-02-27 | Chat | 6 | Support article solved the issue |
| 6 | 49 | 61 | 2024-03-17 | Technician Visit | 5 | Upgrade process was smooth |
| 7 | 78 | 69 | 2024-01-27 | Support Ticket | 47 | Customer missing from dimension |
| 8 | 81 | 66 | 2024-02-11 | SMS | 67 | null |
| 9 | 46 | 19 | 2024-02-19 | Streaming App | 3 | App navigation is confusing |
| 10 | 35 | 21 | 2024-02-14 | Web | 99 | Could not reset password |
| 11 | 24 | 72 | 2024-03-13 | MyDISH App | null | null |
| 12 | 8 | 63 | 2024-03-21 | Outbound Call | 23 | Plan details were confusing |
| 13 | 13 | 80 | 2024-02-28 | 9 | Audio quality was poor | |
| 14 | 64 | 31 | 2024-02-03 | Chat | null | Technician arrived late |
| 15 | 3 | 95 | 2024-03-16 | Social | 74 | null |
| 16 | 63 | 53 | 2024-02-03 | Chat | 83 | Customer missing from dimension |
| product_id | product_name |
|---|---|
| 1 | Boost Mobile |
| 2 | DISH TV |
| 3 | OnTech |
| 4 | Sling TV |
| 5 | Gen Mobile |
| 6 | DISH Protect |
| 7 | Boost Infinite |
| 8 | MyDISH Billing |
| 1 | Upgrade Program |
| 2 | Network Pass |
| 3 | Installation Service |
| 4 | DISH Protect |
| 5 | International Package |
| 6 | Account Recovery |
| 7 | DISH Protect |
| 8 | Boost Infinite |
| 9 | Account Recovery |
| 10 | DISH Protect |
| 11 | Trade-In Service |
| 12 | Gen Mobile |
| 13 | Boost Infinite |
| 14 | Prepaid Unlimited |
| 15 | Trade-In Service |
| 16 | DISH TV |
| 17 | Boost Infinite |
| 18 | Smart Home Setup |
| 19 | Network Pass |
| 20 | DISH Protect |
| customer_id | region_name |
|---|---|
| 101 | West |
| 102 | West |
| 103 | South |
| 104 | South |
| 105 | South |
| 106 | Midwest |
| 107 | East |
| 108 | East |
| 109 | |
| 110 | West |
| 111 | West |
| 112 | Northeast |
| 1 | Upper Midwest |
| 2 | Desert |
| 3 | Midwest |
| 4 | Desert |
| 5 | Plains |
| 6 | null |
| 7 | Plains |
| 8 | Coastal |
| 9 | Midwest |
| 10 | New England |
| 11 | New England |
| 12 | null |
| 13 | Central |
| 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 | 3.00 | 1 | 1 | 1 |
| Phone | OnTech | East | 2 | 4.50 | 2 | 0 | 1 |
| Web | Sling TV | West | 2 | 1.50 | 0 | 2 | 1 |
