Percentile Usage User Segmentation

mailchimp_1.sql

WITH login_frequency AS (
  SELECT user_id, 
         COUNT(login) AS num_logins 
         FROM logins 
         GROUP BY user_id) 
SELECT user_id, 
       CASE NTILE(4) OVER (ORDER BY num_logins) 
         WHEN 1 THEN ‘bottom_25’ 
         WHEN 4 THEN ‘top_25’ 
         ELSE ‘middle_50’ 
       END AS usage_segment 
FROM login_frequency