SQL案例练习（5）：产品用户等级分析

案例来自书籍《SQL面试宝典：图解数据库求职题》

【背景】

调研注册用户质量，已知公司体系由3个等级，分别为1等级、2等级、3等级。用户每天状态可能升级或降级。为了实时监控用户质量，需提取数据，制作用户等级监控体系。

【数据源】

用户等级明细表
包含日期（当日与前一日）、用户ID、用户名称、用户等级。

【明确问题】

目的是想要监控用户等级，了解用户等级变化情况，针对不同等级升降级用户进行精细化运营。

【分析过程】

1.每个用户获取前一天的等级情况

利用偏移窗口函数lag()获取用户前一天的等级

select *,
    lag(用户等级,1,null) over (partition by 用户id order by 日期) as 上次等级
from sheet1

创建视图“用户最新等级表”，便于后续调用

create view 用户最新等级表 as (select *,
    lag(用户等级,1,null) over (partition by 用户id order by 日期) as 上次等级
from sheet1)

2.标记每个用户的等级变化情况

用户等级=上次用户等级，说明等级不变
用户等级>上次用户等级，说明升级
用户等级<上次用户等级，说明降级

select 日期,用户id,用户等级,上次等级,
        (case when 用户等级=上次等级 then '等级不变'
              when 用户等级>上次等级 then '升级'
              else '降级' end) as 用户等级变化
from 用户最新等级表
where 上次等级 is not null

创建视图“用户等级变化表”，便于后续调用

create view 用户等级变化表 as (select 日期,用户id,用户等级,上次等级,
        (case when 用户等级=上次等级 then '等级不变'
              when 用户等级>上次等级 then '升级'
              else '降级' end) as 用户等级变化
from 用户最新等级表
where 上次等级 is not null)

3.汇总当日各等级用户等级情况

包括各等级用户数量，升级、降级、等级不变的人数

select 日期,用户等级,
        count(用户id) as 人数,
        sum(case when 用户等级变化 ='等级不变' then 1 else 0 end) as 等级不变人数,
        sum(case when 用户等级变化 ='升级' then 1 else 0 end) as 升级人数,
        sum(case when 用户等级变化 ='降级' then 1 else 0 end) as 降级人数
from 用户等级变化表
group by 日期,用户等级
order by 用户等级 asc

此处数据量较少，展示数量即可。
若数据量较大，可以在数量基础上，展示出百分比。

select 日期,用户等级,
        count(用户id) as 人数,
        concat(round(count(用户id)/(select count(用户id) from 用户等级变化表),2)*100,'%') as 人数占比,
        sum(case when 用户等级变化 ='等级不变' then 1 else 0 end) as 等级不变人数,
        concat(round(sum(case when 用户等级变化 ='等级不变' then 1 else 0 end)/count(用户id),2),'%')        as 等级不变人数占比,
        sum(case when 用户等级变化 ='升级' then 1 else 0 end) as 升级人数,
        concat(round(sum(case when 用户等级变化 ='升级' then 1 else 0 end)/count(用户id),2),'%')        as 升级人数占比,
        sum(case when 用户等级变化 ='降级' then 1 else 0 end) as 降级人数,
        concat(round(sum(case when 用户等级变化 ='降级' then 1 else 0 end)/count(用户id),2),'%')        as 降级人数占比
from 用户等级变化表
group by 日期,用户等级
order by 用户等级 asc

目的是输出监控用户等级表，故不再针对数据讨论分析。