Recode and Pivot Ratings — Solution & ExplanationProblems / Recode and Pivot Ratings / EditorialApproachTwo independent transformations, cleanest done in two layers:

Recode. A CASE expression maps the 1-5 score onto segment labels: score <= 2 gives 'detractor', score = 3 gives 'passive', everything else is 'promoter'. Putting this in a CTE (or withColumn in PySpark) gives the label a name the next layer can reference.

Pivot counts. GROUP BY survey_id with one conditional count per segment: COUNT(CASE WHEN segment = 'detractor' THEN 1 END). COUNT ignores the NULLs produced by non-matching rows and returns 0 over an all-NULL group — so survey 3 (no detractors) and survey 4 (no promoters) show 0, not NULL.

Key InsightRecode-then-pivot beats stuffing the range checks into every aggregate (COUNT(CASE WHEN score <= 2 ...) repeated three times): the business rule — what makes a detractor — lives in exactly one place, and the pivot layer stays mechanical. When the segment boundaries change, only the CASE changes. The boundary scores are where wrong solutions die: 2 is still a detractor, 3 is passive (not a detractor, not a promoter), and 4 is already a promoter.Hints

Two steps: a CASE expression recodes each score into a segment label, then conditional counts pivot the labels into columns.

Put the recode in a CTE (or a withColumn in PySpark) so the pivot layer reads cleanly: COUNT(CASE WHEN segment = 'detractor' THEN 1 END).

COUNT over a CASE with no ELSE yields 0 when no rows match — that is how a survey with zero detractors shows 0 rather than NULL. Watch the boundaries: 2 is still a detractor, 3 is passive, 4 is already a promoter.

Solve Recode and Pivot Ratings yourself →