Build labeled score bands

WITH scored AS (
  SELECT user_id,
         engagement_score,
         NTILE(5) OVER (ORDER BY engagement_score DESC) AS bucket
  FROM user_scores
)
SELECT user_id,
       engagement_score,
       CASE bucket
         WHEN 1 THEN 'elite'
         WHEN 2 THEN 'high'
         WHEN 3 THEN 'medium'
         WHEN 4 THEN 'low'
         ELSE 'very_low'
       END AS engagement_band
FROM scored;