37. 统计活跃间隔对用户分级结果

题目需求

用户等级：

• 忠实用户：近7天活跃且非新用户

• 新晋用户：近7天新增

• 沉睡用户：近7天未活跃但是在7天前活跃

• 流失用户：近30天未活跃但是在30天前活跃

假设今天是数据中所有日期(login_ts)的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数。

注意：最近7天，在该题目中是 date_add(‘2021-10-09’, -7)，而 date_add(‘2021-10-09’, -6) 是更合适的。

期望结果如下：

level （用户等级）	cn （用户数量)
忠实用户	6
新增用户	3
沉睡用户	1

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)	logout_ts(登出时间)
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
102	120.245.11.2	2021-09-22 09:00:00	2021-09-27 09:30:00
103	27.184.97.3	2021-09-23 10:00:00	2021-09-27 10:30:00

实现一

-- 写了两个 SQL 语句，有点违规哦

-- 1） 查询出今天的日期   2021-10-09
select date_format(max(login_ts), 'yyyy-MM-dd') today
from user_login_detail;

-- 4） 统计每个 level 的 用户数
select user_level as level,
       count(*)   as cn
from (
         -- 3）统计每个用户是什么level
         select user_id,
                case
                    -- 忠实用户：近7天活跃且非新用户
                    when last_time_login >= date_add('2021-10-09', -7) and first_time_login < date_add('2021-10-09', -7)
                        then '忠实用户'
                    -- 新晋用户：近7天新增
                    when first_time_login >= date_add('2021-10-09', -7) then '新增用户'
                    -- 沉睡用户：近7天未活跃但是在7天前活跃
                    when last_time_login < date_add('2021-10-09', -7) then '沉睡用户'
                    -- 流失用户：近30天未活跃但是在30天前活跃
                    when last_time_login < date_add('2021-10-09', -30) then '流失用户'
                    end as user_level
         from (
                  -- 2）计算出每个用户的首次登录和末次登录日期, 并去重
                  select user_id,
                         first_time_login,
                         last_time_login
                  from (
                           select user_id,
                                  -- 首次登录日期
                                  first_value(date_format(login_ts, 'yyyy-MM-dd'))
                                              over (partition by user_id order by login_ts)      as first_time_login,
                                  -- 末次登录日期
                                  first_value(date_format(login_ts, 'yyyy-MM-dd'))
                                              over (partition by user_id order by login_ts desc) as last_time_login
                           from user_login_detail
                       ) t1
                  group by user_id, first_time_login, last_time_login
              ) t2
     ) t3
group by user_level;

-- 一个 SQL 语句解决


-- 4） 统计每个 level 的 用户数
select user_level as level,
       count(*)   as cn
from (
         -- 3）统计每个用户是什么level
         select user_id,
                case
                    -- 忠实用户：近7天活跃且非新用户
                    when last_time_login >= date_add('2021-10-09', -7) and first_time_login < date_add('2021-10-09', -7)
                        then '忠实用户'
                    -- 新晋用户：近7天新增
                    when first_time_login >= date_add('2021-10-09', -7) then '新增用户'
                    -- 沉睡用户：近7天未活跃但是在7天前活跃
                    when last_time_login < date_add('2021-10-09', -7) then '沉睡用户'
                    -- 流失用户：近30天未活跃但是在30天前活跃
                    when last_time_login < date_add('2021-10-09', -30) then '流失用户'
                    end as user_level
         from (
                  select user_id,
                         first_time_login,
                         last_time_login,
                         today
                  from (
                           -- 2）计算出每个用户的首次登录和末次登录日期, 并去重
                           select user_id,
                                  first_time_login,
                                  last_time_login
                           from (
                                    select user_id,
                                           -- 首次登录日期
                                           first_value(date_format(login_ts, 'yyyy-MM-dd'))
                                                       over (partition by user_id order by login_ts)      as first_time_login,
                                           -- 末次登录日期
                                           first_value(date_format(login_ts, 'yyyy-MM-dd'))
                                                       over (partition by user_id order by login_ts desc) as last_time_login
                                    from user_login_detail
                                ) t1
                           group by user_id, first_time_login, last_time_login
                       ) t2,
                       -- 笛卡尔积。t3表只有一个 值
                       (
                           -- 1） 查询出今天的日期   2021-10-09
                           select date_format(max(login_ts), 'yyyy-MM-dd') today
                           from user_login_detail
                       ) t3
              ) t4
     ) t5
group by user_level;

题目来源

http://practice.atguigu.cn/#/question/37/desc?qType=SQL