题解 | #统计活跃间隔对用户分级结果#

先计算各个用户的初次登录时间（first_day），最近登录时间（recent_day），以及总的计算下当前的日期（now）

	SELECT 
        uid,
        (select date(max(out_time)) from tb_user_log) as 'now',
        date(min(in_time)) as 'first_day',
        date(max(out_time)) as 'recent_day'
    from tb_user_log as A
    group by uid

接着按照要求对用户分层

    select 
        uid,
        (case
             when timestampdiff(day,first_day,now) <= 6 then '新晋用户'
             when timestampdiff(day,recent_day,now) <= 6 then '忠实用户'
             when timestampdiff(day,recent_day,now) >= 29 then '流失用户'
             when timestampdiff(day,recent_day,now) < 29 then '沉睡用户'
        end) as 'user_grade'
    from temp

总代码如下：

with temp as (
    SELECT 
        uid,
        (select date(max(out_time)) from tb_user_log) as 'now',
        date(min(in_time)) as 'first_day',
        date(max(out_time)) as 'recent_day'
    from tb_user_log as A
    group by uid
),res as (
    select 
        uid,
        (case
             when timestampdiff(day,first_day,now) <= 6 then '新晋用户'
             when timestampdiff(day,recent_day,now) <= 6 then '忠实用户'
             when timestampdiff(day,recent_day,now) >= 29 then '流失用户'
             when timestampdiff(day,recent_day,now) < 29 then '沉睡用户'
        end) as 'user_grade'
    from temp
)
select user_grade,round(count(*)/(select count(*) from res),2) as 'ratio'
from res
group by user_grade
order by ratio desc