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

with a as(
    select uid,
    (select max(date(out_time)) from tb_user_log) now,
    min(date(in_time)) intime,
    max(date(out_time)) newtime
    FROM tb_user_log
    GROUP BY uid
),
b as (
    select  uid,
    (case
    when datediff(now,intime) > 6 and datediff(now,newtime) <= 6 then '忠实用户'
    when datediff(now,intime) <= 6 then '新晋用户'
    when datediff(now,intime) > 29 and datediff(now,newtime) > 29 then '流失用户'
    else '沉睡用户'
    end
    )user_grade
    from a 
)
select user_grade,
       round(count(uid) / (select count(distinct uid) from tb_user_log), 2) ratio
from  b
group by user_grade
order by ratio desc;

思路

统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

注：

用户等级标准简化为：

忠实用户(近7天活跃过且非新晋用户)、

新晋用户(近7天新增)、

沉睡用户(近7天未活跃但更早前活跃过)、

流失用户(近30天未活跃但更早前活跃过)。

假设今天就是数据中所有日期的最大值。近7天表示包含当天T的近7天，即闭区间[T-6, T]。

我们先拆出来**“用户表今天时间 now”、“用户最早出现在这个表里的时间 intime”、“用户最后的活跃时间 newtime”

然后以此为基础划分各级用户

忠实用户(近7天活跃过且非新晋用户)、 datediff(now,intime) > 6 (注册时间) and datediff(now,newtime) <= 6 (最近活跃)

(题上说近7天包括今天所以这里是6 )

新晋用户(近7天新增)、datediff(now,intime) <= 6(表里有过就活跃过)

沉睡用户(近7天未活跃但更早前活跃过)、注册时间、最近活跃 between 7 and 29，太长了所以放在else里面。

流失用户(近30天未活跃但更早前活跃过)。datediff(now,intime) > 29 and datediff(now,newtime) > 29 then '流失用户'