SQL基础第七讲：关于用户留存率的计算

SQL大数据开发 2021-04-19

898

最近，好几个小伙伴都拿着关于用户留存的面试题来问我，所以今天单独开一篇文章讲一下留存问题。

首先看一下留存是什么，简单来说，我和你今天在一家超市购物了，明天我来购物了，你没来，那么我就是这个超市的留存用户。就是在设定的时间间隔都进行购物的客户，一般是计算七日留存，就是七天后谁来购物了。

接着我们看题目，我就直接上原图了：

1.看了下表结构，这里我也构造了一些数据：

2.看他的需求，

第一步是进行了三个判断，头部，尾部，和腰部，定义是上个月的不同消费额。

第二步是留存的概念，上个月消费了，这个月也消费了，看到这里，同学们应该都想到了关联。

第三步是定义了上个月和这个月的概念，这个很重要。

3.我们开始做，看到结果集和金额没有关系，只是中间需要转换成不同客户层，所以我拆开来看，先给他判断好他的消费额

t_6 as (
select 客户名称,   ---计算上月消费用户和他的类别
       case
         when sum(消费金额) > 30000 then
          '头部客户'
         when sum(消费金额) > 10000 then
          '腰部客户'
         else
          '尾部客户'
       end as 客户类别
  from 消费表
 where 消费日期 between date '2020-06-01' and date '2020-06-30'
 group by 客户名称
 having sum(消费金额)>0))
复制

t_7 as 
(select 客户名称   --对本月消费用户进行去重
  from 消费表
 where 消费日期 between date '2020-07-01' and date '2020-07-31'
 group by 客户名称
 having sum(消费金额)>0)
复制

t_g
(select '头部客户' as 客户类别 from dual --构造一个客户类别表，防止少数据
union all
select '腰部客户' as 客户类别 from dual 
union all
select '尾部客户' as 客户类别 from dual )
复制

这三段代码分别可以得到下面数据

select tg.客户类别,   --最后将他们关联起来即可
       count(t6.客户名称) as 用户量,
       count(t7.客户名称) as 留存用户
  from t_g tg
  left join t_6 t6
    on tg.客户类别 = t6.客户类别
  left join t_7 t7
    on t7.客户名称 = t6.客户名称
 group by tg.客户类别
复制

---总的代码如下：
with 消费表 as
(
select '张三' as 客户名称,date'2020-06-01' as 消费日期,10000 as 消费金额 from dual 
union all
select '张三' as 客户名称,date'2020-06-09' as 消费日期,25000 as 消费金额 from dual 
union all
select '李四' as 客户名称,date'2020-06-10' as 消费日期,28000 as 消费金额 from dual
union all
select '王五' as 客户名称,date'2020-06-30' as 消费日期,38000 as 消费金额 from dual
union all
select '李四' as 客户名称,date'2020-07-10' as 消费日期,680 as 消费金额 from dual
union all
select '李四' as 客户名称,date'2020-07-15' as 消费日期,6800 as 消费金额 from dual
union all
select '王五' as 客户名称,date'2020-07-25' as 消费日期,3850 as 消费金额 from dual
union all
select '马六' as 客户名称,date'2020-07-31' as 消费日期,2900 as 消费金额 from dual
)
,t_6 as (
select 客户名称,
       case
         when sum(消费金额) > 30000 then
          '头部客户'
         when sum(消费金额) > 10000 then
          '腰部客户'
         else
          '尾部客户'
       end as 客户类别
  from 消费表
 where 消费日期 between date '2020-06-01' and date '2020-06-30'
 group by 客户名称
 having sum(消费金额)>0)
 ,t_7 as 
 (
select 客户名称
  from 消费表
 where 消费日期 between date '2020-07-01' and date '2020-07-31'
 group by 客户名称
 having sum(消费金额)>0)
,t_g as 
(
select '头部客户' as 客户类别 from dual 
union all
select '腰部客户' as 客户类别 from dual 
union all
select '尾部客户' as 客户类别 from dual 
)
select tg.客户类别,
       count(t6.客户名称) as 用户量,
       count(t7.客户名称) as 留存用户
  from t_g tg
  left join t_6 t6
    on tg.客户类别 = t6.客户类别
  left join t_7 t7
    on t7.客户名称 = t6.客户名称
 group by tg.客户类别
复制

最后得到的结果

感觉还不错的话，点下在看鼓励一下作者吧

没有关注的也可以关注下公众号~再次感谢

数据库

文章转载自SQL大数据开发，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL基础第七讲：关于用户留存率的计算

评论

相关阅读