-
-
Save ks--ks/04684a307310a2d3647496dc41639482 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment