Label CSAT Survey Responses by Sentiment

Easy
330.

Label CSAT Survey Responses by Sentiment

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

ColumnType
response_idint
ticket_idint
scoreint
sent_atdatetime

Primary key: response_id.

One survey per closed ticket. score is 1–5, or NULL while the customer has not answered.

Example

The rows must come back in the order shown.

CsatResponse

response_idticket_idscoresent_at
1200152025-07-01 10:00:00
2200232025-07-01 11:30:00
32003NULL2025-07-01 12:10:00
4200412025-07-02 09:45:00
5200542025-07-02 16:20:00
6200622025-07-03 08:05:00

Output

response_idticket_idsentiment
12001promoter
22002neutral
32003no answer
42004detractor
52005promoter
62006detractor
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
response_idint
ticket_idint
scoreint
sent_atdatetime

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_idticket_idscoresent_at
1200152025-07-01 10:00:00
2200232025-07-01 11:30:00
32003NULL2025-07-01 12:10:00
4200412025-07-02 09:45:00
5200542025-07-02 16:20:00
6200622025-07-03 08:05:00

Output

response_idticket_idsentiment
12001promoter
22002neutral
32003no answer
42004detractor
52005promoter
62006detractor

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_id

Another 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_id

Another 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 →