Your question is Link Feedback to Purchase Opportunities. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| 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 |