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

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

原代码

withtemp1 as (selectdate_sub(date(max(event_time)), interval 90 day) as start_date,date(max(event_time)) as end_datefromtb_order_overall),temp2 as (selectproduct_id,uid,count(*) as 2_cntfromtb_order_detailjoin tb_order_overall using (order_id)join tb_product_info using (product_id)wheretag = '零食'and (date(event_time) between (selectstart_datefromtemp1) and (selectend_datefromtemp1))group byproduct_id,uidorder byproduct_id,uid)
selectproduct_id,round(sum(if(2_cnt >= 2, 1, 0)) / count(*), 3) as repurchase_rate
fromtemp2
group byproduct_id
order byrepurchase_rate desc,product_id
limit3

问题

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_dateFROM tb_order_overallWHERE status = 1  -- 只统计已付款订单
),product_purchases AS (SELECTd.product_id,o.uid,COUNT(*) AS purchase_countFROM tb_order_detail dJOIN tb_order_overall o ON d.order_id = o.order_idJOIN tb_product_info p ON d.product_id = p.product_idWHERE 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
)SELECTproduct_id,ROUND(SUM(CASE WHEN purchase_count >= 2 THEN 1 ELSE 0 END) / COUNT(DISTINCT uid),  -- 去重计算总人数3) AS repurchase_rate
FROM product_purchases
GROUP BY product_id
ORDER BY repurchase_rate DESC,product_id
LIMIT 3;