Label CSAT Survey Responses by Sentiment — SQL Problem
After a ticket closes the customer is asked to rate the support on a 1–5 scale.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #330
Problem statement
After a ticket closes the customer is asked to rate the support on a 1–5 scale. The CX team labels each response: a score of 4 or 5 is a 'promoter', 3 is 'neutral', 1 or 2 is a 'detractor', and a survey that was sent but never answered (score NULL) is 'no answer'.
Return every response with the columns response_id, ticket_id and sentiment, ordered by response_id.
Tables
Table: CsatResponse
| Column | Type |
|---|---|
| response_id | int |
| ticket_id | int |
| score | int |
| sent_at | datetime |
Primary key: response_id.
One survey per closed ticket. score is 1–5, or NULL while the customer has not answered.
Examples
Example 1
CsatResponse
| response_id | ticket_id | score | sent_at |
|---|---|---|---|
| 1 | 2001 | 5 | 2025-07-01 10:00:00 |
| 2 | 2002 | 3 | 2025-07-01 11:30:00 |
| 3 | 2003 | NULL | 2025-07-01 12:10:00 |
| 4 | 2004 | 1 | 2025-07-02 09:45:00 |
| 5 | 2005 | 4 | 2025-07-02 16:20:00 |
| 6 | 2006 | 2 | 2025-07-03 08:05:00 |
Output
| response_id | ticket_id | sentiment |
|---|---|---|
| 1 | 2001 | promoter |
| 2 | 2002 | neutral |
| 3 | 2003 | no answer |
| 4 | 2004 | detractor |
| 5 | 2005 | promoter |
| 6 | 2006 | detractor |
How to solve Label CSAT Survey Responses by Sentiment
Each row is mapped to a label, which is what a searched CASE is for. Its WHEN branches are tried in order and the first true one wins, so the ranges can be written as simple thresholds: after score >= 4 has taken the promoters, score = 3 takes the neutrals and ELSE is left with 1 and 2.
The trap is NULL. For an unanswered survey every comparison — NULL >= 4, NULL = 3 — is unknown, not false, and CASE treats unknown like false, so without a branch of its own a NULL score would fall into ELSE and be counted as a detractor. Testing score IS NULL first gives it the 'no answer' label. The second alternative relies on the same fact the other way round: a simple CASE over the five scores returns NULL for anything it does not list, and COALESCE turns that into 'no answer'.
Nested IF calls are MySQL's shorthand for the same logic. The query reads each row once.
Reference solution (MySQL)
SELECT response_id, ticket_id,
CASE
WHEN score IS NULL THEN 'no answer'
WHEN score >= 4 THEN 'promoter'
WHEN score = 3 THEN 'neutral'
ELSE 'detractor'
END AS sentiment
FROM CsatResponse
ORDER BY response_idAnother way
SELECT response_id, ticket_id, IF(score IS NULL, 'no answer', IF(score >= 4, 'promoter', IF(score = 3, 'neutral', 'detractor'))) AS sentiment FROM CsatResponse ORDER BY response_idAnother way
SELECT response_id, ticket_id, COALESCE(CASE score WHEN 5 THEN 'promoter' WHEN 4 THEN 'promoter' WHEN 3 THEN 'neutral' WHEN 2 THEN 'detractor' WHEN 1 THEN 'detractor' END, 'no answer') AS sentiment FROM CsatResponse ORDER BY response_id← On-Call Engineers Who Were Not Paged in March · First-Response SLA Breach Rate by Priority →