教育行业案例:如何分析复购用户?
【面试题】
"课程订单表”里记录了某在线教育App的用户购买课程的信息(部分数据截图)。
请使用sql将购买记录表中的信息,提取为下表(复购分析表)的格式。并用一条sql语句写出。
复购用户:如果一个用户的首次购买日期是a,且该用户在a之后的第n月内,也有购买行为,这个用户被算做第n月复购用户。
【解题思路】
这是常见的复购问题,也就是将用户按购买时间分组,比较不同时间组的用户复购数。其本质是使用里了群组分析方法,将数据按某些特征进行分类,分成不同的组进行分析。
该业务分析要求查询结果中包括:日期(说明是按购买日期来汇总数据)、当日首次购买用户数、此月复购用户数,第N月复购用户数。
1.当日首次购买用户数
先来看当日首次购买用户数这一列如何分析出?
每日首次购买用户数,表示每一行记录的是当天的购买用户数。
当有“每个”出现的时候,要想到《猴子从零学会SQL》中讲过的用“分组汇总来”来实现。
按每天分组(group by ),汇总购买用户数(计数函数count)。
select 购买时间,
count(distinct 用户id) as 当日首次购买用户数
from 课程订单表
group by 购买时间;
查询结果如下:
2.此月复购用户数
再来看查询结果中的此月复购用户数
此月复购用户数:在本月内购买大于等于两次课程的用户。例如今天购买了课程,7天后又购买了课程的用户,也就是每次购买的月时间间隔<=1个月 。
一个表如果涉及到时间间隔,就需要用到自联结,也就是将两个相同的表进行联结。
select a.*
from 课程订单表 as a
left join 课程订单表 as b
on a.用户id = b.用户id;
把上面的联结结果记为临时表c,如何从临时表c中查找出时间间隔(用户第二次购买时间-用户第一次购买时间)<=1个月的数据呢?
(1)这涉及到计算两个日期之间的差值,《猴子从零学会sql》里讲到对应单函数是timestampdiff。下图是这个函数的用法。
select a.*,timestampdiff(month,a.购买时间,b.购买时间) as 时间间隔
from 课程订单表 as a
left join 课程订单表 as b
on a.用户id = b.用户id;
用case语句选出时间间隔<=1个月的数据,并计数就是此月复购用户数
count(distinct case when 时间间隔<=1 then 用户id
else null
end) as 此月复购用户数
代入上面的sql就是:
select a.购买时间,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) <=1
then a.用户id else null end ) as 此月复购用户数
from 课程订单表 as a
left join 课程订单表 as b
on a.用户id = b.用户id
group by a.购买时间;
查询结果:
3.第三月复购用户数,第四月复购用户数。。。。第二十月复购用户数
和此月复购用户数分析思路一样,只需要更改时间间隔=N个月即可。
最终sql代码如下:
select a.购买时间,
count(distinct a.用户id) 当日首次购买用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) <=1
then a.用户id else null end ) as 此月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =3
then a.用户id else null end ) as 第三月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =4
then a.用户id else null end ) as 第四月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =5
then a.用户id else null end ) as 第五月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =20
then a.用户id else null end ) as 第二十月复购用户数
from 课程订单表 as a
left join 课程订单表 as b
on a.`用户id` = b.`用户id`
where a.课程类型=2
group by a.购买时间;
查询结果:
【本题考点】
1.常用指标的理解,例如留存用户数、用户复购数。
2.灵活使用case来统计when 函数与group by 进行自定义列联表统计。
3.遇到只有一个表,但是需要计数时间间隔的问题,就要想到用自联结来求时间间隔。
4.遇到复购问题,可以拿出本题的答案作为万能模板来应用。
相关文章
- Gary Marcus又来「整顿」AI圈:LeCun不可信,Nature审稿人没用脑子
- 认真的吗?让机器狗当守门员,还发了篇论文
- 两次登顶常识推理问答榜单ProtoQA,哈工大深圳创新掩码模型重排序策略
- 骁龙8 Gen2跑分现身:全新X3超大核,CPU性能仅提升10%
- 这家从东南亚突围的AI初创独角兽,做对了什么?
- Copilot逐字复制代码,恐抹去整个开源社区?程序员拿出律师证发起集体诉讼
- 股价直逼宁德时代,派能科技稳吗?
- 卷!MIT泊松流生成模型击败扩散模型,兼顾质量与速度
- pdf编辑器全版本下载
- 挡不住了!扩散模型只用文字就能PS照片了
- 到小红书去,AI技术青年大有可为
- 多云架构下,JAVA微服务技术选型实例解析
- JAVA已过气?中俄大佬对话告诉你俄罗斯最受欢迎的编程语言是什么!
- Acrobat最经典的版本:PDF编辑器Acrobat 2021经典版,下载
- Adobe Acrobat Pro DC 2019(PDF) 软件下载安装包教程(附下载方法)
- Adobe Acrobat Pro DC 2018(PDF) 软件下载安装包教程(附下载方法)
- 仅需1% Embedding参数,硬件成本降低十倍,开源方案单GPU训练超大推荐模型
- 文件更小,质量更高,大火的Stable Diffusion还能压缩图像?
- 11分钟充电70%,华人教授在锂电池中加镍箔登上Nature
- 戴着VR头盔教机器人抓握,机器人当场就学会了