Task
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.
Schema
| 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 |
Sample data
| 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 |
Expected output
| 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 |
