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

# 先将订单总表中退款的订单剔除，后连接订单明细表，于是形成各商品销售情况表
with k1 as(
select t1.order_id, t1.uid, event_time, status, product_id, price, cnt
from (
    select *
    from tb_order_overall
    where status != 2
) t1
join tb_order_detail t2
on t1.order_id = t2.order_id)

# 计算零食类商品复购率
select t3.product_id, round(ifnull(fugou_num, 0)/all_num, 3) repurchase_rate 
from (
    select *
    from tb_product_info
    where tag = '零食'
) t3
left join (
    # 从商品销售情况表中计算各商品近90天内的至少购买它两次的人数
    select product_id, count(uid) fugou_num
    from (
        select product_id, uid, count(*)
        from (
            select *, max(date(event_time))over() today
            from k1
        ) k2
        where datediff(today, date(event_time)) < 90
        group by product_id, uid
        having count(*) >= 2
    ) k3
    group by product_id 
) t4
on t3.product_id = t4.product_id
join (
    # 从商品销售情况表中计算各商品的购买总人数
    select product_id, count(distinct uid) all_num
    from k1
    group by product_id 
) t5
on t3.product_id =t5.product_id
order by repurchase_rate desc
limit 3;

好家伙，以后题目一定要看清楚，自测时运行通过了，结果一直提交不过。找了很久的问题，结果发现：

没加上tag = "零食"的条件，啊啊啊啊，以后一定要看清楚需求。