SQL Window Functions: Percentiles, Bucketing, and Distribution Analysis

SELECT customer_id,
       spend,
       NTILE(10) OVER (ORDER BY spend DESC) AS spend_decile
FROM customers;

# Bucket rows into 10 groups.

SELECT player_id,
       score,
       PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM leaderboard;

# Relative standing from 0 to 1.

WITH scored AS (
  SELECT salary,
         CUME_DIST() OVER (ORDER BY salary) AS cd
  FROM employees
)
SELECT *
FROM scored
WHERE cd >= 0.90;

# Identify rows at or above a cumulative threshold.

WITH bucketed AS (
  SELECT customer_id,
         lifetime_value,
         NTILE(4) OVER (ORDER BY lifetime_value DESC) AS quartile
  FROM customers
)
SELECT customer_id,
       lifetime_value,
       CASE WHEN quartile = 1 THEN 'top_quartile' ELSE 'other' END AS segment
FROM bucketed;

# Turn NTILE output into a simple segment label.

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;

# Assign named score ranges using a window bucket.