SQL分析品牌在2019.5-2020.4期间的复购率（复购率理解、inner join）

【面试题】某公司数据库里有3张表，销售订单表、产品明细表、销售网点表

”销售订单表”记录了销售情况，每一张数据表示哪位顾客、在哪一天、哪个网点购买了什么产品，购买的数量是多少，以及对应产品的零售价

 

 

销售订单表和产品明细表通过“产品”字段关联，销售订单表和销售网点通过“交易网点”关联

【问题】分析品牌在2019.5-2020.4期间的复购率

【解题思路】

1.明确业务指标

要分析的是“在2019.5-2020.4期间的复购率”，复购率是业务分析中经常用到的指标。

复购率是衡量产品受欢迎度的指标。复购率高，说明产品的忠诚用户越多，这时公司需要把更多的精力和资源投入到吸引、引导在用户的复购上。

复购率低说明产品的忠诚用户少，需要把资源和精力用在提升用户的转化率上。

复购率 =一定时间内购买次数大于1次的人数/所有购买的用户数

举个例子，3月份购买产品的100个人中有30个人购买了2次。3月份的总的复购人数为30人，复购率=一定时间内购买次数大于1次的人数（30）/所有购买的用户数（100）=30%

2.如何判断是否是复购呢？

不同的行业有不同的标准，一般是指发货了再购买的才算复购，同一天购买的不算是复购。

也就是日期不同的，再次购买的用户算是复购。

在“销售订单表”中，需要先用if函数判断用户（顾客ID字段）购买次数，大于1的为复购，标记为1，否则标记为0

if(count(distinct (a.顾客ID))>1,1,0)

 然后再用汇总函数（count）计数

count(distinct(a.顾客ID))

这样，复购率 =一定时间内购买次数大于1次的人数/所有购买的用户数

if(count(distinct (a.顾客ID))>1,1,0)/
   count(distinct(a.顾客ID)) as 复购率

3.不同日期的复购

一般按不同日期的购买为复购，需要把“销售订单表”复制为2个表，用不同的日期取交集，取出不同日期购买的顾客/次数，如下图

from 销售订单表 as a
inner join  销售订单表 as b
on a.交易日期!=b.交易日期

 把前面步骤里的SQL组合到一起，最终得到分析复购率的SQL：

select 
   if(count(distinct (a.顾客ID))>1,1,0)/
   count(distinct(a.顾客ID)) as 复购率
from 销售订单表 as a
inner join  销售订单表 as b
on a.交易日期!=b.交易日期
and a.交易日期 between "2019-05-01" and "2020-04-30";

得到复购率=0.2=20%

【本题考点】

1.此题重点查看对业务的理解，必须先清楚业务的指标的含义。常用的指标可以回顾《衡量业务：指标体系》

2.考察if判断函数的应用

3.内联结inner join的应用

注释：复购率也就是一段时间内购买次数大于1的情况，前提是不在同一天，所以需要排除时间相同的情况，也就是用到了表的内连接