题解 | #零食类商品中复购率top3高的商品#

题目：请统计零食类商品中复购率top3高的商品，结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

指标：某商品复购率 = 近90天内，购买它至少两次的人数 ÷ 购买它的总人数；其中，近90天指包含最大日期（记为当天）在内的近90天

注意，这里有个坑点是，“近90天”是指距离tb_order_overall表中最大日期的近90天（包含最大日期当天），而非每一件商品各自的近90天；题目没讲清楚，使得部分同学解题难度加深了，即使结果其实是对的但也过不了用例

解题思路

步骤一：找出零食类商品中，在近90天内，每件商品被每个用户购买的次数，从而找出有复购的用户

这一步骤的难点在于，定位近90天 —— 可以直接结合DATEDIFF函数与子查询一起使用；仍需要注意的另外一点是：因为包含了最大日期当天，因此DATEDIFF的结果应该是小于等于89的（结果为89的日期，其实就是近90天内的，第90天；因为最大日期当天的DATEDIFF结果应该为0）

SELECT o.uid, d.product_id, 
		COUNT(o.uid) AS buy_cnt /* COUNT(o.uid)>=2 的用户就是复购用户 */
FROM tb_order_detail AS d 
     LEFT OUTER JOIN tb_order_overall AS o ON d.order_id = o.order_id
     LEFT OUTER JOIN tb_product_info AS i ON d.product_id = i.product_id
/* 找出距离最大日期的近90天 */
WHERE DATEDIFF((SELECT MAX(event_time) FROM tb_order_overall), event_time) <= 89
/* 不要待支付订单、退款订单，只要已成交订单 */
AND o.status = 1
/* 只要零食类商品 */
AND i.tag = '零食'
GROUP BY o.uid, d.product_id

步骤二：计算出每个商品下的复购用户的个数并计算复购率

这一步比较明朗了，有了步骤一的临时表结果，可以知道 "COUNT(o.uid)>=2" 的用户就是复购用户。

只需要再使用CASE WHEN条件分支与聚合函数的结合，来对该些复购用户进行计数 —— "COUNT(o.uid)>=2" 的用户赋予值“1”，否则为“0”；最终按照product_id分组去计算，每个商品下复购用户的个数（对刚才新赋值的列进行SUM聚合）以及对应的复购率

# 请统计零食类商品中复购率top3高的商品。
SELECT t.product_id,
		/* 复购率保留3位小数 */
        ROUND(SUM(CASE WHEN t.buy_cnt >= 2 THEN 1 ELSE 0 END) / COUNT(t.product_id), 3) AS repurchase_rate
FROM
    (SELECT o.uid, d.product_id, COUNT(o.uid) AS buy_cnt
    FROM tb_order_detail AS d 
         LEFT OUTER JOIN tb_order_overall AS o ON d.order_id = o.order_id
         LEFT OUTER JOIN tb_product_info AS i ON d.product_id = i.product_id
    WHERE DATEDIFF((SELECT MAX(event_time) FROM tb_order_overall), event_time) <= 89
    AND o.status = 1
    AND i.tag = '零食'
    GROUP BY o.uid, d.product_id) AS t
GROUP BY t.product_id
/* 按复购率倒序、商品ID升序排序 */
ORDER BY repurchase_rate DESC, t.product_id ASC
/* 只要TOP 3 */
LIMIT 3;