You are given customer feedback survey responses collected for Elsevier products such as ScienceDirect and Scopus. Write a SQL query to summarize the survey results for each survey question by returning the question text, the number of non-null ratings submitted, and the average rating. Only include rows where rating_score is not null, and sort the results by average rating descending and then by question text ascending.
| Column | Type | Description |
|---|---|---|
| response_id | INT | Unique survey response row |
| product_name | VARCHAR(100) | Elsevier product the feedback refers to |
| question_text | VARCHAR(255) | Survey question shown to the customer |
| rating_score | INT | Rating from 1 to 5; may be null if skipped |
| submitted_date | DATE | Date the survey was submitted |
| response_id | product_name | question_text | rating_score | submitted_date |
|---|---|---|---|---|
| 3 | ScienceDirect | Overall satisfaction | 5 | 2024-04-03 |
| 1 | Scopus | Overall satisfaction | 4 | 2024-04-01 |
| 5 | ClinicalKey | Ease of use | 2 | 2024-04-04 |
| 8 | Scopus | Support experience | 1 | 2024-04-06 |
| question_text | response_count | avg_rating |
|---|---|---|
| Content relevance | 4 | 4.50 |
| Overall satisfaction | 4 | 4.00 |
| Ease of use | 3 | 3.00 |
| Support experience | 2 | 1.50 |
| Column | Type | Description |
|---|---|---|
| response_idPK | INT | Unique identifier for each survey response row |
| product_name | VARCHAR(100) | Elsevier product associated with the survey response |
| question_text | VARCHAR(255) | Survey question answered by the customer |
| rating_score | INT | Rating score from 1 to 5; null if the question was skipped |
| submitted_date | DATE | Date the survey response was submitted |
| response_id | product_name | question_text | rating_score | submitted_date |
|---|---|---|---|---|
| 3 | ScienceDirect | Overall satisfaction | 5 | 2024-04-03 |
| 1 | Scopus | Overall satisfaction | 4 | 2024-04-01 |
| 7 | ScienceDirect | Content relevance | 5 | 2024-04-05 |
| 5 | ClinicalKey | Ease of use | 2 | 2024-04-04 |
| 10 | Scopus | Content relevance | 4 | 2024-04-07 |
| 2 | Scopus | Ease of use | 3 | 2024-04-02 |
| 12 | ScienceDirect | Support experience | 2024-04-08 | |
| 8 | Scopus | Support experience | 1 | 2024-04-06 |
| 4 | ScienceDirect | Overall satisfaction | 2024-04-03 | |
| 6 | ClinicalKey | Ease of use | 4 | 2024-04-04 |
| 9 | ClinicalKey | Support experience | 2 | 2024-04-06 |
| 11 | ClinicalKey | Content relevance | 5 | 2024-04-07 |
| 13 | Scopus | Overall satisfaction | 3 | 2024-04-08 |
| 14 | ScienceDirect | Content relevance | 4 | 2024-04-09 |
| 15 | ClinicalKey | Overall satisfaction | 4 | 2024-04-09 |
| 16 | Scopus | Content relevance | 2024-04-10 | |
| 1 | Neurology Practice | Institutional access setup | 13 | 2024-03-30 |
| 2 | Expert Lookup | Search quality | 56 | 2024-04-03 |
| 3 | Expert Lookup | Mobile experience | 88 | 2024-04-13 |
| 4 | Researchfish | Support experience | 82 | 2024-04-12 |
| 5 | Scopus | Metadata quality | 69 | 2024-03-30 |
| 6 | ScienceDirect | Citation tracking usefulness | null | 2024-04-06 |
| 7 | Sherpath | Personalization relevance | 26 | 2024-04-12 |
| 8 | PlumX | Institutional access setup | 48 | 2024-04-07 |
| 9 | PlumX | Support experience | 76 | 2024-04-12 |
| 10 | Cell Press | Platform reliability | 81 | 2024-03-29 |
| 11 | Mendeley | Alert usefulness | 6 | 2024-04-12 |
| 12 | Pure | Dashboard usefulness | 92 | 2024-04-09 |
| 13 | Sherpath | Coverage of content | 51 | 2024-03-29 |
| 14 | Funding Institutional | Mobile experience | 19 | 2024-04-01 |
| question_text | response_count | avg_rating |
|---|---|---|
| Content relevance | 4 | 4.50 |
| Overall satisfaction | 4 | 4.00 |
| Ease of use | 3 | 3.00 |
| Support experience | 2 | 1.50 |
