mysql - 如何按 created_at 分组并返回 SQL 中的每日活跃用户率？

Question

我使用模式公共数据集 tutorial.yammer_user 和 tutorial.yammer_events

Yammer.users 表 Yammer.events 表

这是问题：

请根据tutorial.yammer_events 表中的用户生命周期事件计数将用户生命周期事件计数分为几类。类别应该是您认为统计上最合理的方式，即0、1~4、5~49、50~499、> = 500等。然后请按用户创建日期对这些类别的用户数进行分组和排序.

事件仅包括：home_page、like_message、login、search_autocomplete、search_run、search_click_result_X、send_message、view_inbox。

所需的输出将是 user_create_date、lifetime_event_count_category1、lifetime_event_count_category2、lifetime_event_count_category3、lifetime_event_count_category4、lifetime_event_count_category5、total_active_user_count、total_user_count、active_user_rate

这是我的代码：

获取每个事件的计数

with temp2 as(
select user_id,
sum(case when event_name='home_page' then 1 else 0 end) as home_page,
sum(case when event_name='like_message' then 1 else 0 end) as like_message,
sum(case when event_name='login' then 1 else 0 end ) as login,
sum(case when event_name='search_autocomplete' then 1 else 0 end) as search_complete,
sum(case when event_name='search_run' then 1 else 0 end ) as search_run,
sum(case when event_name ilike 'search_click_result_%' then 1 else 0 end ) 
as search_click_result_X,
sum(case when event_name='send_message'then 1 else 0 end) as send_message,
sum(case when event_name='view_inbox' then 1 else 0 end) as view_inbox
from tutorial.yammer_events
group by user_id
)

将事件总数分为 5 组

SELECT 
sum(case when sum_of_lifetime_event_count=0 then 1 else 0 end) as lifetime_event_count_category1,
sum(case when sum_of_lifetime_event_count between 1 and 4 then 1 else 0 end) as lifetime_event_count_category2,
sum(case when sum_of_lifetime_event_count between 5 and 49 then 1 else 0 end) as lifetime_event_count_category3,
sum(case when sum_of_lifetime_event_count between 50 and 499 then 1 else 0 end) as lifetime_event_count_category4,
sum(case when sum_of_lifetime_event_count>500 then 1 else 0 end) as lifetime_event_count_category5
from 

##get the total no of event engaged for each user_id
(select user_id, 
sum(home_page+like_message+login+search_complete + search_run + search_click_result_X + view_inbox) 
as sum_of_lifetime_event_count
from temp2 group by user_id order by sum_of_lifetime_event_count desc) b;

通过用户 created_day 获取 yammer.user 表组

select cast(created_at as date),count(*) as created_date from tutorial.yammer_users 
group by created_date, user_id ;

我无法按用户创建日期对生命周期事件进行分组和排序。

标签： mysql