教育行业案例：学员续费如何分析？

Previous小红书面试题：如何分析用户行为？Next字节跳动面试题：你的平均薪水是多少？

Last updated 4 years ago

Was this helpful?

教育行业案例：学员续费如何分析？

【面试题】

某线上学习平台设置学员线上学习阶梯，新学员购买 50 节课为一个学习阶段，学习完想要进入下个阶段必须再次购买，即续费（假设所有学员只能续费一次）并且每个学员可选择不同老师进行学习。

表一：学员上课表

表二：购买表

现求出续费学员在续费前 3 个月内的总课量，3 个月给学员上课老师数量，以及每个上课老师给学员的上课量。
现求出每个续费学员在续费前的最后一节课的时间，以及对应的上课老师。

【解题步骤】

1. 求出所有续费学员在续费前 3 个月内的总课量，3 个月给学员上课老师数量，以及每个上课老师给学员的上课量。

1）所有续费学员在续费前 3 个月内的总课量

购买表中 “订单类型”=2 的为续费学员，上课信息在学员上课表中。要用到 2 个表，所以需要多表联结。

学员上课表和购买表，通过学生 id 作为联结条件。

使用哪种联结呢？

select *
from 学员上课表 as  a
inner join 购买表 as b
on a.学员id=b.学员id
where b.订单类型=2;

查询结果

如何求得续费前 3 个月的课程量？

用 case 语句来判断，新增一列为 “续费前 3 个月数”。如果(上课时间 - 续费时间)<=3，那么“续费前 3 个月数” 列中对应的值标记为 1。否则标记为 null。

select *,
(case when 
          timestampdiff(month,a.上课时间,b.续费时间)<=3 
           then 1 
           else null 
           end)  as 续费前三个月数
from 学员上课表 as  a
inner join 购买表 as b
on a.学员id=b.学员id
where b.订单类型=2;

查询结果

对续费前 3 个月的记录进行计数（count）得出所有续费学员的总课量，对老师 id 去重计数得出上课老师数量。

select count(distinct a.老师id) as 上课老师数量,
count(case when 
              timestampdiff(month,a.上课时间,b.续费时间)<=3 
              then 1 
              else null end) as 续费前三个月数
from 学员上课表 as  a
inner join 购买表 as b
on a.学员id=b.学员id
where b.订单类型=2;

查询结果如下：

2）每个上课老师给学员的上课量。

按老师 id 分组（group by ），汇总续费前三个月课程量（计数函 count）。

select a.老师id,
count(case when 
               timestampdiff(month,a.上课时间,b.续费时间)<=3 
               then 1 
               else null end) as 续费前三个月数
from 学员上课表 as  a
inner join 购买表 as b
on a.学员id=b.学员id
where b.订单类型=2
group by  a.老师id;

查询结果：

2. 求出每个续费学员在续费前的最后一节课的时间，以及对应的上课老师。

1）跟前面题目一样，找出购买表中 “订单类型”=2 的为续费学员。两表联结获得续费学员的上课信息。

select  *
from 学员上课表 as  a
inner join 购买表 as b
on a.学员id=b.学员id
where b.订单类型=2;

筛选早于续费日期的学习记录

select  *
from 学员上课表 as  a
inner join 购买表 as b
on a.学员id=b.学员id
where b.订单类型=2
and a.上课时间 < b.续费时间;

查询结果如下：

2）题目要求查询 “每个用户”，当每个出现的时候，就要想到分组汇总（group by 或者窗口函数的 partiotion by）。

3）续费前最后一节课的时间

大白话翻译就是，上课时间最晚的课程信息。按照上课时间对每个学员 id 的上课记录进行排名，然后取出最后一条课程数据就是。

所以使用分组（窗口函数 partiotion by 学员 id），并按最后交易时间降序排列 (order by 上课时间 desc)，套入窗口函数的语法，得出下面的 sql 语句：

select a.*,b.续费时间,
row_number() over (
             partition by a.学员id 
             order by a.上课时间 DESC ) as 上课时间排序
from 学员上课表 as  a
inner join 购买表 as b
on a.学员id=b.学员id
where b.订单类型=2
and a.上课时间 < b.续费时间;

查询结果：

4）可以看到通过上课时间降序排序后，最晚的学习的记录是排在第一条，用 where 筛选出每个学员的第 1 条记录，得出每个学员续费前的学习记录。

select * from
(select a.*,b.续费时间,
row_number() over (
          partition by a.学员id 
          order by a.上课时间 DESC ) as 上课时间排序
from 学员上课表 as  a
inner join 购买表 as b
on a.学员id=b.学员id
where b.订单类型=2
and a.上课时间 < b.续费时间) as c
where 上课时间排序=1;