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.
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 |
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 | |
| 201 | 1 | 2024-03-20 | 8 | in_app |
| 209 | 7 | 2024-03-25 | 10 | phone |
| 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 |
| 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 |
| customer_id | customer_name | institution_type |
|---|---|---|
| 3 | Northlake Health | Hospital |
| 1 | Alder University | University |
| 7 | Gray Research Lab | Research Institute |
| 2 | Beacon University | University |
| 5 | Easton College | College |
| 4 | Crestview Pharma | Corporate |
| 8 | Harbor Medical Center | Hospital |
| 6 | Delta Biotech | |
| 9 | Ivy Teaching Hospital | Hospital |
| 10 | Juniper Analytics | Corporate |
| 1 | Juniper Analytics | Life Sciences |
| 2 | Helix Discovery Lab | Government |
| 3 | Granite Medical Group | Government |
| 4 | Vertex Pharma | Academic Medical Center |
| 5 | Redwood University | National Lab |
| 6 | Parkview Hospital | Community College |
| 7 | Pinecrest Hospital | Consortium |
| 8 | Westbridge Health | Research Hospital |
| 9 | Parkview Hospital | null |
| 10 | Easton College | Academic Medical Center |
| 11 | Oakridge Medical School | Publisher |
| 12 | Juniper Analytics | Research Institute |
| 13 | Crestview Pharma | Community College |
| 14 | Ivy Teaching Hospital | College |
| 15 | Silverline Institute | Library Consortium |
| 16 | Cedar Oncology Center | Foundation |
| 17 | Cedar Oncology Center | Private Clinic |
| 18 | Vertex Pharma | Hospital |
| 19 | Crestview Pharma | Hospital |
| survey_id | customer_id | response_date | satisfaction_score | feedback_channel |
|---|---|---|---|---|
| 204 | 3 | 2024-03-18 | 9 | |
| 201 | 1 | 2024-03-20 | 8 | in_app |
| 209 | 7 | 2024-03-25 | 10 | phone |
| 202 | 1 | 2024-03-28 | 8 | |
| 203 | 2 | 2024-03-15 | 4 | |
| 205 | 2 | 2024-03-29 | 5 | in_app |
| 206 | 5 | 2024-03-31 | 6 | |
| 207 | 8 | 2024-03-05 | 7 | phone |
| 208 | 4 | 2024-02-27 | 3 | |
| 210 | 9 | 2024-04-02 | 9 | in_app |
| 211 | 2024-03-11 | 6 | ||
| 212 | 6 | 2024-03-14 | ||
| 1 | 50 | 2024-03-06 | 99 | onsite_interview |
| 2 | 15 | 2024-03-31 | 11 | user_group |
| 3 | 16 | 2024-03-17 | 25 | regional_event |
| 4 | 53 | 2024-03-31 | 32 | survey_link |
| 5 | 50 | 2024-03-24 | 100 | conference_booth |
| 6 | 85 | 2024-03-30 | 71 | survey_link |
| 7 | 99 | 2024-03-23 | 67 | product_banner |
| 8 | 94 | 2024-02-24 | null | chat |
| 9 | 40 | 2024-03-10 | 32 | newsletter |
| 10 | 58 | 2024-03-19 | 16 | conference_booth |
| 11 | 65 | 2024-03-08 | 83 | customer_success_call |
| 12 | 91 | 2024-04-02 | 50 | |
| 13 | 4 | 2024-03-29 | 21 | chat |
| 14 | 64 | 2024-03-26 | 22 | null |
| 15 | 89 | 2024-04-01 | 89 | embedded_widget |
| 16 | 52 | 2024-03-13 | null | phone |
| 17 | 38 | 2024-03-01 | null | onsite_interview |
| 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 |
| 102 | 1 | 2024-03-05 | article_view | 4 |
| 103 | 1 | 2024-03-12 | article_view | 7 |
| 104 | 1 | 2024-03-20 | citation_export | 1 |
| 106 | 3 | 2024-03-10 | search | 5 |
| 107 | 3 | 2024-03-22 | 2 | |
| 108 | 5 | 2024-03-01 | search | 3 |
| 109 | 5 | 2024-03-03 | article_view | 5 |
| 110 | 5 | 2024-03-03 | article_view | 5 |
| 112 | 5 | 2024-03-15 | citation_export | 0 |
| 113 | 5 | 2024-03-30 | 1 | |
| 114 | 2 | 2024-03-01 | search | 9 |
| 115 | 2 | 2024-03-01 | article_view | 2 |
| 116 | 2 | 2024-03-02 | article_view | 3 |
| 117 | 2 | 2024-03-02 | article_download | 1 |
| 118 | 2 | 2024-03-03 | search | 4 |
| 119 | 2 | 2024-03-04 | article_view | 6 |
| 120 | 2 | 2024-03-05 | citation_export | 1 |
| 121 | 2 | 2024-03-06 | search | 7 |
| 122 | 2 | 2024-03-06 | article_view | 2 |
| 123 | 2 | 2024-03-06 | article_download | 1 |
| 124 | 2 | 2024-03-10 | search | 5 |
| 125 | 2 | 2024-03-10 | article_view | 3 |
| 126 | 2 | 2024-03-10 | article_download | 2 |
| 127 | 2 | 2024-03-10 | article_download | 2 |
| 128 | 2 | 2024-03-12 | search | 4 |
| 129 | 2 | 2024-03-12 | article_view | 3 |
| 130 | 2 | 2024-03-12 | citation_export | 1 |
| 131 | 2 | 2024-03-12 | search | 4 |
| 132 | 2 | 2024-03-12 | article_view | 3 |
| 133 | 2 | 2024-03-12 | article_download | 1 |
| 134 | 4 | 2024-03-08 | search | 2 |
| 135 | 2024-03-09 | search | 1 | |
| 136 | 9 | 2024-04-01 | article_view | 10 |
| customer_id | customer_name | total_usage_events | active_usage_days | latest_satisfaction_score | usage_segment |
|---|---|---|---|---|---|
| 6 | Delta Biotech | 0 | 0 | null | Low Usage |
| 16 | Cedar Oncology Center | 0 | 0 | 25 | Low Usage |
| 4 | Crestview Pharma | 1 | 1 | 21 | Low Usage |
| 15 | Silverline Institute | 0 | 0 | 11 | Low Usage |
| 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 | 20 | 8 | 5 | High Usage |
