拆解需求

统计活跃间隔对用户分级后，各活跃等级用户占比

求什么

统计间隔，用户分级，级人数/总人数

输出什么

用户分级，对应级人数/总人数

步骤

概览

数据预处理
根据时间间隔，给每个人打标签
按照标签聚合，求人数

具体

1. 数据预处理

因为前几题有说in_time,out_time跨天，两天都算活跃，所以保险起见，还是用union拼了一下表。
看题解区其他小伙伴的代码，发现可加可不加，对本题没有很大影响。大家可酌情。
为什么用union，不用union all。——有用户当天活跃多次，用union可直接去重同人同天

with t0 as(
    select
    uid,date(in_time) dt
    from tb_user_log
    union
    select
    uid,date(out_time) dt
    from tb_user_log),

2.根据时间间隔，给每个人打标签

时间间隔
基础参数：最后活跃时间，首次活跃时间，今天。用datediff求间隔。
每个人打标
case when 条件 then '标签'

1.1活跃时间间隔

（1）看描述，需要什么参数

凡涉及新增，一般需要用first_day首次活跃时间——以每人聚合，max(date)

凡涉及活跃，一般需要last_day最后活跃时间——以每人聚合，min(date)

凡近X天，一般需要参考today：今天——整个表，最大日期max(date)

代码

t1 as(
    select
    uid
    ,max(dt) last_day
    ,min(dt) first_day
    ,(select max(dt) today from t0) today
    from t0
    group by 1),

（2）由此，需要这3个基础数据，再用datediff（）算间隔，梳理出条件

datediff()，本题最小单位是天，所以用到datediff即可，不需要timestampdiff那么细

①小陷阱：

沉睡用户实际应该是：近7天未活跃，但近30天有活跃。

因为，不加这个边界，沉睡用户会包含流失用户。这几个标签之间应该是互斥唯一的。

②个人小技巧：

需求包含当天的情况下（如本需求），近X天，datediff直接<X。未活跃（反动作），就在活跃的基础上改开口方向（补集）
不包含当天，在<的基础上，增删等号，包含的加了，不包含就不加
不知道我有没有说清楚，以大家自己的为准，欢迎一起讨论交流，不然每次需要推，每个小窍门，会比较耗时。

1.2 用户分级

用case when 根据不同间隔datediff() ，打标签then“XX用户”

小tips：如果datediff分不清哪个早晚大小，可以直接加abs()绝对值

代码

t2 as(
    select 
uid,case
when abs(datediff(first_day,today))<7 then '新晋用户'
when abs(datediff(first_day,today))>=7 and abs(datediff(last_day,today))<7 then'忠实用户'
when first_day is not null and abs(datediff(last_day,today))>=7 and abs(datediff(last_day,today))<30 then '沉睡用户'
when first_day is not null and abs(datediff(last_day,today))>=30 then '流失用户'
else '其他' end user_grade
    from t1)

3.按照标签聚合，求人数

总人数用一个子查询，从没聚合的表里取

select 
user_grade
,round(count(1)/(select count(uid) tol from t2),2) ratio
from t2
group by 1
order by 2 desc

完整代码

with t0 as(
    select
    uid,date(in_time) dt
    from tb_user_log
    union
    select
    uid,date(out_time) dt
    from tb_user_log
),
t1 as(
    select
    uid
    ,max(dt) last_day
    ,min(dt) first_day
    ,(select max(dt) today from t0) today
    from t0
    group by 1
),
t2 as(
    select 
uid,case
when abs(datediff(first_day,today))<7 then '新晋用户'
when abs(datediff(first_day,today))>=7 and abs(datediff(last_day,today))<7 then'忠实用户'
when first_day is not null and abs(datediff(last_day,today))>=7 and abs(datediff(last_day,today))<30 then '沉睡用户'
when first_day is not null and abs(datediff(last_day,today))>=30 then '流失用户'
else '其他' end user_grade
    from t1)
select 
user_grade
,round(count(1)/(select count(uid) tol from t2),2) ratio
from t2
group by 1
order by 2 desc

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