SQL171 零食类商品中复购率top3高的商品

SQL171 零食类商品中复购率top3高的商品

原代码

with temp1 as ( select date_sub(date(max(event_time)), interval 90 day) as start_date, date(max(event_time)) as end_date from tb_order_overall ), temp2 as ( select product_id, uid, count(*) as 2_cnt from tb_order_detail join tb_order_overall using (order_id) join tb_product_info using (product_id) where tag = \'零食\' and ( date(event_time) between (  select start_date  from temp1 ) and (  select end_date  from temp1 ) ) group by product_id, uid order by product_id, uid )select product_id, round(sum(if(2_cnt >= 2, 1, 0)) / count(*), 3) as repurchase_ratefrom temp2group by product_idorder by repurchase_rate desc, product_idlimit 3

问题

1. ​时间范围计算​
   date_sub(date(max(event_time)), interval 90 day) 应改为 date_sub(date(max(event_time)), interval 89 day)
   （因为 between 包含边界值，90天区间应为 [max_date-89, max_date]）

2. ​订单状态过滤​
   未筛选已付款订单（status=1），会包含待付款/退款订单

3. ​复购率分母逻辑​
   当前分母是 count(*)（所有购买记录数），但实际应为 ​去重的购买人数

修正代码

WITH date_range AS ( SELECT DATE_SUB(DATE(MAX(event_time)), INTERVAL 89 DAY) AS start_date, DATE(MAX(event_time)) AS end_date FROM tb_order_overall WHERE status = 1 -- 只统计已付款订单),product_purchases AS ( SELECT d.product_id, o.uid, COUNT(*) AS purchase_count FROM tb_order_detail d JOIN tb_order_overall o ON d.order_id = o.order_id JOIN tb_product_info p ON d.product_id = p.product_id WHERE p.tag = \'零食\' AND o.status = 1 -- 已付款订单 AND DATE(o.event_time) BETWEEN (SELECT start_date FROM date_range)  AND (SELECT end_date FROM date_range) GROUP BY d.product_id, o.uid)SELECT product_id, ROUND( SUM(CASE WHEN purchase_count >= 2 THEN 1 ELSE 0 END) / COUNT(DISTINCT uid), -- 去重计算总人数 3 ) AS repurchase_rateFROM product_purchasesGROUP BY product_idORDER BY repurchase_rate DESC, product_idLIMIT 3;