• 用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
  • 假设今天就是数据中所有日期的最大值。
  • 近7天表示包含当天T的近7天，即闭区间[T-6, T]。
  • 近7天 (datediff<=6 )
  • 近30天（datediff<=29)
  • 假设今天就是数据中所有日期的最大值。
  • 求出今天的日期 now----select max(date(out_time) )from tb_user_log as now
  • 求出每个客户第一次登录的时间(firsttime)----min(date(in_time) )以及最新登录的时间(newtime)----max(date(out_time) )

  • 易错点
  1. 用户等级划分：
    • 近7天表示包含当天T的近7天，即闭区间[T-6, T]。
    • 近7天 (datediff<=6 )
    • 近30天（datediff<=29)
  2. select 后面查询规则：select 后面语句只能是一列一列的，不能是多行多列
  3. 等级区分，使用case when 函数

运行代码：
select user_grade, round(count(uid)/ (select count(distinct uid) from tb_user_log) ,2) ratio
from 
(
select uid, (case when datediff(now,newtime)<=6 and datediff(now,firsttime)>6
             then'忠实用户'
             when datediff(now,firsttime)<=6 then '新晋用户'
             when datediff(now,newtime)>29 and datediff(now,firsttime)>29 then '流失用户'
             else '沉睡用户'
             end ) user_grade
             from
(
select uid, max(date(out_time)) newtime, #最近登录时间 
min(date(in_time)) firsttime, #第一次登录时间
(select max(date(out_time)) nowtime from tb_user_log ) now, #今天的日期
(select count(distinct uid) from tb_user_log ) couid #总的用户数
from tb_user_log
group by uid
    ) as A) as B
    group by user_grade
    order by ratio desc