Task
You are given customer purchases and post-purchase feedback for Best Buy shoppers. Write a PostgreSQL query that identifies product categories with the strongest opportunity for action by combining purchasing behavior with customer sentiment. Return, for each category in Q1 2024, the number of distinct purchasing customers, total orders, average rating, count of negative feedback entries (rating = 2 or sentiment = 'negative'), and the negative feedback rate. Only include categories with at least 2 distinct purchasing customers and rank the results from highest negative feedback rate to lowest, breaking ties by total orders descending.
Use the tables below. Feedback may exist without a matching purchase, and some purchases may have no feedback, so your logic should handle both cases correctly.
Schema
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| membership_tier | VARCHAR(20) | My Best Buy membership tier |
| signup_date | DATE | Account signup date |
purchases
| column | type | description |
|---|
| purchase_id | INT | Unique purchase identifier |
| customer_id | INT | Purchasing customer |
| product_category | VARCHAR(50) | Purchased category |
| purchase_date | DATE | Purchase date |
| order_amount | DECIMAL(10,2) | Order value |
feedback
| column | type | description |
|---|
| feedback_id | INT | Unique feedback identifier |
| purchase_id | INT | Related purchase when available |
| feedback_date | DATE | Date feedback was submitted |
| rating | INT | Rating from 1 to 5 |
| sentiment | VARCHAR(20) | Derived sentiment label |
Sample data
purchases
| purchase_id | customer_id | product_category | purchase_date | order_amount |
|---|
| 101 | 1 | Laptops | 2024-01-05 | 1299.99 |
| 102 | 2 | TVs | 2024-01-08 | 799.99 |
| 103 | 1 | Accessories | 2024-01-20 | 49.99 |
| 104 | 3 | Laptops | 2024-02-02 | 999.99 |
feedback
| feedback_id | purchase_id | feedback_date | rating | sentiment |
|---|
| 201 | 101 | 2024-01-10 | 2 | negative |
| 202 | 102 | 2024-01-12 | 5 | positive |
| 203 | 103 | 2024-01-25 | 3 | neutral |
| 204 | 104 | 2024-02-05 | 1 | negative |
Expected output
| product_category | distinct_customers | total_orders | avg_rating | negative_feedback_count | negative_feedback_rate |
|---|
| Laptops | 3 | 3 | 2.67 | 2 | 0.67 |
| Accessories | 2 | 2 | 2.50 | 1 | 0.50 |
| TVs | 2 | 2 | 4.00 | 0 | 0.00 |
