教育行业案例：如何分析​复购用户？

【面试题】

"课程订单表”里记录了某在线教育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 aleft join 课程订单表 as bon a.用户id = b.用户id;

把上面的联结结果记为临时表c，如何从临时表c中查找出时间间隔（用户第二次购买时间-用户第一次购买时间）<=1个月的数据呢？

（1）这涉及到计算两个日期之间的差值，《猴子 从零学会sql》里讲到对应单函数是timestampdiff。下图是这个函数的用法。

select a.*,timestampdiff(month,a.购买时间,b.购买时间) as 时间间隔from 课程订单表 as aleft join 课程订单表 as bon 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 aleft join 课程订单表 as bon a.用户id = b.用户idgroup 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 aleft join 课程订单表 as bon a.`用户id` = b.`用户id`where a.课程类型=2group by a.购买时间;

查询结果：

【本题考点】

1.常用指标的理解，例如留存用户数、用户复购数。

2.灵活使用case来统计when 函数与group by 进行自定义列联表统计。

3.遇到只有一个表，但是需要计数时间间隔的问题，就要想到用自联结来求时间间隔。

4.遇到复购问题，可以拿出本题的答案作为万能模板来应用。

推荐：如何从零学会sql？