Task
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.
Schema
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| institution_type | VARCHAR(50) | Customer segment |
sciencedirect_usage
| column | type | description |
|---|
| usage_id | INT | Unique usage event identifier |
| customer_id | INT | Customer tied to the usage event |
| usage_date | DATE | Date of the usage event |
| event_type | VARCHAR(50) | Type of usage event |
| article_views | INT | Number of article views in the event |
customer_surveys
| column | type | description |
|---|
| survey_id | INT | Unique survey response identifier |
| customer_id | INT | Customer who submitted the survey |
| response_date | DATE | Survey response date |
| satisfaction_score | INT | Satisfaction score from 1 to 10 |
| feedback_channel | VARCHAR(50) | Survey collection channel |
Sample data
customers
| customer_id | customer_name | institution_type |
|---|
| 3 | Northlake Health | Hospital |
| 1 | Alder University | University |
| 7 | Gray Research Lab | Research Institute |
sciencedirect_usage
| usage_id | customer_id | usage_date | event_type | article_views |
|---|
| 105 | 3 | 2024-03-02 | article_download | 8 |
| 101 | 1 | 2024-03-05 | search | 12 |
| 111 | 7 | 2024-02-28 | search | 6 |
customer_surveys
| survey_id | customer_id | response_date | satisfaction_score | feedback_channel |
|---|
| 204 | 3 | 2024-03-18 | 9 | email |
| 201 | 1 | 2024-03-20 | 8 | in_app |
| 209 | 7 | 2024-03-25 | 10 | phone |
Expected output
| customer_id | customer_name | total_usage_events | active_usage_days | latest_satisfaction_score | usage_segment |
|---|
| 7 | Gray Research Lab | 0 | 0 | 10 | Low Usage |
| 3 | Northlake Health | 3 | 3 | 9 | Low Usage |
| 1 | Alder University | 4 | 3 | 8 | Low Usage |
| 8 | Harbor Medical Center | 0 | 0 | 7 | Low Usage |
| 5 | Easton College | 5 | 4 | 6 | Medium Usage |
| 2 | Beacon University | 22 | 6 | 5 | High Usage |
