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.
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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer full name |
| membership_tier | VARCHAR(20) | My Best Buy membership tier |
| signup_date | DATE | Date the customer signed up |
| Column | Type | Description |
|---|---|---|
| purchase_idPK | INT | Unique purchase identifier |
| customer_id | INT | Customer who made the purchase |
| product_category | VARCHAR(50) | Best Buy product category |
| purchase_date | DATE | Date of purchase |
| order_amount | DECIMAL(10,2) | Total order amount |
| Column | Type | Description |
|---|---|---|
| feedback_idPK | INT | Unique feedback identifier |
| purchase_id | INT | Associated purchase when available |
| feedback_date | DATE | Date feedback was submitted |
| rating | INT | Customer rating from 1 to 5 |
| sentiment | VARCHAR(20) | Derived sentiment label |
| feedback_id | purchase_id | feedback_date | rating | sentiment |
|---|---|---|---|---|
| 206 | 106 | 2024-02-28 | 3 | neutral |
| 201 | 101 | 2024-01-10 | 2 | negative |
| 209 | 109 | 2024-03-18 | 5 | positive |
| 203 | 103 | 2024-01-25 | 3 | neutral |
| 211 | 999 | 2024-03-20 | 1 | negative |
| 204 | 104 | 2024-02-05 | 1 | negative |
| 208 | 108 | 2024-03-12 | negative | |
| 202 | 102 | 2024-01-12 | 5 | positive |
| 210 | 2024-03-25 | 4 | positive | |
| 205 | 105 | 2024-02-20 | 5 | positive |
| 207 | 107 | 2024-03-06 | 2 | neutral |
| 1 | 98 | 2024-01-19 | 15 | null |
| 2 | 57 | 2024-03-14 | 18 | very positive |
| 3 | 16 | 2024-02-29 | 71 | pending |
| 4 | 27 | 2024-01-21 | 54 | unsatisfied |
| 5 | 23 | 2024-03-21 | 51 | skeptical |
| 6 | 22 | 2024-01-23 | 27 | neutral |
| 7 | 50 | 2024-02-10 | 42 | mixed |
| 8 | 46 | 2024-01-21 | null | very positive |
| 9 | 95 | 2024-01-08 | 5 | null |
| 10 | 70 | 2024-03-10 | 57 | null |
| 11 | 86 | 2024-01-12 | 40 | not recommend |
| 12 | 87 | 2024-03-05 | 53 | critical |
| 13 | 17 | 2024-03-28 | 21 | frustrated |
| 14 | 65 | 2024-02-27 | null | frustrated |
| 15 | 16 | 2024-01-11 | 68 | skeptical |
| 16 | 14 | 2024-03-06 | 39 | very negative |
| customer_id | customer_name | membership_tier | signup_date |
|---|---|---|---|
| 3 | Priya Shah | Plus | 2023-11-15 |
| 1 | Ava Martinez | Core | 2023-09-01 |
| 6 | Noah Kim | 2024-01-10 | |
| 2 | Liam Johnson | Plus | 2023-10-12 |
| 5 | Sophia Chen | Core | 2024-02-20 |
| 4 | Mason Lee | Total | 2023-12-05 |
| 8 | Lucas Brown | Core | 2024-03-01 |
| 7 | Emma Davis | Plus | 2024-01-22 |
| 1 | Alexander Green | Partner | 2023-12-19 |
| 2 | James Taylor | Plus | 2023-12-29 |
| 3 | Logan Perez | Monthly | 2023-11-04 |
| 4 | Olivia Wilson | Total | 2023-09-08 |
| 5 | Ethan Clark | VIP | 2024-01-26 |
| 6 | Abigail Nelson | VIP | 2023-11-08 |
| 7 | Noah Kim | Monthly | 2024-02-20 |
| 8 | Mason Lee | Trial | 2023-10-25 |
| 9 | Logan Perez | Rewards | 2023-09-04 |
| 10 | Isabella Moore | null | 2024-02-21 |
| 11 | Alexander Green | Basic | 2024-02-03 |
| 12 | Emma Davis | Family | 2024-03-02 |
| 13 | Noah Kim | Tech | 2024-03-01 |
| 14 | Ethan Clark | Family | 2024-02-09 |
| 15 | Evelyn Baker | Elite | 2023-09-02 |
| 16 | Liam Johnson | Premium | 2023-12-17 |
| 17 | Liam Johnson | null | 2024-03-04 |
| 18 | Olivia Wilson | Employee | 2023-11-12 |
| 19 | Liam Johnson | Total | 2023-11-21 |
| purchase_id | customer_id | product_category | purchase_date | order_amount |
|---|---|---|---|---|
| 110 | 8 | Gaming | 2024-02-18 | 0 |
| 101 | 1 | Laptops | 2024-01-05 | 1299.99 |
| 108 | 6 | Accessories | 2024-03-10 | 19.99 |
| 103 | 1 | Accessories | 2024-01-20 | 49.99 |
| 111 | 3 | 2024-03-22 | 59.99 | |
| 106 | 4 | TVs | 2024-02-25 | 649.99 |
| 104 | 3 | Laptops | 2024-02-02 | 999.99 |
| 109 | 7 | Appliances | 2024-03-15 | 499.99 |
| 102 | 2 | TVs | 2024-01-08 | 799.99 |
| 112 | 2 | Laptops | 2023-12-28 | 899.99 |
| 105 | 2 | Laptops | 2024-02-14 | 1099.99 |
| 107 | 5 | Accessories | 2024-03-03 | -10 |
| 1 | 89 | null | 2024-02-08 | 349.39 |
| 2 | 4 | null | 2024-03-09 | 14.80 |
| 3 | 54 | null | 2024-03-24 | 345.83 |
| 4 | 33 | Cameras | 2024-03-18 | 720.64 |
| 5 | 9 | Wearables | 2024-03-22 | 263.55 |
| 6 | 70 | Printers | 2024-03-12 | 536.52 |
| 7 | 79 | null | 2024-01-23 | 317.72 |
| 8 | 20 | Cameras | 2024-01-02 | 634.30 |
| 9 | 91 | Monitors | 2023-12-27 | 598.26 |
| 10 | 68 | Storage | 2024-01-26 | 305.83 |
| 11 | 12 | Laptops | 2023-12-29 | 189.45 |
| 12 | 49 | Drones | 2024-02-25 | 523.62 |
| 13 | 77 | Car Electronics | 2024-01-15 | 558.30 |
| 14 | 71 | Wearables | 2024-03-09 | 54.58 |
| 15 | 95 | Audio | 2024-01-28 | 352.85 |
| 16 | 9 | Networking | 2024-01-14 | 870.47 |
| 17 | 75 | Laptops | 2024-02-12 | 879.22 |
| 18 | 21 | Fitness Tech | 2024-02-05 | 869.67 |
| product_category | distinct_customers | total_orders | avg_rating | negative_feedback_count | negative_feedback_rate |
|---|---|---|---|---|---|
| Accessories | 3 | 3 | 2.50 | 2 | 0.67 |
| Laptops | 4 | 4 | 7.25 | 2 | 0.50 |
| null | 5 | 5 | null | 0 | 0.00 |
| Cameras | 2 | 2 | null | 0 | 0.00 |
| TVs | 2 | 2 | 4.00 | 0 | 0.00 |
| Wearables | 2 | 2 | 39.00 | 0 | 0.00 |
