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

WITH
    curr_time_table AS (
        SELECT
            DATE (MAX(event_time)) curr_time
        FROM
            tb_order_overall
    ),
    snack_table AS (
        SELECT
            product_id,
            uid
        FROM
            tb_order_overall
            INNER JOIN tb_order_detail USING (order_id)
            RIGHT JOIN tb_product_info USING (product_id)
            CROSS JOIN curr_time_table
        WHERE
            tag = "零食"
            AND status = 1
            AND DATEDIFF (curr_time, DATE (event_time)) < 90
    ),
    user_cnt_table AS (
        SELECT
            product_id,
            uid,
            COUNT(uid) user_cnt #每个客户购买次数
        FROM
            snack_table
        GROUP BY
            product_id,
            uid
    )
SELECT
    product_id,
    ROUND(
        COUNT(IF(user_cnt > 1, 1 ,NULL))/COUNT(DISTINCT uid),
        3
    )repurchase_rate
FROM user_cnt_table
GROUP BY product_id
ORDER BY repurchase_rate DESC,  product_id
LIMIT 3

1. curr_time_table:计算 tb_order_overall 表中最大事件时间的日期，并将其作为当前时间 curr_time。
2. snack_table:通过连接 tb_order_overall、tb_order_detail 和 tb_product_info 表，筛选出标签为 "零食"、状态为 1 并且事件时间在当前时间的90天内的订单记录。
3. user_cnt_table:统计每个 product_id 下，每个 uid 购买的次数，结果存储在 user_cnt 中。
4. 最终查询:计算每个 product_id 的回购率（即购买超过一次的用户数占总用户数的比例），并选择回购率最高的前三个产品。

详细解题思路：

1. 获取当前时间:从 tb_order_overall 表中提取最大事件时间的日期，作为当前时间 curr_time。
2. 筛选符合条件的订单记录:从 tb_order_overall、tb_order_detail 和 tb_product_info 表中筛选出标签为 "零食"、状态为 1 且在当前时间的90天内的订单记录。通过连接不同的表来获取需要的数据。
3. 统计每个产品的用户购买次数:通过 GROUP BY 语句，统计每个产品（product_id）下每个用户（uid）的购买次数，并将结果存储在 user_cnt 中。
4. 计算回购率:对于每个产品，计算购买超过一次的用户数占总用户数的比例，即回购率。通过 IF 条件判断购买次数是否大于1，并使用 COUNT 函数统计满足条件的用户数。最后，通过 ORDER BY 子句按回购率降序排序，并选择回购率最高的前三个产品。