『SQL实战』高频考题之复购问题，坑点居然这么多！

数据攻略 2024-09-05

251

点击上方蓝色『数据攻略』关注+星标~

数据分析求职干货不错过

哈喽大家好，我是数据攻略的六哥~
数据分析笔面试关于sql的考核
一般面试官的出题习惯，大多是结合
所在行业、实际工作中的一些高频场景
抽离出来逻辑、相关指标进行考察

这种考察可以达到两个目的：
对于面试官来讲，一方面这样出题成本最低
另一方面，由于是实际工作中会遇到的问题
不仅可以考察应聘者的基础代码和逻辑能力
也可以侧面了解下ta对行业基础指标的了解

比如，电商行业关于sql考核中
经常会遇到这类经典 「复购问题」

本篇文章就来解析一下这道经典题目
含参考思路、易错考点总结
文末有类似考题的延考伸变体
可以自查自测、也可以举一反三
提高求职高峰期期间的备战效率

------正文手动分割线------

本文结构速览：

一、问题背景（真题）

二、解题思路（2种）

三、参考代码（2种）

四、考点总结+延伸变体

一、问题背景

假设你现在负责某电商业务线，运营负责的某个资源位，希望得知每日下单的用户规模，以及对应的平均复购天数是多少？有以下声明：

其中用户下单后，第二次购买的时间间隔成为复购时间
复购时长大于30天将直接记为流失用户，不参与复购时长计算
购买订单不包含未支付订单

▼ 表说明：

现有一张订单支付表（order_info），字段结构如下：

user_id，用户注册时生成的ID
orderid, 订单ID
goodsid,商品ID
paystatus,支付状态（含：退款，未支付，已支付）
paytime,支付时间
order_amount,支付金额

▼ 输出要求：

请用2种思路写出sql逻辑，输出结构如下：

日期：dt
下单用户：pay_uv
平均复购天数：avg_rttn_days

二、解题思路

整体思路逻辑：

首先，明确统计的粒度和范围，即维度是日期，计算的平均指标范围是支付用户，目前的数据表记录粒度和范围都更细更广，故底层需要先做过滤、去重处理
接着，搞清楚指标定义，复购的含义即当前购买用户再一次购买，根据本题意，所以需要得知用户当前下单距离下次下单的间隔时间。这里是思路核心。（💥涉及2种方式来实现，也是考核重点）
然后，进行处理计算聚合，对每一行用户当前下单时间和下次下单时间，利用datediff做时间差值计算后，再聚合统计每日这群用户的指标均值情况

2类解法提示：

因为数据库存储特性一般做查询处理只能在同行数据列上操作如果需要到在跨行之间做前后查询处理：

第1种方式：利用窗口函数，达到一段sql可实现“不同行之间做处理，且可维持展示原行数条数据”的效果
第2种方式：利用表自关联join的方式来处理，来实现附加列字段添加后进行判断处理

三、参考代码

方法一：窗口函数

步骤1：底层先做处理：过滤统计范围、去重用户、时间戳转换为日期；
步骤2：当天下单用户利用lead()偏移1行，找下次下单时间；
步骤3：利用where过滤掉下次下单间隔超过30天的用户
步骤3：接着上述操作后，求对应日期分区，下单用户&平均复购天数，即每个用户当前及之后复购时间利用DATEDIFF做差求平均。

(以下代码可左滑看)

--步骤[3]：求对应日期分区,下单用户&平均复购天数:每个用户当前及之后复购时间做差求平均
SELECT  pay_dt
       ,COUNT(1)                          AS pay_uv
       ,AVG(DATEDIFF(rttn_pay_dt,pay_dt)) AS avg_rttn_days
FROM
(
    --步骤[2]：当天下单用户偏移1行，找下次下单时间
    SELECT  user_id
           ,pay_dt
           ,LEAD(pay_dt,1)OVER(PARTITION BY user_id ORDER BY  pay_dt) AS rttn_pay_dt
    FROM
    (
        --步骤[1]：底层先做处理：过滤统计范围、去重用户、时间戳转换为日期
        SELECT  user_id
               ,TO_DATE(paytime) AS pay_dt
        FROM order_info
        WHERE paystatus NOT IN ('未支付')
        GROUP BY  user_id
                 ,TO_DATE(paytime)
    )t1
)t2
WHERE rttn_pay_dt <= DATEADD(pay_dt, 30) -- 注意：过滤掉下次下单间隔超过30天的用户 
GROUP BY  pay_dt
 ;

方法二：自关联方式

步骤1：构建临时表order_user_daily_info，圈选出每日下单的用户范围，注意底层where过滤、user_id去重处理；
步骤2：表自关联构造新列，具体如下：

步骤2条件1：利用where筛选出每个用户b表购买日期晚于a表的记录
步骤2条件2：利用where筛选出天数间隔在30日以内的记录

步骤3：取当前a表日期分区下的最近一次复购日期记录，利用min()函数；
步骤4：求对应日期分区,下单用户&平均复购天数即每个用户当前及之后复购时间利用DATEDIFF做差求平均。

(以下代码可左滑看)

--步骤[1]：构建临时表，圈选出每日下单的用户范围，注意底层where过滤、user_id去重处理
CREATE TABLE order_user_daily_info AS
SELECT  user_id
       ,TO_DATE(paytime) AS pay_dt
FROM order_info
WHERE paystatus NOT IN (‘未支付’)
GROUP BY  user_id
         ,TO_DATE(paytime)
;

--步骤[4]：求对应日期分区,下单用户&平均复购天数:每个用户当前及之后复购时间做差求平均
SELECT  a.pay_dt
       ,COUNT(1)                              AS uv
       ,AVG(DATEDIFF(b.rttn_pay_dt,a.pay_dt)) AS avg_rttn_days
FROM
(
    SELECT  a.user_id
           ,a.pay_dt
           ,MIN(b.dt) AS rttn_pay_dt   --步骤[3]：取当前a表日期分区下的最近一次复购日期记录:min
    FROM order_user_daily_info a
    INNER JOIN order_user_daily_info b --步骤[2]：自关联每个用户
    ON a.user_id = b.user_id
    WHERE a.pay_dt < b.pay_dt          --步骤[2]条件1：筛选出每个用户b表购买日期晚于a表的记录
    AND b.dt <= DATEADD(a.pay_dt, 30)  --步骤[2]条件2：筛选出天数间隔在30日以内的记录
    GROUP BY  a.user_id
             ,a.dt
)a
GROUP BY  a.pay_dt
;