题解|时间限定分组位置|存疑|零食类复购率top3高的商品

# 计算每个用户对每个商品是否复购（生成子表t_uid_product_info）：
# 内连接多表：tb_order_detail JOIN tb_order_overall USING(order_id) JOIN tb_product_info USING(product_id)
# 筛选零食类商品：WHERE tag="零食"
# 筛选近90天的记录：
# 计算最小允许日期：DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
# 筛选：event_time >= (SELECT ... FROM tb_order_overall)
# 按用户和商品分组：GROUP BY uid, product_id
# 计算是否复购：IF(COUNT(event_time)>1, 1, 0) as repurchase
# 按商品分组：GROUP BY product_id
# 计算复购率：SUM(repurchase) / COUNT(repurchase) as repurchase_rate
# 保留3位小数：ROUND(x, 3)

# SELECT 复购率 = 近90天内至少购买两次的人数/购买的总人数
# MAX(DATE(event_time)) AS cur_day
# DATE_SUB(cur_day, INTERVAL 9 DAY) AS first_day
# DATE(event_time) BETWEEN first_day AND cur_day

# ## 购买总人数：COUNT(DISTINCT uid)
# ## 至少购买两次的人数：COUNT(event_time) OVER(PARTITION BY product_id,uid) AS cnt
# ## SELECT product,DISTINCT uid, COUNT(event_time) AS time_cnt
# ## GROUP BY product_id,uid

## 首先，生成一张表，判断是否为复购uid，需要进行表的连接
# SELECT a.product_id, DISTINCT uid, COUNT(event_time) AS time_cnt
# FROM tb.order_detail a
# LEFT JOIN tb_order_overall b ON a.order_id = b.order_id
# LEFT JOIN tb_product_info c ON a.product_id = c.product_id
# LEFT JOIN(
#     SELECT MAX(DATE(event_time)) AS cur_day,DATE_SUB(cur_day, INTERVAL 9 DAY) AS first_day
#     FROM tb_order_overall
# ) ON 1 = 1
# GROUP BY a.product_id,uid

## 使用筛选条件进行筛选，最近90天内是：
SELECT product_id, ROUND(SUM(re_cnt)/COUNT(re_cnt),3) AS repurchase_rate
FROM(
    SELECT a.product_id, uid, IF(COUNT(event_time)>1,1,0) AS re_cnt
    FROM tb_order_detail a
    LEFT JOIN tb_order_overall b ON a.order_id = b.order_id
    LEFT JOIN tb_product_info c ON a.product_id = c.product_id
    WHERE tag = '零食' AND event_time >= (
        SELECT DATE_SUB(MAX(DATE(event_time)), INTERVAL 89 DAY)
        FROM tb_order_overall)
    GROUP BY a.product_id, uid   
    )t1
GROUP BY product_id
ORDER BY repurchase_rate DESC,product_id 
LIMIT 3