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

# 用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。

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

分析思路

1、先将各类标签转化成sql语言（类似于数学建模），发现只需要最近一次和最早一次活跃天数就能分类

-- 忠实用户 最早一次活跃在七天之前，最近一次活跃在近七天

-- 新增用户 最早一次活跃就是最近七天

-- 沉睡用户 最早一次活跃在七天之前，最近一次活跃也在七天之前：即最近一次活跃不是null而是7天前

-- 流失用户 最早一次活跃在30天之前，最近一次活跃也在30天前：即最近一次活跃不是null而是30天前

2、这里没要求，但是可以先把in_time 和out_time union 一起（去重）且保留唯一uid和活跃日期

3、然后统计每个用户的最早活跃日期，最晚活跃日期，聚合函数+group by uid

4、接下来有两种思路，因为结果的格式是每个标签的比率作为一行而不是一列，因此可以union不同类型用户的比率结果，或者先给每个用户打上tag标签，然后再用tag做分组字段，统计各个类型用户的比率。

这里有两个注意点：计算比率可以在group by tag的基础上，使用窗口函数sum(group by 后的聚合字段，即count(uid))计算分母——即所有用户数，over()里面可以为空；

其次case when字段有个坑点，如果先计算沉睡用户再计算流失用户，则流失用户因为要求比较严苛，全部满足沉睡条件——就把流失用户也全记作沉睡用户了；因此应该先计算流失用户，不满足条件的再进入下一个分支，判断是不是沉睡用户其实因为这四个分类完全时囊括了所有用户，把最后一个tag放到else 里面也可以

代码

with t1 as (
select uid,
    max(dt) as last_active,
    min(dt) as first_active,
    (select max(date(out_time)) from tb_user_log) as now_dt
from(
select distinct uid,date(in_time) as dt
from tb_user_log 
union
select distinct uid,date(out_time) as dt
from tb_user_log 
) tmp1
group by uid
)

select tag as user_grade,round(count(uid)/sum(count(uid))over(),2) as ratio
from(
select uid, 
    case when datediff(now_dt,first_active)>6 and datediff(now_dt,last_active)<=6 then "忠实用户"
    when  datediff(now_dt,first_active)<=6 and datediff(now_dt,last_active)<=6 then "新晋用户"
    when  datediff(now_dt,first_active)>29 and datediff(now_dt,last_active)>29 then "流失用户"
    when  datediff(now_dt,first_active)>6 and datediff(now_dt,last_active)>6 then "沉睡用户"
    else null end as tag
from t1
) tmp2
group by tag
order by ratio desc