user_activity_rank.sql

/* user_id (var) IDR5678
activity_id (var)0456765
activity_type (text) signup / purchase / logout / upsell_click
created_at (date) 2020-12-23

Calculate the most popular (the most commonly used) user action. */

select activity_type
, count(*) as total_activity
from (
         select user_id
         , activity_type
         , total_actions
         , rank() over (partition by user_id order by total_actions desc) as rank
         from (
                  select ua.user_id,
                         ua.activity_type,
                         count(*) as total_actions
                  from user_activity as ua
                  group by ua.user_id, ua.activity_type
                  order by ua.user_id, ua.activity_type, total_actions
              )
         order by rank asc
     )
where rank = 1
group by activity_type
order by popular_activity desc