Your question is Combine Usage and Survey Feedback. 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 activity from ScienceDirect and post-period survey responses. Write a PostgreSQL query that combines the two sources to show, for each customer who submitted a survey in March 2024, their total March usage, the number of distinct active usage days, their latest survey score in March, and a usage segment derived from total usage. Include surveyed customers even if they had no matching usage records.
Use the latest March survey response per customer when multiple responses exist, and classify customers as High Usage for 20+ events, Medium Usage for 5-19 events, and Low Usage for fewer than 5 events. Return the result ordered by survey score descending, then total usage descending, then customer name ascending.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer organization name |
| institution_type | VARCHAR(50) | Institution segment for the customer |
| Column | Type | Description |
|---|---|---|
| usage_idPK | INT | Unique usage event identifier |
| customer_id | INT | Customer associated with the usage event |
| usage_date | DATE | Date of the usage event |
| event_type | VARCHAR(50) | Type of ScienceDirect activity |
| article_views | INT | Number of article views recorded for the event |
| Column | Type | Description |
|---|---|---|
| survey_idPK | INT | Unique survey response identifier |
| customer_id | INT | Customer who submitted the survey |
| response_date | DATE | Date the survey response was submitted |
| satisfaction_score | INT | Survey satisfaction score from 1 to 10 |
| feedback_channel | VARCHAR(50) | Channel used to collect the response |